Reverse Names Easily with Find & Replace

A student in one of my CLE programs recently asked me the following question:  What do you do when you have a long list of names of the form “Cook, Mary” and you want to put them in the form “Mary Cook”?  This issue comes up so often I decided to write a post about it.

Suppose you have all those pesky names in a single column:

 

Although there are text functions that will extract the various parts of the name field, a much more straightforward way is to use wildcards within Excel’s Find & Replace feature to quickly isolate the first names and last names, and then recombine them.  Here’s how it works:

Step 1 is to copy the entire column of names into 2 helper columns:

 

Next, select Column B and hit Ctrl+H to launch the Find & Replace dialog box.  Tell Excel to replace ‘*, ’ with nothing:

This deletes everything up to and including the comma and the space after it, leaving just the first name:

Next, select Column C and hit Ctrl+H again.  This time replace  ‘, *’ (that’s comma space asterisk) with nothing.

This will delete everything from the comma onwards, isolating the last names:

  

Now you can combine the first and last names with this simple formula:

 

Copy the formula all the way down to recombine the rest of the names:

 

There you have it!  You’ll probably want to replace all these formulas with the resulting text values.  Do this by Copying Column D and then pasting back on top of it with the Paste-Special-Values button:

Thanks for reading.  If you have a thorny Excel formatting issue, please share it in the comments below.  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire.