Return leftmost word in cell

[ljtong]

Hello All,

I need to get the leftmost word in a cell.

I have used =LEFT(D34,FIND(" ",D34))

But if it is a 1 word cell (no spaces), I get #Value - I would like to return the single word

Is there any other method besides putting in ISERROR into all the formulas?

Thanks

 

[barry houdini]

Try

=LEFT(D34,FIND(" ",D34&" "))

 

[ljtong]

Nice!

 

[RomulusMilea]

Hello LJTong,

Or try this one: =IF(LEN(D34)-LEN(SUBSTITUTE(D34," ",""))=0,D34,LEFT(D34,FIND(" ",D34)-1)).

You have to make sure you have no trailing spaces. Cheers !

Romulus.

 

[dksaluki]

i think you're going to have to include an IF statement and a FIND statement in there. Use the FIND in conjunction with the IF to return the position of a blank space (""), then if there is one, use the LEFT function like you have, to get the all the characters to the left of the blank that it found.
If it did NOT find a blank space, just get the whole cell.

hope this helps

-dk

 

[dksaluki]

whoa, Barry Houdini's idea is way "awesomer"! nice

 

[Yogi Anand]

Hi ljtong:

I will take the nice formula from barry houdini and also use the TRIM function ...

=TRIM(LEFT(D34,FIND(" ",D34&" ")))

 

[ljtong]

Good point Yogi,

I think i can also do LEFT(D34,FIND(" ",D34&" ")-1)