Published: 27 July 2015
I want to share a quick software tip in the form of a couple of Excel formula's that I wrote.
These formulas convert single cells with names in [Last Name], [First Name] order to [First Name] [Last Name] order. The first version leaves in middle initials and suffixes. The second version trims everything after the first name. You can also use portions of these functions to pull out portions of names into separate cells.
In order to help you understand how they work; this first formula looks for the comma, extracts all of name after comma, extracts all of name up to comma, and then flips them. It leaves in stuff like middle initials. Make sure to change B1
in the formulas below to be whatever cell the name in the last, first format is located in on your spreadsheet.
=(TRIM(MID(B1, SEARCH(",",B1,1)+1,LEN(B1))))&" "&(TRIM(MID(B1, 1, SEARCH(",", B1, 1)-1)))
This version does the same thing, but it looks for the first space after the first name and ignores anything after, which has the effect of removing middle names.
=(TRIM(MID(B1,SEARCH(",",B1,1)+1,(SEARCH(" ",B1,(SEARCH(",",B1,1)+2))-(SEARCH(",",B1,1))))))&" "&(TRIM(MID(B1,1,SEARCH(",",B1,1)-1)))
I have found these are not perfect and will occasionally miss things. For example, if someone has 2 first names the second formula will only capture one of the two first names. But so far they have worked for me about 95-98% of the time and have proved useful to myself and a few co-workers when working with long lists of names in Excel.
Anyway, that's the quick tip. Thanks!