Get last block of numbers on a string

Question

Assuming that I have the following list:

2John n3
Mi33chael 445
321Peter 1234DD
44434Jack       44
Anna333Y

Is it possible to get the last digit (or block of digits) on each line via VBA?

I mean, I need the output (in this case) be:

Possible duplicate of [Regular expression to match last number in a string](http://stackoverflow.com/questions/5320525/regular-expression-to-match-last-number-in-a-string) — Daniel, Dec 26 '16 at 11:40

score 2 · Accepted Answer · answered Dec 26 '16 at 12:09

Try the UDF below :

Function GetLastDigits(byMixedString As String) As Variant

    Dim Entries As String
    Dim RegEx As Object, Matches As Object, Match As Object

    Entries = byMixedString
    Set RegEx = CreateObject("vbscript.regexp")

    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "(\d+)" ' Match any set of digits
    End With

    Set Matches = RegEx.Execute(Entries)

    If VBA.Left(Matches(Matches.Count - 1), 1) = 0 Then
        GetLastDigits = VBA.Right(Matches(Matches.Count - 1), Len(Matches(Matches.Count - 1)) - 1)
    Else
        GetLastDigits = Matches(Matches.Count - 1)
    End If

End Function

Worksheet results running this UDF (you need to enter the Mixed String inside the Formula brackets bar):

I modified some parts to match my needs, and that was the thing I need! — Filipe Pires, Dec 26 '16 at 12:25

Get last block of numbers on a string

1 Answers1