Extract Only Numbers From Text String

dalbega

New Member
Joined
Mar 10, 2014
Messages
22
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)),"")))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
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.
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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]";"")
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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?

Thanks for your time more for your brain power.

Asad
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,747
Office Version
365, 2010
Platform
Windows, Mobile
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)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,686
Office Version
365
Platform
Windows
Works perfect.
:eek: 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
Joined
May 16, 2013
Messages
1,634
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.

Asad
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
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,686
Office Version
365
Platform
Windows
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"
 

Forum statistics

Threads
1,085,693
Messages
5,385,214
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top