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

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

Originally Posted by dalbega
Unless something has changed with the non-subscription version of Excel 2016 (I don't have it, so I don't know), according to Microsoft, TEXTJOIN is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets. So at the moment, that formula is not usable by the majority of Excel users.

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

Originally Posted by dalbega
And, when it comes to Excel 2016, there is a simpler formula:

=regexreplace(A2;"[^0-9]";"")

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

The following formula worked perfect for me until I got to a set of values that gave me strange answers.
Originally Posted by Rick Rothstein

=-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
My data has values like
36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.

Anything I can do to fix this problem?

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

Try the below as it seems to work for your examples posted...

=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)

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

Originally Posted by MARK858
Try the below as it seems to work for your examples posted...
Brilliant.
Works perfect.
Thanks a lot.

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

Works perfect.
Just beware - if you subsequently insert any rows at the top of the sheet, the formula will return an incorrect result.

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

The following formula worked perfect for me until I got to a set of values that gave me strange answers.

My data has values like
36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.

Anything I can do to fix this problem?

The problem, as usual, is caused by the tendency of excel to convert everything to date or time value. In this case the problem arises when the first digit in the cell is followed by a space and a "p" or "a". Excel interpretes this situation as 9 pm or 9 am and converts them to time. So the solution (in the framework of the formula above) is avoiding this structure to occur.

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

The following formula worked perfect for me until I got to a set of values that gave me strange answers.

=-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
My data has values like
36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.

Anything I can do to fix this problem?
Originally Posted by István Hirsch
The problem, as usual, is caused by the tendency of excel to convert everything to date or time value. In this case the problem arises when the first digit in the cell is followed by a space and a "p" or "a". Excel interpretes this situation as 9 pm or 9 am and converts them to time. So the solution (in the framework of the formula above) is avoiding this structure to occur.
Give this a try...

=-LOOKUP(0,-LEFT(MID(SUBSTITUTE(SUBSTITUTE(A1,"P","X"),"A","X"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

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

Originally Posted by Rick Rothstein
Give this a try...

=-LOOKUP(0,-LEFT(MID(SUBSTITUTE(SUBSTITUTE(A1,"P","X"),"A","X"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
For the limited examples this poster had that may work, but as a general extractor it is not reliable.
eg "9DECT"