Can I use the FIND function to find two values in Excel

[angelad66]

I have a string of characters in a cell that are separated by a "/" or a "-", for example,
TRD-/TYU/OPM
DE-/GFU/REA
MDDR/LMV/OPG
YTT/IKM

I only need the first characters of the string so I am using this formula, =LEFT(D2,FIND("-",D2)-1) which returns the following
TRD
DE
#VALUE!
#VALUE!

If I replace the "-" with "/" then I get
#VALUE!
#VALUE!
MDDR
YTT

How do a combine the two statements to work for all instances of "-" or "/" in my data?

 

[Joe4]

Welcome to the Board!

If every entry has either "-" or "/", then this should work:

=LEFT(D2,IFERROR(FIND("-",D2),FIND("/",D2))-1)

 

[Fluff]

How about

=LEFT(D2,FIND("-",SUBSTITUTE(D2,"/","-"))-1)

 

[JEC]

Or

=LEFT(A1,MIN(SEARCH({"-","/"},A1&"-/")-1))

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B
1
2	TRD-/TYU/OPM	TRD
3	DE-/GFU/REA	DE
4	MDDR/LMV/OPG	MDDR
5	YTT/IKM	YTT
Sheet27

Cell Formulas
Range	Formula
B2:B5	B2	=LEFT(A2,MIN(FIND({"-","/"},A2&"-/"))-1)

 

[JEC]

Same thoughts

 

[Joe4]

Lots of subtly different ways to approach this one!

 

[angelad66]

Welcome to the Board!

If every entry has either "-" or "/", then this should work:

=LEFT(D2,IFERROR(FIND("-",D2),FIND("/",D2))-1)

This worked. Thank you

 

[Fluff]

Lots of subtly different ways to approach this one!

That's the beauty of Excel, plenty of ways to skin a

 

[Joe4]

This worked. Thank you

You are welcome.
Glad we were able to help!