Extract Numeric Value From Alphanumeric Text Using VBA RegExp

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

Code:

---

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

---

Quote:

---

Originally Posted by Excel Fox

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

Code:

---

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)...

Code:

---

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

---

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.