Excel Tip: Format Names from FName LName to LName, FName

Do you work a lot in Excel. Do have to manipulate a lot of text strings or values. This tip might make life a little easier. This will help you convert a list of values from
<FirstName LastName> to <LastName, FirstName>

The problem: A long list of names are available as First Name Last Name.

But you need to display this as Last Name, First Name.

Do note that the names may not always be 2 words, they may contain 3,4 or more words. (Eg: Vijay Dinanath Chauhan). The assumption here is the Last word (Chauhan, in this case) is always the Last Name (or Surname).

Formula in cell B2:

 

=RIGHT(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))),LEN(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-FIND(“~”,SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””)))))&”, “&LEFT(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))),FIND(“~”,SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-1)

Drag the formula till the bottom of the list and you have an easy way to reformat your list of names.

Voila! Works on Names with 2 words or even more than 2.