Extract numbers from string

[bebo021999]

I have ABC123/045-0XYZ?85T in cell A1, I would like to get in B1 like this: 123045085.
That mean which formula to extract numbers from string?

 

[bebo021999]

More information: numbers can be placed anywhere in string: i.e. "ABC123/5-0T", "102-85?","11ZA99#"...

 

[GTO]

As long as they are to be longs, this seems to work.

Excel Workbook
	B	C
1	Before	After
2	ABC123/045-0XYZ?85T	123045085
3	ABCD	No Numbers
4	123.456	123456
5	ABC123/5-0T	12350
6	102-85?	10285
7	11ZA99#	1199
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C2	=RETNUMERICS(B2)
C3	=RETNUMERICS(B3)
C4	=RETNUMERICS(B4)
C5	=RETNUMERICS(B5)
C6	=RETNUMERICS(B6)
C7	=RETNUMERICS(B7)

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> RetNumerics(<SPAN style="color:#00007F">ByVal</SPAN> CellText<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Static</SPAN> REX<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> REX<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Set</SPAN> REX = CreateObject("VBScript.RegExp")<br>    <SPAN style="color:#00007F">With</SPAN> REX<br>        .Global =<SPAN style="color:#00007F">True</SPAN><br>        .Pattern = "[0-9]"<br>        <SPAN style="color:#00007F">If</SPAN> .test(CellText)<SPAN style="color:#00007F">Then</SPAN><br>            .Pattern = "[^0-9]"<br>            RetNumerics =<SPAN style="color:#00007F">CLng</SPAN>(.Replace(CellText, vbNullString))<br>        <SPAN style="color:#00007F">Else</SPAN><br>            RetNumerics = "No Numbers"<br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Hope that helps,

Mark

 

[Trebor76]

Here's a number of other suggestions.

 

[Rick Rothstein]

Here is a formula solution that was posted originally to the old Microsoft newsgroups by Lars-Åke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string. (Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).

 

[Biz]

Another solution

use Formula in cell C2
=AlphaNum(B2,FALSE)

Function can extract Alphabets if set True.

Function AlphaNum(txt As String, Optional Alpha As Boolean = True) As String
'Use in cell like _
=AlphaNum(A1,True) '<- True for Alphabets, False for Numbers
 '// \d+ Matches a digit character of any length.
 '// \D Matches a nondigit character of any length.
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(Alpha, "\d+", "\D+")
    .Global = True
    AlphaNum = .Replace(txt, "")
End With
End Function

Biz

 

[Rick Rothstein]

If you are up for a VB solution... that is, a UDF (user defined function) as others have offered... then here is a non-RegExp one for you to consider.

Function GetDigits(ByVal Text As String) As String
  Dim X As Long
  For X = 1 To Len(Text)
    If Not Mid(Text, X, 1) Like "#" Then Mid(Text, X) = " "
  Next
  GetDigits = Replace(Text, " ", "")
End Function

 

[bebo021999]

Thanks everybody!