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.

 

[anglais428]

Try something like =IFERROR(TRIM(IF(ISNUMBER(FIND("EMP",B2)),IF(ISNUMBER(FIND("-",MID(B2,FIND("EMP",B2),10))),SUBSTITUTE(MID(B2,FIND("EMP",B2),10),"-",""),MID(B2,FIND("EMP",B2),10)),IF(ISNUMBER(FIND("ES",B2)),IF(ISNUMBER(FIND("-",MID(B2,FIND("ES",B2),10))),SUBSTITUTE(MID(B2,FIND("ES",B2),10),"-",""),MID(B2,FIND("ES",B2),10)),IF(ISNUMBER(FIND("-",MID(B2,FIND("TS",B2),10))),SUBSTITUTE(MID(B2,FIND("TS",B2),10),"-",""),MID(B2,FIND("TS",B2),10))))),"not found")

 

[Scott Huish]

It might be possible to have a shorter formula if you could supply some actual samples of data and the results you expect.

 

[Executioner]

Thanks anglais428! That worked.

Scott - yeah the formula is very long. If a shorter formula would work, I would be interested. The data from column B looks like this:
SPLICE JOINT BONDER - 3 MONTHS - EMP2052240
PLEAT & FOLD - 1 YEAR - EMP2049244 - PROCEDURE B & C
Guide Hypotube Holder 1 YEAR - TS01841
Lock Line slack gage 1 YEAR - TS03691
Zebra Thermal Transfer Label Printer TLP 2824 2 MONTH - ES03624
USON SPRINT LC MULTI AIR TESTER SEQUENCED PM- ES01322 (NOTE: on this one, the formula that anglasis428 gave me, the result is: ESTER SEQU. Not a big deal since I can correct that manually.

 

[Scott Huish]

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

Copy down.

Excel 2010
	B	C
2	SPLICE JOINT BONDER - 3 MONTHS - EMP2052240	EMP2052240
3	PLEAT & FOLD - 1 YEAR - EMP2049244 - PROCEDURE B & C	EMP2049244
4	Guide Hypotube Holder 1 YEAR - TS01841	TS01841
5	Lock Line slack gage 1 YEAR - TS03691	TS03691
6	Zebra Thermal Transfer Label Printer TLP 2824 2 MONTH - ES03624	ES03624
7	USON SPRINT LC MULTI AIR TESTER SEQUENCED PM- ES01322	ES01322
Sheet1

Cell Formulas
Range	Formula
C2	=TRIM(LEFT(SUBSTITUTE(MID(B2,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},B2)+1),255),"-",REPT(" ",255)),255))
C3	=TRIM(LEFT(SUBSTITUTE(MID(B3,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},B3)+1),255),"-",REPT(" ",255)),255))
C4	=TRIM(LEFT(SUBSTITUTE(MID(B4,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},B4)+1),255),"-",REPT(" ",255)),255))
C5	=TRIM(LEFT(SUBSTITUTE(MID(B5,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},B5)+1),255),"-",REPT(" ",255)),255))
C6	=TRIM(LEFT(SUBSTITUTE(MID(B6,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},B6)+1),255),"-",REPT(" ",255)),255))
C7	=TRIM(LEFT(SUBSTITUTE(MID(B7,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},B7)+1),255),"-",REPT(" ",255)),255))

 

[Executioner]

Thanks Scott. All I get is #N/A for results. I did hold the CTRL-SHIFT-ENTER keys due to the formula, but still get the #N/A.

 

[Scott Huish]

You don't need CTRL-SHIFT-ENTER. Does your data not look like your samples?

All of your codes in your sample are preceded by a hyphen and then a space. Is this consistent with your data?

 

[Executioner]

I tried it both ways without the CTRL-SHIFT-ENTER and still get #N/A. I even copied your sheet 1 examples of text on a new sheet and I get the same value. I can send you a copy of the excel sheet via PM with email address if you like.

 

[anglais428]

You just need to change the cell reference to Column A (rather than Column B).
e.g. In cell C2 it should be =TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},A2)+1),255),"-",REPT(" ",255)),255))

 

[Executioner]

You just need to change the cell reference to Column A (rather than Column B).
e.g. In cell C2 it should be =TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9.99999999E+307,FIND({"- EMP","- ES","- TS"},A2)+1),255),"-",REPT(" ",255)),255))

Hi anglais428. The data is in column B not A. What I discovered is it has to do with some hidden format of column B text. If I type in the text data, it does work, but it won't work with my spreadsheet, probably due to some hidden blanks or other hidden symbols that I can't see on the screen. I even tried the format painter option with the same results.

As a test, I deleted the following text from B: LASER WELDER - 1 YEAR - EMP2009334
After deletion, I manually typed in B: LASER WELDER - 1 YEAR - EMP2009334

and the formula will then work! I then tried the format painter on the next 5 cells below, and I get the #N/A. The format of the cells is just "general", and I can't see any other text or symbols that would cause it not to work, but it's obvious that it's related to some type of text issue.

I'm willing to send the spreadsheet to you or Scott, since I can't figure out why it does not work when I'm looking at text only. Just PM me with your email address and I'll send you the excel file. I'm just curious why it does not work with my data.