splitting name

[mtin]

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!

 

[Mike H]

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

 

[John Bundy]

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

 

[Ron Rosenfeld]

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