Hi All,
I have a long list of names in one column, some just first and last, some with middle initial and some with middle name.
What I need to do is just end up with a list in 2 cols, Firstname and Lastname
Any help is appreciated.
Thanks
Hi All,
I have a long list of names in one column, some just first and last, some with middle initial and some with middle name.
What I need to do is just end up with a list in 2 cols, Firstname and Lastname
Any help is appreciated.
Thanks
Last edited by fastcar; 11-16-2018 at 10:20 AM.
so you have like John Q Public in cell A2 and want John in B2 and Public in C2.
try these
=LEFT(A2,FIND(" ",A2)-1) in B2 and
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))) in C2
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Enter formula in cell B2 and drag formula across to cell D2 and down
Formula:Please Login or Register to view this content.
v A B C D 1 Names First Name Middle Name Last Name 2 Justin Wolf Justin Wolf 3 Allan J Green Allan J Green 4 Jerry Grant Jerry Grant 5 Steven B Paul Steven B Paul 6 Henry Ward Beecher Henry Ward Beecher
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
UDF (user defined function) will do?
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
you marked it as solved, so what did you use?
In C2 then copied down
=LEFT(B2,FIND(" ",B2)-1)
In D2 then copied down
=RIGHT(B2,LEN(B2)-AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(B2)))/(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=" "),1))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks