Sort a List by Last Name When First Name is Used
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!
Sincerely,
The Closet Entrepreneur
6 Comments
spongeblog
That’s a great read, I’m sure it will come in handy as I use excel a lot too and this is one thing I don’t know how to do before. Thanks.
Dec 26th, 2007
TOMAS
@spongeblog - It’s definitely a good tip, and even more impressive is the fact that this was one of their initial attempts at a screencast - they made it look easy.
Dec 27th, 2007
Daniel
Very creative tip! I never think such way before. This tip will be very usefull for me as I work with Excell everyday. Thanks o lot!
Jan 21st, 2008
TOMAS
Glad you could make use of the info Daniel!
Jan 21st, 2008
Sojourner
Very good CE! Thank you very much. I found you because of your article on San Manuel but now you are going in my favorites list.
Feb 18th, 2008
TOMAS
Thanks Sojourner! I’ve been thinking of revamping the Excel tutorials so be on the lookout for more to come!
Feb 19th, 2008
Leave a Comment