View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Separating Strings

I want it to return everything BEFORE the space to
one column and then I need another function to return
everything AFTER the space in another column.


Assuming all your names are in this format:

A1 = Jim Smith

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

=MID(A1,FIND(" ",A1)+1,100) = Smith

This does not account for names like:

Jim Bob Smith
Mr. Jim Smith
J. B. Smith


--
Biff
Microsoft Excel MVP


"CJ" wrote in message
...
I'm sure this will come as a really simple question to the rest of you,
but I
have a column with names in it; I want to divide them into first and last
names. How can I do this?

I started with finding the space in between the first and last names, like
this:

SEARCH(" ",A2)

Then when I got the number of the space, I started a conditional
statement:

IF(SEARCH(" ",A2)0, . . . .)

But that's where I lose my line of thinking.So I'm telling it that if it
finds a space, then I want it to return everything BEFORE the space to one
column and then I need another function to return everything AFTER the
space
in another column.

TIA for your help!