On Sat, 14 Apr 2007 17:51:04 -0400, "Dana DeLouis"
wrote:

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