Tech: MS Excel trick - How to transpose "FirstName LastName" to "LastName, FirstName"

For me, Excel is becoming a "data fixup" glue of sorts that I used to use simple Unix commands for.  The chief reason for this is that many of the tools producing the data have export functions to native Excel file formats, so it is easy enough to put output of disparate tools in to multiple tabs of the same workbook and fix them up locally.  But this also presents certain challenges working within the confines of Excel formula's, which like Perl can be very hard to understand what is happening in a complex formula if each part is not well documented.

Thus, the reason behind this post.  While trying to determine if all of the employees of interest from one sheet had completed each of 5 mandatory training modules tracked in another, the first thing I had to do was transpose how the name was represented from one to the other so that I could do a match.

In this instance I want to take "Jane Doe" to "Doe, Jane" .

First, to get the last name we use the RIGHT function assuming the first name is in Column A Row 2:

=RIGHT(A2,LEN(A2)-FIND(" ", A2,1))

Concatenating a trailing comma is as simple as using "&"

=RIGHT(A2,LEN(A2)-FIND(" ", A2,1)) & ", "

Now we use the LEFT function to get the first name:

=LEFT(A2,FIND(" ",A2,1)-1)

Put it all together and we have the following:

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

All this is really doing is extracting out the substring on either side of the whitespace character and allowing you to manipulate that text independently of the rest.  Obviously if there is another separator, you would specify that instead to do the same thing.