Extract Only Numbers From Text String

[mgirvin]

Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.

 

[kbend3r]

Here is one way to do it...

=-LOOKUP(0,-LEFT(MID(E5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E5&"0123456789")),15),ROW(INDEX(A:A,1):INDEX(A:A,15))))

Hi Rick,

Thank you so much for your quick response.

This is working perfectly with the exception of the number 10 for some reason. That is being returned as .91### but only the number 10. 20, 100, etc. are all fine. It doesn't seem to see the 0 but I am not sure how to debug since I don't understand the formula.

Kind Regards,
Kim

 

[Rick Rothstein]

Hi Rick,

This is working perfectly with the exception of the number 10 for some reason. That is being returned as .91### but only the number 10. 20, 100, etc. are all fine. It doesn't seem to see the 0 but I am not sure how to debug since I don't understand the formula.

I cannot duplicate that result... if 10, 20, or whatever is the only number in the text in cell E5 (the cell that I used when developing the formula), then that number is displayed by the formula.

 

[kbend3r]

I cannot duplicate that result... if 10, 20, or whatever is the only number in the text in cell E5 (the cell that I used when developing the formula), then that number is displayed by the formula.

Hi Rick,

I don't want to take up any more of your time on this as your solution is far and away above my skillset. Thank you again for your help. I will post the results so that you can see what I was referring to for the results.

My formula: =-LOOKUP(0,-LEFT(MID(A4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A4&"0123456789")),15),ROW(INDEX(A:A,1):INDEX(A:A,15))))

Column A Column with formula/results
park 10 10
10 parking 1
we 10 parking 1
20 parking 20
we 20 parking 20

 

[BatTodor]

Here is one way to do it...

=-LOOKUP(0,-LEFT(MID(E5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E5&"0123456789")),15),ROW(INDEX(A:A,1):INDEX(A:A,15))))

Hi Rick,
In string "abd501,56dadas" or "501,56dadas" or "abd501,56" I want to extract "501,56" as number. Do you think that will be possible to do that with formula?

Best regards
Todor Todorov

 

[bosco_yip]

Hi Rick,
In string "abd501,56dadas" or "501,56dadas" or "abd501,56" I want to extract "501,56" as number. Do you think that will be possible to do that with formula?

Best regards
Todor Todorov

Try,

1] A1:A3, enter : abd501,56dadas or 501,56dadas or abd501,56

2] B1, formula copy down :

=TEXT(-LOOKUP(0,-MID(SUBSTITUTE(A1,",",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,",","")&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,15)))),"#\,00 ")

Regards

 

[István Hirsch]

Is comma the decimal separator in your excel version (501,56 is displayed as a number)?

 

[István Hirsch]

Try,

1] A1:A3, enter : abd501,56dadas or 501,56dadas or abd501,56

2] B1, formula copy down :

=TEXT(-LOOKUP(0,-MID(SUBSTITUTE(A1,",",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,",","")&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,15)))),"#\,00 ")

Regards

For "abc124" I get 1,24. Is it my settings?

 

[Rick Rothstein]

Hi Rick,
In string "abd501,56dadas" or "501,56dadas" or "abd501,56" I want to extract "501,56" as number. Do you think that will be possible to do that with formula?

Is the comma your decimal point? If so, I think the formula will work for you (but I cannot test it because my decimal point is a dot).

 

[BatTodor]

Is the comma your decimal point? If so, I think the formula will work for you (but I cannot test it because my decimal point is a dot).

1) Yes, the comma is decimal point
2) Yes, original formula works in most cases, but if string is "0,5aa" the result is "0", not "0,5"

In fact I try to find solution to separate text and numbers in string <digits (include comma)><text>. This is my casual case. Optional will be same formula to separate it in cases:
1) <text><digits (include comma as decimal separator)><text>
2)<text><digits (include comma as decimal separator)>

Best regards
Todor

 

[BatTodor]

1) Yes, the comma is decimal point
2) Yes, original formula works in most cases, but if string is "0,5aa" the result is "0", not "0,5"

In fact I try to find solution to separate text and numbers in string <digits (include="" comma)=""><text>. This is my casual case. Optional will be same formula to separate it in cases:
1) <text><digits (include="" comma="" as="" decimal="" separator)=""><text>
2)<text><digits (include="" comma="" as="" decimal="" separator)="">

Best regards
Todor

Casual case is digits (with comma as decimal separator) next text
Optional cases:
1. Text then digits (with comma) and text
2. Text then digits (again with comma)

Sorry for spaming!
Todor
</digits></text></text></digits></text></text></digits>