I have names in one column. i would like to split last names to another column.
Some names have 2 first names.
exp: mary anne smith, susan smith, etc...
just last names to the another column.

Please suggest!

Assuming your list starts in a1 put this in B1 and drag down

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,LEN(A1))

Mike

"mtin" wrote:

I have names in one column. i would like to split last names to another column.
Some names have 2 first names.
exp: mary anne smith, susan smith, etc...
just last names to the another column.

Please suggest!

I'm sure someone has a prettier way, but...
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),MID(A1,FIND(" ",A1),LEN(A1)-FIND("
",A1)+1),MID(A1,FIND(" ",A1,FIND(" ",A1)+1),LEN(A1)-FIND(" ",A1,FIND("
",A1))))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"mtin" wrote:

I have names in one column. i would like to split last names to another column.
Some names have 2 first names.
exp: mary anne smith, susan smith, etc...
just last names to the another column.

Please suggest!

On Fri, 7 Nov 2008 06:21:01 -0800, mtin wrote:

I have names in one column. i would like to split last names to another column.
Some names have 2 first names.
exp: mary anne smith, susan smith, etc...
just last names to the another column.

Please suggest!

To extract the last name:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

To remove the last name from the original string, assuming the last name is in
B1, and the full name is in A1:

=SUBSTITUTE(A1,B1,"",(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1))

--ron