Excel - Splitting 1 cell into 2 1

[mman74]

I have an Excel file with a list of fullnames, and telephone numbers. It is a big list. For example;
"Michael Man" in one cell.
Can I split this up into 2 cells, a first name and a surname? Because people can have middle names, I only want it to transfer the last word into a separate cell.
Can anybody tell me how? Thanks!

 

[ETID]

highlite the column in question then goto the menu...Data>text to columns...then tell the wizard to delimit by space.


You'll end up with 3 cols if there are middle names more if you have names like "John Doe De Lorenzo"

 

[mman74]

Thanks! That worked exactly as you said!

 

[KenWright]

You said that you only wanted the last names as opposed to any middle names, so you could just use a formula:-

With your data in Col A starting A1, put the following in B1 and copy down.

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","&quot))))

Then just copy Col B and paste special as values.

Regards
Ken................