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

1. Re: Extract Only Numbers From Text String

Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))  Reply With Quote

2. Re: Extract Only Numbers From Text String Originally Posted by dalbega Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
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.  Reply With Quote

3. Re: Extract Only Numbers From Text String Originally Posted by dalbega Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
And, when it comes to Excel 2016, there is a simpler formula:

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

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?

Thanks for your time more for your brain power.  Reply With Quote

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

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

7. Re: Extract Only Numbers From Text String Originally Posted by asad Works perfect. Just beware - if you subsequently insert any rows at the top of the sheet, the formula will return an incorrect result.  Reply With Quote

8. Re: Extract Only Numbers From Text String Originally Posted by asad 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?

Thanks for your time more for your brain power.

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

9. Re: Extract Only Numbers From Text String Originally Posted by asad 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)))))  Reply With Quote

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"  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
•