This just came up (again, for the thousandth time maybe) and I came up with a different formula than I had in the past. This formula works correctly for the following name layouts...
First Last
First Middle Last
First M Last
First M. Last
First1 First2 Last
First1 First2 Last1 Last2
That next-to-last one being a name like "Mary Ann Jones" where "Mary Ann" is the full first name. The last one, on the other hand, covers a name like "Mary Ann Della Rossa" where "Mary Ann" is the full first name and "Della Rossa" is the full last name. I should note that the last one could be thought of as this also...
First Middle Last1 Last2
First M Last1 Last2
First M. Last1 Last2
which, of course, are also covered by the formula. Unfortunately, this formula will not correctly handle this arrangement...
First Last1 Last2
If you think about it, though, pretty much nothing ever could because there is no way to reliably distinguish between both of the following within the same data list...
First1 First2 Last
First Last1 Last2
as there would be no way to tell 100% of the time whether the middle "word" belongs to the First or the Last name. Anyway, a choice had to be made and since First1 First2 Last would be processed identically to First Middle Last, that was the one that was chosen.
Okay, enough of all that introduction stuff, here is the formula that will properly reverse (with an introduced comma separator) probably better than 99.99% of the names in any list you would ever have to process...
=MID(A2&", "&A2,FIND(" ",A2,FIND(" ",A2)+COUNTIF(A2,"* * *"))+1,LEN(A2)+1)
Bookmarks