Keep first word of cell - but ignore if cell only contains one word

[kradclayton]

Hi All,

I have to do some VLOOKUP formulas and need to isolate words that I can match between two workbooks.

I am using the below formula to isolate the first word in the cell - example "Camelot Cement Wallpaper"
=LEFT(B2,FIND(" ",B2)-1)
I am left with "Camelot"

Which is working great, but I do have some instances where in the cell B2 there is only one word and so it returns a #VALUE! - is there an IF formula I can use to ignore cells that only have one word, or to keep that one word somehow?

Thanks

 

[mart37]

maybe you can use IFERROR()

 

[Fluff]

How about

=LEFT(B2,FIND(" ",B2&" ")-1)

 

[jtakw]

Hi,

Try these:

Book3.xlsx
	A	B	C
1	Keep Single word		Ignore Single word
2	Camelot	Camelot Cement Wallpaper	Camelot
3	Castle	Castle
4	Castle	Castle Carpet	Castle
Sheet718

Cell Formulas
Range	Formula
A2:A4	A2	=LEFT(B2,FIND(" ",B2&" ")-1)
C2:C4	C2	=IFERROR(LEFT(B2,FIND(" ",B2)-1),"")

Use A2 formula to Keep Single Word entries in B Column, Use C2 formula to Ignore.