# Thread: Extract Only Numbers From Text String Thanks: 0 Likes:  3 Post #2194891 (1)Post #2195554 (1)Post #2195604 (1)

1. ## Re: Extract Only Numbers From Text String

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)

2. ## Re: Extract Only Numbers From Text String

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:
Code:
`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?

3. ## Re: Extract Only Numbers From Text String

Originally Posted by 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)
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.