Extract Only Numbers From Text String

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
...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
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
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
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
. 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
Joined
Apr 5, 2015
Messages
13
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
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
Ron, you just saved my day my friend. Thank you
Some follow-up comments ..

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
Joined
Mar 13, 2018
Messages
3
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
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
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
Joined
Aug 15, 2011
Messages
3
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

AB
1(555) 555-55555555555555
2555-555-5555 C.5555555555
3Cell - 555-555-55555555555555
4Home 555 555 55555555555555

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

Spreadsheet Formulas
CellFormula
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
Joined
Apr 18, 2011
Messages
35,568
Office Version
2010
Platform
Windows
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
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
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)
 

Forum statistics

Threads
1,085,370
Messages
5,383,241
Members
401,820
Latest member
RustEE2020

Some videos you may like

This Week's Hot Topics

Top