Obtain First, Middle and Surname from multiple names

[jturner12]

Hi,

I have a list of names which need to be split in to 3 fields: First Name, Middle Name and Surname. This would be simple if all names were limited to 3 names, however, the names in the list can sometimes be 3, sometimes 4,5,6 or sometimes only 2: First and Last. Below is a sample of names:

Please Login or Register  to view this content.

In theory, what I need in fname field is to get the text up to first space and this works fine using: =LEFT(A2,FIND(" ",A2,1)-1)
For Surname: It should be everything after the last space
Middle name: Should be everything else or blank if there's only 2 names.

Please help..

[dredwolf]

Welcome to the forum jturner12
Assuming your data is is in column A and you want the results to be in Columns B(first name), C(middle names, if any),D(last name):
in B2:

Please Login or Register  to view this content.

Drag down as far as needed

in C2:

Please Login or Register  to view this content.

Drag down as far as needed

in D2:

Please Login or Register  to view this content.

Drag down as far as needed

Hope this helps

EDIT

You need all 3 formulas in place before the second one works, it relies upon the 3rd one to operate correctly

[dredwolf]

Self deleted, duplicate post...again

[jturner12]

perfect! Thank you so much

[dredwolf]

You are welcome !

Please mark thread as closed (see signature below for details) if you are satisfied with answer