CODEWITHSUNDEEP
excelvbaalphanumeric
Dogulas
Dogulas

Reputation: 45

Extract last alpha+numeric pair in a string in Excel

I'm trying to figure out a way to extract the last alpha+numeric sequence in a string made up of similar patterns. The sequence is an alpha+numeric pair: an alpha string (one or more letters) plus a numeric string (one or more numbers). For instance:

G98Y8RT9 -- I need to isolate "RT9"

H8L77 -- I need to isolate "L77"

D64RL19HT7899 -- I need to isolate "HT7899"

As shown above, there are a variable number of characters in each part of the pair and also in the number of pairs preceding the last one. I've tried Excel formulas using FIND, ISNUMBER, etc., but I couldn't figure out the logic to make it work for these variables.

Is there a formula that would help? Or is some kind of regex VBA function the way to go?

Upvotes: 0

Views: 1090

Answers (2)

István Hirsch
István Hirsch

Reputation: 122

A bit long formula, but seems to work:

=RIGHT(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,LEN(A1)-MATCH(FALSE,ISNUMBER(1*MID(A1,LEN(A1)-{0,1,2,3,4,5,6,7,8},1)),0)-{0,1,2,3,4,5,6,7,8},1)),0)+MATCH(FALSE,ISNUMBER(1*MID(A1,LEN(A1)-{0,1,2,3,4,5,6,7,8},1)),0)-1)

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

I think this should work, as a user-defined function you can place it in a standard module, and call it like:

=GetLastPair($A$1), etc.

Here is the function:

Function GetLastPair(str As String)
Dim numPart As Integer
Dim strPart As Integer

Do Until Not IsNumeric(Mid(str, Len(str) - numPart, 1))
    numPart = numPart + 1
Loop

Do Until IsNumeric(Mid(str, Len(str) - numPart - strPart, 1))
    strPart = strPart + 1
Loop

GetLastPair = Right(str, numPart + strPart)

End Function

Results:

enter image description here

Upvotes: 1

Related Questions