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)  Reply With Quote

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?  Reply With Quote

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.  Reply With Quote

User Tag List

Tags for this Thread

extract numbers string  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•