How do i split 1st name & 2nd name into two columns?

Posted by Andy on September 29, 2001 2:38 AM

A little similar to a previous post by Jim....
I have a cell with "Joe Smith" in it. I need to split this into two cells, one for the last name, the other for the remaining text. I say it like that because some cells i have could look like this "Mr.& Mrs. Smith" or even "Joe & Betty Smith" I need to end up with one cell for the last name....
Thanks for taking the time to read this...

Posted by Aladin Akyurek on September 29, 2001 3:16 AM

Andy --

Assuming that you have the first full name in A1,

in B1 enter: =SUBSTITUTE(A1," "&C1,"")
in C1 enter: =RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Select B1 and C1 then copy down as far as needed.

Aladin

Posted by Andy on September 29, 2001 4:22 AM

Re: How do i split column... works a treat !

Many Thanks Aladin..

That's well smart!! Works just how i wanted it to! Thanks very much!

Cheers, Andy

Posted by Andy C on October 22, 2001 4:43 AM

This is a bit complex for the simple task requested. Use the Text-to-Columns wizard on the tools menu.