Extract Text with Left, Mid, etc

[berniean]

I have 2 spreadsheets that need to talk to each other. One is a product line
plan, the other is a margin model. I need to compare product names on the two
and show any differences on the margin model. The problem is, some of the
products have extra text in the form of size in the margin model, but not in
the line plan. This is necessary because costs will be different for
different sizes. All the names have 3 words, the third being the size.

Margin Model Name will be:
Product One XS
Product One Small
Product One Medium
Product Three XS
Product Three Small
Product Three Medium

Line plan name will be Product One, Product Two, Product Three, etc.

Assuming margin model name in column A, this is the formula I came up with
just to get the text:

=LEFT(A5,FIND(" ",A5)-1)&MID(A5,FIND(" ",A5),6)

This will return Product One, and even Product Two, but not Product Three,
instead yielding Product Thre.

Since the second word is of variable length and the size text is variable,
how do I eliminate the size word and just get the two Product name words?
Once I have that, I can do an IF statement with a VLOOKUP to compare the two
names and return any discrepancies.

Any help greatly appreciated!
Bernie

 

[Jacob Skaria]

Try
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1))

If this post helps click Yes

 

[berniean]

Is it really that simple? That works perfectly! Thank you very much, Jacob.

 

[berniean]

Oops, I spoke too soon. Your formula puts a space at the end of the text.
That defeats the possiblity of a VLOOKUP. Sorry.

 

[Glenn]

=TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)))

Oops, I spoke too soon. Your formula puts a space at the end of the text.
That defeats the possiblity of a VLOOKUP. Sorry.

 

[Jacob Skaria]

Try the below

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

If this post helps click Yes

 

[berniean]

Yes, that works. As does Glenn's Trim function.

Try the below

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

If this post helps click Yes

 

[berniean]

Yes, that works, as does Jacob's -1)

=TRIM(LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)))

 

[anandydr]

Try using

=MID(A9,1,FIND(" ",A9,FIND(" ",A9)+1))
Anand
09910548139