What a good idea. If you don't want to install that DLL, you could use
Harlan Grove's SubStr regular expression UDF, and then use this formula
=--SubStr(SubStr(A1,"[^0-9 \t]{2,}"," "),"[^0-9]","")
'-----------------------------------------
Function Substr(orig_text As String, _
match_pat As String, _
replace_pat As String, _
Optional instance As Variant) As Variant
'-----------------------------------------
'Similar to Excel's SUBSTITUTE but using VBScript's perl-like regexps
'Required:
' orig_text - string to search through,
' match_pat - regexp to find,
' replace_pat - replacement pattern
'Optional:
' instance - which matched substring to replace or 0 for all (default)
'-----------------------------------------
Dim regex As Object, matches As Object, m As Object
If IsMissing(instance) Then
instance = 0
ElseIf TypeName(instance) <> "Double" Then
Substr = CVErr(xlErrValue) 'invalid instance type
instance = -1
ElseIf CDbl(instance) <= 0.5 Then
Substr = CVErr(xlErrNum) 'invalid instance value
instance = -1
Else
instance = Int(instance + 0.5)
End If
If instance = -1 Then Exit Function 'do nothing quickly
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True
If instance = 0 Then
Substr = regex.Replace(orig_text, replace_pat)
Else
Set matches = regex.Execute(orig_text)
If instance > matches.Count Then
Substr = orig_text 'matchnum out of bounds - do nothing
Else
Set m = matches.Item(instance - 1)
Substr = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
End If
End Function
--
HTH
RP
(remove nothere from the email address if mailing direct)
Ron Rosenfeld said:
If you just have single numbers, as in the above, then
1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/
2. Then use this formula:
=REGEX.MID(A1,"\d+")
--ron
)