Extract Only Numbers From Text String

István Hirsch

Well-known Member
...Or, if the strings you work with do not contain anything but pax and px (besides the digits) simply "de-paxing" ("de-pxing") the string, delete the periods and then only the digits will be left.

Rick Rothstein

MrExcel MVP
For the limited examples this poster had that may work, but as a general extractor it is not reliable.
eg "9DECT"
We can add date handling like this...

=-LOOKUP(0,-LEFT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"P","X"),"A","X"),"E","X"),"J","X"),"O","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)))))

I know... I know... this is starting to get ridiculous.

Last edited:

Peter_SSs

MrExcel MVP, Moderator
. this is starting to get ridiculous.
More than starting I think. With only 8 rows of formulas they are taking around half a second to calc on my poor old machine.

In any case do we actually know what we are trying to extract fore the last questioner? The samples have some "." in them so perhaps it is possible to have decimal numbers? Could there be negative numbers?
Without specific details it is something of a guessing game.

So, I'll have a guess too.
If we are trying to extract a positive or zero leading number (whole or decimal) then may be array-entered (Ctrl+Shift+Enter)
=LEFT(A1,MATCH(TRUE,ABS(51.5-CODE(MID(SUBSTITUTE(A1&"|","/","|"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)+1)),1)))>=6,0)-1)+0

.. or if you don't want the array entry
=LEFT(A1,MATCH(TRUE,INDEX(ABS(51.5-CODE(MID(SUBSTITUTE(A1&"|","/","|"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)+1)),1)))>=6,0),0)-1)+0

Last edited:

Lazerus3511

New Member
Ron, you just saved my day my friend. Thank you

Try this:

With
A1 containing an alphanumeric string: eg 9128ABC37DEF465

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER) returns ONLY the numbers:
Code:
``````B1: =SUM(MID(A1,LARGE(ISNUMBER(--MID(A1,ROW(\$1:\$25),1))*
ROW(\$1:\$25),ROW(\$A\$1:INDEX(\$A:\$A,COUNT(--MID(A1,ROW(\$1:\$25),1))))),1)*
10^(ROW(INDEX(\$1:\$25,COUNT(--MID(A1,ROW(INDEX(\$1:\$25,1,1):
INDEX(\$1:\$25,LEN(A1),1)),1)),1):INDEX(\$1:\$25,1,1))-1))``````
In the above example, the formula returns: 912837465

That formula works for text up to 25 characters long.

Is that something you can work with?

Peter_SSs

MrExcel MVP, Moderator
Ron, you just saved my day my friend. Thank you

1. Ron pointed out the 25 character limit but if the number of digits in the string was about a dozen or more, the results may not be what you expect anyway.

2. If you happen to have a very recent version of Excel that contains the CONCAT function then you could try this (also confirmed wit Ctrl+Shift+Enter)
=CONCAT(IFERROR(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)+0,""))+0

3. If you might have a lot of digits in the string, then the suggestion in 2. above has the same issue mentioned in 1. above. If that might be your circumstance and you are happy with the result being a text string of digits (of any length in any length string) then leave off the red "+0" (but still confirm with C+S+E).

Last edited:

JohnStu

New Member
Question: If you have in H1) 1ST:1.03WT and in H2) 1 ST : 9 WT -> How can you extract the numbers to the right of the ":" ??

Peter_SSs

MrExcel MVP, Moderator
Question: If you have in H1) 1ST:1.03WT and in H2) 1 ST : 9 WT -> How can you extract the numbers to the right of the ":" ??
If that number is always followed by just "WT" as per your examples ..

Excel Workbook
HI
11ST:1.03WT1.03
21 ST : 9 WT9
Num

macutan

New Member
Perhaps you could consider a user-defined function?
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
``````Function Digits(s As String) As String
Dim i As Long

For i = 1 To Len(s)
If Mid(s, i, 1) Like "#" Then Digits = Digits & Mid(s, i, 1)
Next i
End Function``````
Sheet1

 A B 1 (555) 555-5555 5555555555 2 555-555-5555 C. 5555555555 3 Cell - 555-555-5555 5555555555 4 Home 555 555 5555 5555555555

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 141px;"><col style="width: 92px;"></colgroup><tbody>
</tbody>

 Cell Formula B1 =Digits(A1)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
Love this simple solution.

Quick question, how would the code look like to do the opposite? ie, extract only the nonDigits (ie. the text and ignore the numbers) ?

Rick Rothstein

MrExcel MVP
Quick question, how would the code look like to do the opposite? ie, extract only the nonDigits (ie. the text and ignore the numbers) ?
Does this do what you want...
Code:
``````Function NonDigits(ByVal S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(S, X, 1) Like "#" Then Mid(S, X) = Chr\$(1)
Next
NonDigits = Replace(S, Chr\$(1), "")
End Function``````

Peter_SSs

MrExcel MVP, Moderator
how would the code look like to do the opposite? ie, extract only the nonDigits (ie. the text and ignore the numbers) ?
A simple change to my earlier code would be (changes highlighted)
Rich (BB code):
``````Function ND(s As String) As String
Dim i As Long

For i = 1 To Len(s)
If Not Mid(s, i, 1) Like "#" Then ND = ND & Mid(s, i, 1)
Next i
End Function``````
If you happened to have original text like "a 3 4 5 7 5 b" then this function (& Rick's) would return the "a" & "b" with 6 spaces between them. Similarly "35 trees" would return " trees" (with a space at the front).
If those sorts of things are possible with your data, then it may be that you would want to reduce the first example to a single space and remove that leading space from the second example.
If so, add this line immediately before the "End Function"
Rich (BB code):
``ND = Application.Trim(ND)``