Extract Numeric Value From Alphanumeric Text Using VBA RegExp

[Excel Fox]

In continuation to the Excel formula based solution given at http://www.excelfox.com/forum/f13/extract-number-alphanumeric-text-186/, here's one using VBA regular expression


Function ExtractNumFromAlphaNum(strAlNum As String) As Long

Const strPatter As String = "[\d+]"
Dim var, strOutput As String

With CreateObject("vbscript.regexp")
.Pattern = strPatter
.Global = True
Set var = .Execute(strAlNum)
For Each var In var
strOutput = strOutput & var
Next var
End With

ExtractNumFromAlphaNum = strOutput

End Function

[Rick Rothstein]

In continuation to the Excel formula based solution given at http://www.excelfox.com/forum/f13/extract-number-alphanumeric-text-186/, here's one using VBA regular expression


Function ExtractNumFromAlphaNum(strAlNum As String) As Variant
...<<snip>>
End Function

You should change your function's declared data type to Variant as I show in red above in order to stop the error that is generated when an argument containing no digits is passed into the function.

From my standpoint, I do not think using the heavy "machinery" of the Regular Expression engine is necessary for such a simple requested functionality; here is a Function using standard built-in VBA functions that will do the same thing (note I changed the function's name to something easier to type)...

Function GetDigits(strAlNum As String) As Variant
Dim X As Long
For X = 1 To Len(strAlNum)
If Mid(strAlNum, X, 1) Like "#" Then GetDigits = GetDigits & Mid(strAlNum, X, 1)
Next
End Function

[Excel Fox]

I like the way you term it 'heavy machinery'... I completely agree.... It's just that I wanted to share a simple method to show how this bazooka can be used.