Retreiving only numeric values from a string

[Alex]

There are two parts I am trying to accomplish for a set of data I have in
column A.

What I have Results I would like
A1:kkdd55dd B1:55
A2:eaa5788ee B2:5788
A3:sfse B3:455
A4:
A5:
A6:455dae

If possible I wanted to have a function where it would Search at a range
from A1:A10 and place the first set of numbers it found (always be together)
in B1, the second set of numbers it finds placed in B2 and so on. As a
reminder I cannot download any type of files if that is to be suggested.

-thank you

 

[Norman Jones]

Hi Alex,

Try posting the following user defined
function into a standard module:

'============= >>
Public Function NumberOnly( _
sStr As String) As Variant
Dim oRegExp As Object

Application.Volatile
Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "/d"
NumberOnly = .Replace(sStr, vbNullString)
If IsNumeric(NumberOnly) Then _
NumberOnly = CDbl(NumberOnly)
End With
End Function
'<<=============

With the data of interest in A1:A10.
enter:

=NumberOnly(A1)

in B1 and drag the formula down to B10.

 

[Mike H]

Try this non VBA attempt

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$1000),1))))

Mike

 

[Norman Jones]

Hi Alex.

As Mike has introduced the possibility
of a non VBA approach, see:


Extracting numbers from alphanumeric strings
http://office.microsoft.com/en-us/excel/HA011549011033.aspx

 

[Norman Jones]

Hi Alex,

oRegExp.Pattern = "/d"

Should read:

oRegExp.Pattern = "\D+"

and:

Application.Volatile

is unnecessary.

Therefore, replace the suggested function with:

'============= >>
Public Function NumberOnly( _
sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D+"
NumberOnly = .Replace(sStr, vbNullString)
If IsNumeric(NumberOnly) Then _
NumberOnly = CDbl(NumberOnly)
End With
End Function
'<<=============

 

[Alex]

Thank you Norman and Mike, both methods worked great. I appreciate both
responses.