Reverse Names with Find & Replace

In a recent post I explained how to count the number of pages in a list of Bates ranges.  Today I’ll discuss a common text formatting issue.

Suppose you have a long list of names of the form “Cook, Mary” and you want to put them in the form “Mary Cook”?


You can use wildcards within Excel’s Find & Replace feature to quickly isolate the first names and last names, and then recombine them.

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


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 ‘,*’ with nothing.


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


Now you can combine the first and last names like so:


 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:


In a future post I’ll discuss how to achieve the same result – and tackle some more complicated problems – using text functions such as RIGHT, LEFT, and MID.  Stay tuned!

Do you need advice about a text formatting or other Excel issue?  Email me or leave a comment and you may read the solution on a future blog post.

Excel Esquire.  Good answer.

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

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