Extract the first word from a cell?
 

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?

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

--
Regards
Frank Kabel
Frankfurt, Germany

Adam Cole wrote:
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?

One way:

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



In article ,
Adam Cole <Adam wrote:

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?

Make that:

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


In article ,
JE McGimpsey wrote:

One way:

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

JE McGimpsey wrote:
Make that:

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


In article ,
JE McGimpsey wrote:

One way:

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

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))

In article ,
"Frank Kabel" wrote:

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

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 :-)

--
Regards
Frank Kabel
Frankfurt, Germany

JE McGimpsey wrote:
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))

In article ,
"Frank Kabel" wrote:

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