Extracting decimal numbers (2002 SP3)

Topic

New Reply

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 “

Reply | Quote

Replies

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+LEN(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

Reply | Quote

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+LEN(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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Yes, great minds ….

Steve

Reply | Quote

Yes, great minds ….

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Or try this non-array formula :

=–MID(C3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C3&”0123456789″)),SUM((LEN(C3)-LEN(SUBSTITUTE(C3,{“.”,0,1,2,3,4,5,6,7,8,9},””)))))

Reply | Quote

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???

Reply | Quote

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???

Reply | Quote

Or try this non-array formula :

=–MID(C3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C3&”0123456789″)),SUM((LEN(C3)-LEN(SUBSTITUTE(C3,{“.”,0,1,2,3,4,5,6,7,8,9},””)))))

Reply | Quote