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:

reversenames1 

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:

 reversenames2

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

reversenames3

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

reversenames4b

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

reversenames5

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

 reversenames6 

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

 reversenames7

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

 reversenames8

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:

PSV

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.

Advertisements

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! 🙂

    Like

  3. Patrick says:

    Good tips 🙂
    Patrick

    Like

  4. Gerald Strever says:

    Thank you for this post – very neat!

    Like

  5. Krasi says:

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

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s