Converting Names

[Bartlett]

I have a large spreadsheet with last names in the format in the same column

FName LName

would like to convert that to

LName, FName

all in one column

Example

Mike Trout becomes Trout, Mike

[Tony Valko]

Like this...

A1 = Mike Trout

This formula entered in B1:

=MID(A1,FIND(" ",A1)+1,20)&", "&LEFT(A1,FIND(" ",A1)-1)

However, are there any 3 word names?

That formula will work on a name like Eddie Van Halen but it won't work on a name like Betty Jo Spickerman.

[AlKey]

Or in case there is a middle name:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30))&", "&SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30)),"")

v	A	B
1	George Lucas	Lucas, George
2	Brad Pitt	Pitt, Brad
3	George Timothy Clooney	Clooney, George Timothy

[Tony Valko]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30))&", "&SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30)),"")

That handles 2 word first names but fails on 2 word last names.

If there are 2 word first and last names it is not possible to do this with 100% accuracy.