Extract 5 digit number from string

[Dana DeLouis]

A non-capturing, positive lookahead pattern seems to avoid the issue I had
with testing "12345A56789"
The key for me was to wrap the string in something we're not looking for to
avoid Beginning / Ending issues. I've borrowed Ron's CreateObject
statement.

Function Last5(Str As String)
Dim Re As Object
Dim M As Object
Dim S As String

Set Re = CreateObject("VBScript.RegExp")

With Re
.IgnoreCase = True
.Global = True
.Pattern = "\D(\d{5})(?=\D)"

S = "x" & Str & "x"
If .Test(S) Then
Set M = Re.Execute(S)
Last5 = M.Item(M.Count - 1).SubMatches(0)
Else
Last5 = "None"
End If
End With
End Function

 

[Ron Rosenfeld]

Thanks Ron! This one works exactly the way I wanted!
Thank you all for your help!

Chris

Glad to help, Chris. Thanks for the feedback. Having your examples certainly
helped.
--ron

 

[Dana DeLouis]

Oh. Now I get it! One just ignores a positive-lookup at the end.
Now it works like I was expecting:

Sub TestIt()
Debug.Print Last5("12345X56789")
End Sub

Returns:
56789

Function Last5(Str As String)
Dim Re As Object
Dim M As Object

Set Re = CreateObject("VBScript.RegExp")
With Re
.IgnoreCase = True
.Global = True
.Pattern = "(?:^|\D)+(\d{5})(?=\D|$)"

If .Test(Str) Then
Set M = .Execute(Str)
Last5 = CDbl(M.Item(M.Count - 1).SubMatches(0))
Else
Last5 = "None"
End If
End With
End Function

 

[Dave Peterson]

And if I had remembered "like "#####"", then I would have used:

Option Explicit
Function ExtractFirst5DigitNumber(myInStr As String) As String

Dim myArr As Variant
Dim iCtr As Long
Dim myOutStr As String

myInStr = Application.Trim(myInStr)
For iCtr = 1 To Len(myInStr)
If Mid(myInStr, iCtr, 1) Like "[0-9,.]" Then
'do nothing
Else
Mid(myInStr, iCtr, 1) = " "
End If
Next iCtr
myInStr = Application.Trim(myInStr)

myArr = Split(myInStr, " ")
myOutStr = ""
For iCtr = LBound(myArr) To UBound(myArr)
If myArr(iCtr) Like "#####" Then
myOutStr = myArr(iCtr)
Exit For
End If
Next iCtr

If myOutStr = "" Then
ExtractFirst5DigitNumber = "None"
Else
ExtractFirst5DigitNumber = myOutStr
End If

End Function

Even though I like JE's code better, I kind of like the idea of building a
string of valid characters and then splitting it into pieces. I think it would
be easy to change if more than one 5 digit number had to be returned.

 

[Ron Rosenfeld]

Oh. Now I get it! One just ignores a positive-lookup at the end.
Now it works like I was expecting:

Sub TestIt()
Debug.Print Last5("12345X56789")
End Sub

Returns:
56789

Function Last5(Str As String)
Dim Re As Object
Dim M As Object

Set Re = CreateObject("VBScript.RegExp")
With Re
.IgnoreCase = True
.Global = True
.Pattern = "(?:^|\D)+(\d{5})(?=\D|$)"

If .Test(Str) Then
Set M = .Execute(Str)
Last5 = CDbl(M.Item(M.Count - 1).SubMatches(0))
Else
Last5 = "None"
End If
End With
End Function

And if you want to return the i'th 5 digit string, you could use something like
this:


Function Extr5D(str As String, Optional i As Long = 1) As String
Dim oRegExp As Object
Dim colMatches As Object
Const sPattern As String = "(^|[^0-9,.])(\d{5})(?=\D|$)"

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
.Pattern = sPattern
If .Test(str) = True Then
Set colMatches = .Execute(str)
If i > colMatches.Count Then Exit Function
Extr5D = colMatches(i - 1).submatches(1)
End If
End With

End Function

--ron