Find specific text based on criteria

[erilut]

Hello,

After alot of trials and errors, I need help finding out how to extract a specific word from a cell that contains more than one word in Excel.

For example if the cell A1 contains this text: "Example Example1 ST95999 Example2"
I need a formula that will extract "ST95999" to B2, based on the criteria that i know the word I am looking for is 7 digits long and that it is built up with two letters at the start and 5 numbers at the end.

 

[p45cal]

Would a user defined function do?
See column B in the attached.
Use thus:
=myCode(A2)

Function myCode(s)
With CreateObject("VBScript.RegExp")
  .Pattern = "\D{2}\d{5}"
  .Global = False
  Set Match = .Execute(s.Value)
  If Match.Count > 0 Then myCode = Match(0) Else myCode = "not found"
End With
End Function

 

[erilut]

This is perfect! Thanks alot

 

[bosco_yip]

Extract word from a string with 2 letters at the start and 5 numbers at the end

Here is a formula solution using Filterxml function for your consideration.

In B2, formula copied down :

=FILTERXML("<a><b>"&SUBSTITUTE(A2," ","</b><b>")&"</b><c><![CDATA[ ]]></c></a>","//b[string-length()=7][substring(., 1,2)*0!=0][substring(., 3,7)*0=0]|//c")