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
» This entry was filed under Excel and tagged with: Excel, Productivity, tutorial, video
34 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
Sam
What a time saver! Thanks!!!
Oct 2nd, 2008
TOMAS
@Sam – It definitely is a time saver; glad you could make use of the info.
Oct 12th, 2008
Andrew Johnston
Really neat.
Really going to help.
Thanks for sharing.
Nov 13th, 2008
Tammy
Thanks for the tip!
I realized a shorter way to do it though:
Edit > Replace > ?*[space]
That removes the need for the @ intermediate.
Mar 13th, 2009
TOMAS
@Andrew Johnston – No problem—glad the tip helped you out!
@Tammy – Hey that’s a great tip and saves a step, thanks for sharing!
🙂
Mar 13th, 2009
Arshdeep Singh
Awesome.
I had to play around your solution to suit my needs but it worked. Thanks a lot.
Mar 31st, 2009
TOMAS
@Arshdeep Singh – Glad you were able to make it work for you! 😉
Apr 1st, 2009
MARIANO
THA NKSSSSS ALOOTTT
Jun 10th, 2009
TOMAS
@MARIANO – You’re welcome, and thanks for taking the time to leave a comment! 😉
Jul 15th, 2009
Sohail Rizki
Thanks
that is real good tip and easy too.
Aug 20th, 2009
TOMAS
@Sohail Rizki – Glad to hear that it worked for you. 😉
Aug 28th, 2009
Folu
This came in handy today…thank you very much
Oct 19th, 2009
apachemono
Excellent…. Thanks so much…this really helped me alot
Oct 21st, 2009
Luis
AWSOME!, saved me some hours of work! Im always working with excel by doing RSVP’s . Thanks a million
Jan 26th, 2010
Carol A. LeBrun
Thank you so much – I was doing a similar function but copying column to word, converting, searching and replacing to get the final last name only. This will help immensely!
Feb 24th, 2010
Amna
This was very useful. I just used it for a bunch of lists!
Mar 2nd, 2010
Francesca
The video was great but it does not work for excel 2007. I think 2007 is too smart and does not recognize what to do with the replace with ‘blank’ Any tips how to do this with office 2007!!
Apr 28th, 2010
Alec Stanley
Thank you so much, you just saved me about 3 hrs of work. You are my hero.
Jun 10th, 2010
Nulubez
Very handy! Now what if you wanted to sort the name by last name, Title firstName middleInitial ?
Such as: Mr John Q. Smith Jr.
as: Smith Jr., Mr John Q
If someone could answer this question my life would be complete!
Oct 26th, 2010
Pat Brady
Fantastic tip! After several hours (!), I was about to give up sorting by last name. This worked! Thanks a million! –PB
Oct 29th, 2010
Kristen
I just had to leave a comment to say what a GREAT tutorial this is! Saved me TONS of time!
Jan 25th, 2011
Laura
Thank you so very much. I really appreciate being able to find actually helpful tools for using software on the web. You guys rock!
Feb 24th, 2011
Chris Bellevue
Thanks for the this simple and effective tutorial!
Mar 1st, 2011
hunter
AWESOME!!!!!!! Thanks so much ^,^
May 9th, 2011
Brandy
Amazing! Thank you so much!!
Oct 10th, 2011
James
Great work, the best way to do it without using ugly macros 🙂 Thanks
Mar 6th, 2012
Stephanie
Thanks for presenting this in such a simple format… I’m a visual learner and this was so easy to master. Made wrangling my wedding invite spreadsheet a lot easier… thanks for the help!
May 6th, 2012
Rina
Thank you so much, very helpful and innovative on your part.
Jun 22nd, 2012
Leave a Comment