Extracting 1st word of a text string delimited by 1 of 2 characters

[SG51090]

I have strings that I need to pull the first word out of. The problem is, that the first word is either delimited by a space, or an underscore. I have been using the below formula to find the first word if separated by a space, but unclear how to add the OR to look for an underscore.


=IF(ISERR(FIND(" ",A1)),"",LEFT(G10,FIND(" ",A1)-1))

Two exampled of text I would need to pull out:

1.

BigStar Movies - CA - RON - Connected TV / CTV - Video 2. TubiTV_CA - RON (Telaria)- Connected TV / CTV - Video <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

In option 1, I would need to pull in BigStar Movies, and in option 2 I would need to pull in TubiTV, so need a formula that will search for either a space OR an underscore.

Thanks!

 

[Fluff]

How about

Excel 2013/2016
	A	B
13	BigStar Movies - CA - RON - Connected TV / CTV - Video	BigStar
14	TubiTV_CA - RON (Telaria)- Connected TV / CTV - Video	TubiTV
Template

Cell Formulas
Range	Formula
B13	=LEFT(A13,FIND(" ",SUBSTITUTE(A13,"_"," "))-1)

 

[jtakw]

I have strings that I need to pull the first word out of. The problem is, that the first word is either delimited by a space, or an underscore. I have been using the below formula to find the first word if separated by a space, but unclear how to add the OR to look for an underscore.

Two exampled of text I would need to pull out:

1.

BigStar Movies - CA - RON - Connected TV / CTV - Video 2. TubiTV_CA - RON (Telaria)- Connected TV / CTV - Video <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

In option 1, I would need to pull in BigStar Movies, and in option 2 I would need to pull in TubiTV, so need a formula that will search for either a space OR an underscore.

Thanks!

Hi,

Please clarify, for the 1st string, you want the First 2 words, and the 2nd string, you want the First 1 word ??

 

[SG51090]

Thank you! This is exactly what I was looking for.

 

[Fluff]

You're welcome & thanks for the feedback