Looking for a text function

[Tom James]

Does anyone know a function that will return the first word in a text
string? I know that the Left function can do it, but I want to be able to
copy the function down a column so that no matter what length the first word
is, it will extract it without having to rewrite the function. For example,
if I had a list of first and last names, I would want to be able to extract
the first name in one column and extract the last name in another column
(without having to write VBA code).

 

[Ragdyer]

You could try using TTC (Text To Columns).

Select your column of names, and then:
<Data> <TextToColumns> <Delimited> <Next>

Click on "Space", then <Next>

Now, you can either replace the original column of data with 2 columns of
the "split" names by hitting <Finish>,
OR
You can preserve the original column and have your "split" data display in 2
other columns by replacing the address in the "Destination" box, from the
default location of the original data, to the empty left column of a
neighboring empty pair, and then hit <Finish>.

On the other hand, if you would like to use formulas, with your names in
Column A,
you could try this in B1 to extract the first name:

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

And this in C1 to extract the rest:

=RIGHT(A1,LEN(A1)-LEN(B1)-1)

And copy down both formulas as needed.

 

[Ken Johnson]

Does anyone know a function that will return the first word in a text
string? I know that the Left function can do it, but I want to be able to
copy the function down a column so that no matter what length the first word
is, it will extract it without having to rewrite the function. For example,
if I had a list of first and last names, I would want to be able to extract
the first name in one column and extract the last name in another column
(without having to write VBA code).

Hi Tom,

=IF(ISERROR(FIND(" ",A1)),IF(ISBLANK(A1),"",A1),LEFT(A1,FIND("
",A1)-1))

seems to work for the text in A1.

Ken Johnson

 

[Barbara Wiseman]

Tom,
=LEFT(A1,FIND(" ",A1)-1)
Will work, I think

and
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
to get anything after the first space

Barbara.

 

[Tom James]

Thanks to RD, Ken, and Barbara for the great examples!

 

[Naraine]

try using the mid function combined with the find function.