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.

 

[skhanna]

Hi,

This did work. However, it does not cater to cases where in we have decimal values (Float) in the cell. For example: INR0.001. For this, the formula results in 1.
B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

 

[Ron Coderre]

Yes....the assumption in most of the suggested solutions is that the numbers are greater than or equal to zero. Decimal fractions can be an issue. If your example is your typical situation, this regular formula returns the first set of consecutive numbers from the test cell:

B1: =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(1:10000))))

Something you can work with?

 

[Peter_SSs]

Hi,

This did work. However, it does not cater to cases where in we have decimal values (Float) in the cell. For example: INR0.001. For this, the formula results in 1.
B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Welcome to the MrExcel board!

It would be good to see a slightly larger set of your sample data and expected results as the best/simplest way to get your results may well depend on just how much variation there is in your actual data.
We can't tell from one example but in the trivial case where all your data was 3 letters followed by a number then the result could be obtained with
=REPLACE(A1,1,3,"")+0
Whereas if there could be quite varied text before and/or after the number then a more complex solution may be required.