Excel formula to reverse first name last name order


This formula is one that everyone should have in his or her toolbox:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1)

When you have a name listed in cell A1 it will switch it to the Last name, First name format. So Ashton Eaton will become Eaton, Ashton.

So the way this formula works is as follows. This part:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

. . . finds text from the right in cell A1. The total length of the cell A1 is 12 characters (which we get by LEN(A1)) and then the formula subtracts from 12 the position of the first blank space in A1, which is at 7 [see FIND(" ",A1)]

So the RIGHT formula sees to take the 5 characters from the right of the cell.

&", "&

. . . adds in a comma and a space

LEFT(A1,FIND(" ",A1)-1)

The purpose of this part of the formula is to find the number of characters from the left of cell A1. The FIND formula again shows the position of the first blank space in the cell, which is subtracted by one to get the number of characters of the first name.