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.