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.
Excel Esquire 