VBA - keep the last occurrence of a pattern in a cell

[JofFrey]

Hello everyone,

I have cells that contain a mix of information. However in each cell there is at least one occurrence of the pattern ABCD1234, so 4 letters followed by 4 numbers. I think this is in regex [A-Z]{4}[0-9]{4}.
I need to keep the last occurrence of this pattern in each cell so for example a cell can contain:

REG/ABCD EE /44 AACD0015 BCAD1401 BEL/ TAR / REDA15

EFP/EGTA0017

AACD0715 AACD1115 AACD0325 AACD0015 A RML/ CONTACT 003348976

The sollution would give me:
BCAD1401
EGTA0017
AACD0015

I really have no idea how I can pull of this feat so any help is more than welcome.

 

[JvdV]

Nice question. For Excel 2013 and above (apart for web or mac) one could also use functions instead of VBA:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1," ","/"),"/","</s><s>")&"</s></t>","//s[string-length()=8][translate(substring(.,1,4), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][substring(.,5,4)*0=0][last()]")

 

[JvdV]

For VBA, maybe a regular expression is easiest?

Public Function RegexExtract(s As String) As String

Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "\b[A-Z]{4}\d{4}\b"
RE.Global = True

Set REMatches = RE.Execute(s)
RegexExtract = REMatches(REMatches.Count - 1)

End Function

 

[JofFrey]

Nice question. For Excel 2013 and above (apart for web or mac) one could also use functions instead of VBA:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1," ","/"),"/","</s><s>")&"</s></t>","//s[string-length()=8][translate(substring(.,1,4), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][substring(.,5,4)*0=0][last()]")

Hi, that one didn't work for me. I think you're dutch right so i changed all the commas to semicommas and cell A1 to the cell containing the pattern. As a result I get #VALUE!

 

[JvdV]

I am Dutch indeed, this worked for me which I translated:

=XML.FILTEREN("<t><s>"&SUBSTITUEREN(SUBSTITUEREN(A1;" ";"/");"/";"</s><s>")&"</s></t>";"//s[string-length()=8][translate(substring(.,1,4), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][substring(.,5,4)*0=0][last()]")

Note: Don't change the commas inside the xpath expressions!

 

[JofFrey]

For VBA, maybe a regular expression is easiest?

Public Function RegexExtract(s As String) As String

Static RE As Object: If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "\b[A-Z]{4}\d{4}\b"
RE.Global = True

Set REMatches = RE.Execute(s)
RegexExtract = REMatches(REMatches.Count - 1)

End Function

Thank you very much JvdV, it works like a charm. Now I am trying to figure out how it works so I can add it to my knowledge.
Nogmaals dank,

Joffrey