# Extract Only Numbers From Text String

#### Rick Rothstein

##### MrExcel MVP
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.

#### István Hirsch

##### Well-known Member
And, when it comes to Excel 2016, there is a simpler formula:

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

##### Well-known Member
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?

#### MARK858

##### MrExcel MVP
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)

##### Well-known Member
Try the below as it seems to work for your examples posted...
Brilliant.
Works perfect.
Thanks a lot.

#### Peter_SSs

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

#### István Hirsch

##### Well-known Member
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.

#### Rick Rothstein

##### MrExcel MVP
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?
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)))))

Last edited:
• MARK858

#### Peter_SSs

##### MrExcel MVP, Moderator
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"