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.

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in CLE, eDiscovery, Everybody, Lit Support, Practice Management and tagged , , . Bookmark the permalink.

5 Responses to Reverse Names Easily with Find & Replace

  1. Pingback: Excel Roundup 20150525 « Contextures Blog

  2. MF says:

    Simple but nice trick without formula! 🙂


  3. Patrick says:

    Good tips 🙂


  4. Gerald Strever says:

    Thank you for this post – very neat!


  5. Krasi says:

    You can mark the column and use: Data – Text to columns – Delimited – use comma field.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s