Results 1 to 8 of 8

20050104, 16:35 #1
 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 "

20050104, 17:03 #2
 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

20050104, 17:07 #3
 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

20050104, 17:07 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20050104, 17:09 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20050104, 17:12 #6
 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

20050104, 22:19 #7
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Extracting decimal numbers (2002 SP3)
Or try this nonarray 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},"")))) )

20050105, 09:14 #8
 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 nonarray formula???