CODEWITHSUNDEEP
excelstringvba
Filipe Pires
Filipe Pires

Reputation: 147

Get last block of numbers on a string

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:

3
445
123
44
333

Upvotes: 1

Views: 138

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

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

enter image description here

Upvotes: 2

Related Questions