Extract Only Numbers From Text String

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.
 
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)),"")))
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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]";"")
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top