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”?

reversenames1 

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:

 reversenames2

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 ‘,*’ 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 like so:

 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

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.

Advertisements

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:

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