Extract Only Numbers From Text String

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,245
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.
 
...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.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:
Upvote 0
. 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:
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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 ":" ??
 
Upvote 0
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
 
Upvote 0
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
(555) 555-5555
555-555-5555 C.
Cell - 555-555-5555
Home 555 555 5555

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 141px;"><col style="width: 92px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="align: right"]5555555555[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]5555555555[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]5555555555[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]5555555555[/TD]

</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) ?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

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