Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting decimal numbers (2002 SP3)

    Hi all and happy new year.

    I'm using the array formula {=MID(C3,MATCH(FALSE,ISERROR(1*MID(C3,ROW(INDIRECT ("1:"&LEN(C3))),1)),0),LEN(C3)-SUM(1*ISERROR(1*MID(C3,ROW(INDIRECT("1:"&LEN(C3))) ,1))))*1} to try to extract the numbers from a bank statement that has the amount column formatted as "

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting decimal numbers (2002 SP3)

    You can use this if they will always have decimal points:

    {=MID(C3,MATCH(FALSE,ISERROR(1*MID(C3,ROW(INDIRECT ("1:"&LEN(C3))),1)),0),1+LEN(C3)-SUM(1*ISERROR(1*MID(C3,ROW(INDIRECT("1:"&LEN(C3))) ,1))))*1}

    Currently you use 1 less because the decimal is not a number, but you want to include it in the length of the string.

    If it won't always have a decimal you can try (searching for the period):

    {=MID(C3,MATCH(FALSE,ISERROR(1*MID(C3,ROW(INDIRECT ("1:"&LEN(C3))),1)),0),ISNUMBER(FIND(".",C3))*1+LE N(C3)-SUM(1*ISERROR(1*MID(C3,ROW(INDIRECT("1:"&LEN(C3))) ,1))))*1}

    This will fail if it has period in it that is not a decimal point

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting decimal numbers (2002 SP3)

    You probably know this, but forgot:

    You will get an error if there is no period in the string.
    (FIND(".",C3)>0)
    will yield a #value error if it is not found. It is not like VB's InStr which returns 0 if not found.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting decimal numbers (2002 SP3)

    Edited by HansV to correct error in formula

    The formula counts the number of digits in the string - 4 in this example - and extracts that number of characters starting at the first digit - 97.2 in your example. In other words, the formula doesn't take the decimal point into account. Try this variation:

    =MID(C3,MATCH(FALSE,ISERROR(1*MID(C3,ROW(INDIRECT( "1:"&LEN(C3))),1)),0),LEN(C3)-SUM(1*ISERROR(1*MID(C3,ROW(INDIRECT("1:"&LEN(C3))) ,1)))+ISNUMBER(FIND(".",C3)))*1

    (as an array formula). If your values can also include thousands separators, it becomes more complicated.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting decimal numbers (2002 SP3)

    Thanks, I already noticed it and edited the post. Our formulas are now basically the same, I think.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting decimal numbers (2002 SP3)

    Yes, great minds .... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extracting decimal numbers (2002 SP3)

    Or try this non-array formula :

    =--MID(C3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C3&"01234567 89")),SUM((LEN(C3)-LEN(SUBSTITUTE(C3,{".",0,1,2,3,4,5,6,7,8,9},"")))) )

  8. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting decimal numbers (2002 SP3)

    That one is really neat.

    The help suggests that array constants can only be used in array formulas when it says:

    "In an ordinary formula, you can enter a reference to a cell containing a value, or the value itself, also called a constant. Similarly, in an array formula you can enter a reference to an array, or enter the array of values contained within the cells, also called an array constant. Array formulas accept constants in the same way that nonarray formulas do, but you must enter the array constants in a certain format."

    So I'm a bit surprised to find it working in a non-array formula???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •