Extract Only Numbers From Text String

[mgirvin]

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.

 

[István Hirsch]

...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]

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.

 

[Peter_SSs]

. 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

 

[Lazerus3511]

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:

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]

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).

 

[JohnStu]

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]

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
	H	I
1	1ST:1.03WT	1.03
2	1 ST : 9 WT	9
Num

 

[macutan]

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)

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>

Spreadsheet Formulas

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]

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...

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]

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)

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"

ND = Application.Trim(ND)