Extract number with decimal from a text cell

[omarheiba]

Hey all, I am a beginner in the excel world, and I wanted to extract a number from a text cell, the problem is that the numbers contains decimal as well,

e.g. BRAVO 1.5L LIQUID DETERGENT to be (1.5)

what i used was this below formula

=SUMPRODUCT(MID(0&A21,LARGE(INDEX(ISNUMBER(--MID(A21,ROW($1:$30),1))* ROW($1:$30),0),ROW($1:$30))+1,1)*10^ROW($1:$30)/10)

And that extracted the number to be (15) ....


Need help to make the formula recognize the decimal points???

 

[pedrowave]

Hi omarheiba, your formula only works without decimal numbers. Try this array formula.

To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER.

=--MID(A21,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,".",","},A21),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A21,".",0),",",0),ROW($1:$100),1)))

 

[omarheiba]

Hi omarheiba, your formula only works without decimal numbers. Try this array formula.

To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER.

=--MID(A21,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,".",","},A21),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A21,".",0),",",0),ROW($1:$100),1)))

Dear pedrowave, thank you for your help but the formula you gave me keeps on telling me #VALUE!, eventhough i double checked that everything is right??? need help please :S

 

[pedrowave]

I said that you must simultaneously press CTRL+SHIFT+ENTER to enter my array formula into your worksheet. Excel surrounds the formula with braces ({ }) that you don't have to write.

Do not enter array formulas as normal formulas. Did you press the Enter key or these 3 keys together: Ctrl + Shift + Enter?

Read this link: [FONT=wf_segoe-ui_normal]Guidelines and examples of array formulas[/FONT]

 

[omarheiba]

Yes I did, but for some reason it still didnot work!!! :S

I used this different formula

=LOOKUP(9.9E+307,--LEFT(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A1&"1023456789")),999),ROW(INDIRECT("1:999"))))


and that worked pretty fine!!! :D :D
Thank you so much

 

[pedrowave]

It is the -- before my formula, try this array formula:

[COLOR=#49644E][I]=MID(A21,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9,".",","},A21),"")),COUNT(--MID(SUBSTITUTE(SUBSTITUTE(A21,".",0),",",0),ROW($1:$100),1)))[/I][/COLOR]

 

[István Hirsch]

If the formula in #5 does what you need, this formula will also perform the task, and about ten times faster:

=LOOKUP(10^8,--MID(A21,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A21&"0123456789")),{1,2,3,4,5,6,7,8}))

 

[pedrowave]

Hi István Hirsch, I've modified your formula to the largest allowed positive number.

=LOOKUP(9.99999999999999E+307,--MID(A21,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A21&"0123456789")),ROW($1:$20)))

Also we need a total of 20 digits: Excel numbers are until 15 digits + 1 digit decimal separator (.) + 4 digits floating point number (eg: E + 3 digits power).

With 20 characters is possible to evaluate the following string with the largest number into: BRAVO 9.99999999999999E307L LIQUID DETERGENT

 

[István Hirsch]

Hi István Hirsch, I've modified your formula to the largest allowed positive number.

=LOOKUP(9.99999999999999E+307,--MID(A21,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A21&"0123456789")),ROW($1:$20)))

Also we need a total of 20 digits: Excel numbers are until 15 digits + 1 digit decimal separator (.) + 4 digits floating point number (eg: E + 3 digits power).

With 20 characters is possible to evaluate the following string with the largest number into: BRAVO 9.99999999999999E307L LIQUID DETERGENT

I guess – considering the single sample provided – going over 99999999 is needless: data like 85235672.5L (85235672L + 0.5deciliter) would be highly unusual. Besides, some posters do not propose using ROW($1:$X) in a formula like this as they tend to shift if the user happens to insert new lines.

 

[pedrowave]

Besides, some posters do not propose using ROW($1:$X) in a formula like this as they tend to shift if the user happens to insert new lines.

Thanks, I'll remember that for next time. It is better: ROW(INDIRECT("1:20"))