Split full name w/single and multiple middle names

[MrManBoy]

Hi All,

Problem:

I have a data-set that has a list of full names. Splitting the first and last name is fine enough however, some of the names have multiple names between first and last name. Looking for a way (preferably w/a formula),that splits these multiple names as well splitting first and last name. Where possible, the split should occur in a single cell and not separated individually.

FYI - here is the formula I am using to split first and last name: =MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2))

Example:

Book1
	A	B
1	Original Data	Desired Data
2	Doe John	John Doe
3	Doe Jane	Jane Doe
4	Smith Aaron	Aaron Smith
5	Paul James Sam	Sam Paul James
6	Van Nie Sims Joe	Joe Van Nie Sims
Sheet1

Thanking you in advance.

 

[Peter_SSs]

Like this?

WIP.xlsm
	A	B
1	Original Data	Desired Data
2	Doe John	John Doe
3	Doe Jane	Jane Doe
4	Smith Aaron	Aaron Smith
5	Paul James Sam	Sam Paul James
6	Van Nie Sims Joe	Joe Van Nie Sims
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))&" "&A2,LEN(A2))

 

[MrManBoy]

Thank you so much Peter this is perfect!!

Cheers!

 

[Peter_SSs]

Thank you so much Peter this is perfect!!

Cheers!

You're welcome. Thanks for the confirmation.

 

[MrManBoy]

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))&" "&A2,LEN(A2))

Peter, would it be too much to ask if you could break down the formula in terms of what it's doing? Just within the substitute section, with the repeating of the 'blanks' etc. I just tried to work this out but not sure to be honest on whats happening. If not, no dramas at all. Cheers.

 

[Peter_SSs]

The SUBSTITUTE puts 50 spaces between each word.
Taking the RIGHT 50 characters will give some spaces followed by the last word.
TRIM removes the spaces leaving you with the last word.
The rest is similar to your original function.

 

[MrManBoy]

The SUBSTITUTE puts 50 spaces between each word.
Taking the RIGHT 50 characters will give some spaces followed by the last word.
TRIM removes the spaces leaving you with the last word.
The rest is similar to your original function.

Ahh ok, no worries thanks for sharing that Peter.

Cheers.