# Thread: Extracting decimal numbers (2002 SP3)

1. ## 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. ## 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. ## 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)

Steve

4. ## 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. ## 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. ## Re: Extracting decimal numbers (2002 SP3)

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

Steve

7. ## 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. ## 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
•