Extract the first word from a cell?

[Guest]

I'm using Excel 2003 and need to return the first eight characters from a cell.

The "Left" function is fine for this but where the first word is less than
eight characters I only want to extract the first word and "left" includes
the start of the second word!

Can anyone help?

 

[Frank Kabel]

Hi
try
=LEFT(A1,FIND(" ",A1)-1)

 

[JE McGimpsey]

One way:

=LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1),8)))

 

[JE McGimpsey]

Make that:

=LEFT(A1,MIN(IF(ISNUMBER(FIND(" ",A1)),FIND(" ",A1)-1,8)))

 

[Frank Kabel]

Hi J.E. maybe with error checking:
=LEFT(A1,FIND(" ",A1&" ")-1)
or
=LEFT(A1,MAX(8,FIND(" ",A1&" ")-1))

 

[JE McGimpsey]

I like the approach, but neither of those work.

Both, if the first space in the cell is after character 9 (or there is
no space) return a too-long string.

You could fix the first with

=LEFT(A1,FIND(" ",LEFT(A1,8)&" ")-1)

The second one can be fixed by using MIN instead of MAX.

=LEFT(A1,MIN(8,FIND(" ",A1&" ")-1))

 

[Frank Kabel]

Hi J.E.
thanks for the correction. The first approach wans't meant to restrict the
output to only 8 characters but only to prevent an error if no space is
found.
the second one: Shame on me to mess up MIN and MAX