Extracting Specific Text from a Cell

[ijhoeq]

Hello, I'm having an issue getting my data sorted out here. I would like to find an 'identifier' from text in a cell and put the identifier in the next column. The identifier is always formatted as two letters-three numbers (XX-###; see the example data). If there is a formula for this that is preferred but using VBA is also fine with me.

Please let me know if you need more info! I appreciate any help!

Text	Identifier
Blah Blah - T2 Something (AB-123)	AB-123
Temperature one -22 - TX (XY-000)	XY-000
Sensor (thermo-1) 23 (IJ-876) - 15	IJ-876
Thermocouple X1 1-1/2, 22-TX (AB-123; PN123x)	AB-123
T2 (PNx123,AB-123,Hs1) Temp 4	AB-123

 

[JEC]

You could try:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2;";";" ");",";" ");"(";"");")";"");" ";"</y><y>")&"</y></x>";"//y[translate(.,'1234567890','')!=. and string-length()=6 and contains(., '-')]"))

 

[JEC]

Or with VBA (which I prefer)

Function jec(cell As String) As Variant
 With CreateObject("VBScript.RegExp")
   .Global = True
   .Pattern = "\D{2}-\d{3}"
   jec = .Execute(cell)(0)
 End With
End Function

Use as:

=jec(A2)

 

[ijhoeq]

Thanks for the reply!

However, for the first proposed solution, I get an error stating "There is a problem with this formula."

For the VBA solution, I get a #NAME? error.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ijhoeq]

Microsoft Office Professional Plus 2019 (excel version 1808 if that helps)

Thanks for the suggestion I'll have to update that.

 

[Fluff]

Ok, the problem is that jec has posted the formula using semi-colons as separators, whilst you probably use commas.

 

[ijhoeq]

Thank you Fluff and JEC. I updated the formula for my version and it is working.

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,";"," "),","," "),"(",""),")","")," ","</y><y>")&"</y></x>","//y[translate(.,'1234567890','')!=. and string-length()=6 and contains(., '-')]"))

 

[JEC]

You're welcome!
For the VBA solution, you need to paste the code behind a standard module first (right click, insert module).

 

[ijhoeq]

Any idea why that formula might not work for Microsoft Office Professional Plus 2013?