Using multiple FIND with TRIM & SUBSTITUTE

[Executioner]

Hi everyone, I'm using Excel version 2016.

I have a spreadsheet with 7,517 rows of data in column B. I currently have this formula for finding "EMP" in the text:
=TRIM(IF(ISNUMBER(FIND("-",MID(B2,FIND("EMP",B2),10))),SUBSTITUTE(MID(B2,FIND("EMP",B2),10),"-",""),MID(B2,FIND("EMP",B2),10)))

I need to also include "ES" and "TS" text. I tried reformatting the formula, but with no luck. Each text in B contains either:
EMP#######
ES#####
TS#####

The "#" represents numbers after the letters. So a correct formula would return "EMP2010018" for example. My current formula works fine, but I also need it to locate the ES and TS information. Any suggestions are greatly appreciated.

 

[Scott Huish]

OK, the problem was is that your data is not consistent, some of the spaces are actual spaces, some are non-breaking spaces and some don't have some spaces in that part of the data at all.

Try this:
=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(B2,CHAR(160),"")," ",""),LOOKUP(9.99999999E+307,FIND({"-EMP","-ES","-TS"},SUBSTITUTE(SUBSTITUTE(B2,CHAR(160),"")," ","")))+1,255),"-",REPT(" ",255)),255)),"not found")

Copy down.

 

[Executioner]

Hi Scott. Thanks for taking the time to review this. I still have no idea how your formula works. I'm familiar with some of the commands, but I've never have used:
IFERROR
CHAR
LOOKUP
REPT
As for the data, I used a program called "Cognos" that pulls data from a database program called "Maximo". I thought maybe by changing the data to text, it would fix the issue, but no cigar.

 

[Scott Huish]

I tried my formula on your spreadsheet and it worked fine, how is it not working exactly?