Alphabetically sorting a list of names in FirstName LastName format can be simplified by using the Text to Columns wizard in Excel. Unfortunately, you can run into issues when titles, middle names, middle initials, and name suffixes are used.
Thankfully, the Team at REsolved LLC sent in this great Excel tip on how to sort a list by an embedded last name:
Clients often ask us how to alphabetically sort data in Microsoft Excel by someone’s Last Name even if the name field is formatted as “Mr. John M. Doe”. Basically, all you need to do is make a copy of the column containing the name data and use Excel’s REPLACE function in tandem with some wildcards.
? = Find any type of character
* = Find everything (used in tandem with ‘find everything leading up to a particular value’)
We recorded a screencast to show clients how to do it and hope you find the video helpful.
The solution they propose is pretty creative and can be recorded into a macro quite easily. You can view the step-by-step ‘how-to’ video below, and be sure to visit REsolved LLC to learn more about the services they provide to non-profits and independent schools. Thanks for the tip!
The Closet Entrepreneur