Extract words strings with space before a specific word

[myespooir]

Hi there,

I’m trying to separate style and color.

My file has products like
ABC top in red
ACF bottoms in green
A C B top in blue

I need to extract ABC,ACF and A C B in these examples.

Can you help think of a formula that helps?

Thanks a lot!!
Chris

 

[footoo]

If the data to extract is always 3 characters, and it is OK to return the characters without spaces (i.e. ACB instead of A C B) :
=LEFT(SUBSTITUTE(A1," ",""),3)

 

[Rick Rothstein]

Give this array-entered** formula a try...

=LEFT(A1,MIN(IF(CODE(MID(A1,ROW(1:14),1))>96,ROW(1:14),9E+99))-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[myespooir]

Give this array-entered** formula a try...

=LEFT(A1,MIN(IF(CODE(MID(A1,ROW(1:14),1))>96,ROW(1:14),9E+99))-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

The array returns the whole text string.

I want to extract the string before "top" or "Bottom". But this formula does not work either.
=TRIM(LEFT(C3,SEARCH(OR("Bottom","Top"),C3)-1))

 

[jeffngogui]

You can try this formula :
=LEFT(C3,(IFERROR(SEARCH("top",C3),SEARCH("bottom",C3))-2))

 

[myespooir]

Thanks Jeff,

This formula works like magic!

A small problem, there is a third word, "Suit", in addition to "top" and "Bottom". the cells with text strings "Suit" are #VALUE .
I can make an if statement of your formula, to add suit in the mix, just wondering if you can a more straight forward way to do it?

like =iferror(LEFT(C3,(IFERROR(SEARCH("top",C3),SEARCH("bottom",C3))-2)),LEFT(C3,(IFERROR(SEARCH("suit",C3),"")-2)))

 

[jeffngogui]

The edited formula you have written works,
OR you can also use nested IF like :
=LEFT(C3,(IFERROR(SEARCH("top",C3),IFERROR(SEARCH("bottom",C3),SEARCH("suit",C3)))-2))

 

[myespooir]

Thanks so much!! Works perfectly!!