How to select numeric characters only?

[ophelia]

Hi

I need to be able to pull numeric characters only out of a list of names into another column on the same line. The characters are not always in the same position. E.g.

Example 1234
Example of (1234)
Example (3567) & Co
Longer Example of 4567 & Co


Is there a function that I can use to do this?

Advice greatly appreciated!

 

[BrianB]

Here is a custom function. Use it same as any other with something like :-
=getnumeric(A1).

Public Function GetNumeric(v As Variant) As Variant
    Dim Vstr As String
    Vstr = CStr(v)
    GetNumeric = ""
    For n = 1 To Len(Vstr)
        c = Mid(Vstr, n, 1)
        If IsNumeric(c) Then
            GetNumeric = GetNumeric & c
        End If
    Next
End Function

 

[al_b_cnu]

Hi, Welcome to the board!

a VBA solution:

Function ExtractNumbers(ByVal InputString As String) As Variant
Dim iPtr As Integer, iLen As Integer
Dim sChar As String
Dim vResult As Variant
For iPtr = 1 To Len(InputString)
    sChar = Mid$(InputString, iPtr, 1)
    If IsNumeric(sChar) Then vResult = vResult & sChar
Next iPtr
ExtractNumbers = Val(vResult)
End Function

To install, [Alt-F11] Insert / Module & paste above into code window.

Example of use:

Book1
	A	B	C	D
1	extract 1234	1234
2	zzzz 2345 xxxx	2345
3	a1b2c3	123
4	zxcvb	0
Sheet1

Formula in B1 & copied down is =ExtractNumbers(A1)

 

[ophelia]

As I feared, it's a bit more complicated than my limited knowledge but fortunately someone here looked at that and said confidently, "yes, that will work!"

Many thanks!