Separating the full name into First and Last name (and middle maybe) - is this possible?

Originally Posted by mehmetcik

In cell A2 enter the name "Michael Jones"

In Cell B2 enter:-

Please Login or Register  to view this content.

In Cell C2 enter:_

Please Login or Register  to view this content.

WOWOWOWO !!!!

U R A GENIUS!!!

It works!

Pardon my audacity (I am grateful for your help), is there a way to not include the MIDDLE NAME in the last name result?

This
Dolores F. Defazio

Turned his LAST NAME into this:
F. Defazio

If you want First, Middle and Last names in separate columns use this

in B1
=LEFT(A1,FIND(" ",A1)-1)
in C1
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1)+1,1))
In D1
=IF(C1="",MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)+1),TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1),LEN(A1)-(FIND(" ",A1)))))

Your data is very incosistant...
donald boomer
roei n kind
kimber L. Ron
James A. Hayden III

It will be a lot simpler if you can get some uniformity in your data - all middle initials have (or dont have) a period. And how would you want to handle the "Heyden III"?

Originally Posted by FDibbins

Your data is very incosistant...
donald boomer
roei n kind
kimber L. Ron
James A. Hayden III

It will be a lot simpler if you can get some uniformity in your data - all middle initials have (or dont have) a period. And how would you want to handle the "Heyden III"?

I know I am new here, but in my eyes - you guys are magicians!!!
I am shocked by these formals.
Thank you so much.

GIS2013 - yours did not work for me ): Still came up with middle name.
Alkey - yours worked! But I still only want to seperate into two, not three (first + middle initial, last)
[ oh.... so i can just use the 3rd part of the formula....? ]
FDibbins - you are so right! (: Most of these follow the usual way of writing:

JANE X. SMITH

haha about the Hayden III


Again, thank you all so so much for the help!!!

If you are looking up the "splt" names into the combined names, this will work for you...
=iferror(VLOOKUP(A1&"*"&B1,'[full%20name(1).xlsx]Sheet1'!$A$1:$A$5,1,0),""

Here is corrected formula

First and Middle
B1

=IFERROR(LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1))),LEFT(A1,FIND(" ",A1)-1))

Last Name
C1

=TRIM(SUBSTITUTE(A1,IFERROR(LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1))),LEFT(A1,FIND(" ",A1)-1)),""))

You're Welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Try this to pull in the last nane only...
=MID(F1,SEARCH(" ",F1,LEN(LEFT(F1,SEARCH(" ",F1,1)-1))+LEN(F1)-LEN(SUBSTITUTE(F1," ","")))+1,99)
For teh 1st name a simple...
=LEFT(F1,SEARCH(" ",F1,1)-1)

"Now - What do you do if some filled WITH MIDDLE NAME and some DID NOT?"

I believe my formulas already addressed this issue.

Here is the spreadsheet with your examples.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

Hi

the following will handle up to 4 names.

In A1 enter first second third fourth

in B1 enter

Please Login or Register  to view this content.

in C1 enter

Please Login or Register  to view this content.

in D1 enter

Please Login or Register  to view this content.

and in E1 enter

Please Login or Register  to view this content.