Excel Tip – Formula to Convert Name Order in Single Cells

I haven’t posted anything new to the site in a while both because I’ve been busy with other things, and because the main articles I am working on are ones that will probably take me quite a while to research and write up to my satisfaction.  I have several in the works (including a few smaller ones I can turn to if necessary) so hopefully I’ll have at least one new wiki article up in August.

But in order to make sure the site gets at least a bit of new content each month, 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.

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, 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 of the month.  If you have any Excel tips of your own, feel free to add them in the comments.  Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *