I have about 5,000 Full Names in one column. I would like to separate these into first, middle and last name. Currently the names are like John Doe, David P. Smith, Mary Sue Johnson. I have tried successfully to extract the first name using:
=LEFT(C6,FIND(" ",C6)-1)
Using another function
=MID(C6,FIND(" ",C6)+1,LEN(C6)+1-FIND(" ",C6))
I get the Middle and last names. However, when I try to separate the middle and last names further, the functions I have tried will return #VALUE if there is no second name. I guess I am out of ideas.
=LEFT(C6,FIND(" ",C6)-1)
Using another function
=MID(C6,FIND(" ",C6)+1,LEN(C6)+1-FIND(" ",C6))
I get the Middle and last names. However, when I try to separate the middle and last names further, the functions I have tried will return #VALUE if there is no second name. I guess I am out of ideas.