Sort a List by Last Name When First Name is Used

Excel Sort by Last Name

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!

[Subscribers, please visit the site to view the video]

The Closet Entrepreneur

» This entry was filed under Excel and tagged with: , , ,


  1. 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.

  2. 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.


  3. 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!

  4. TOMAS

    Glad you could make use of the info Daniel!

  5. 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. 🙂

  6. TOMAS

    Thanks Sojourner! I’ve been thinking of revamping the Excel tutorials so be on the lookout for more to come!

  7. Sam

    What a time saver! Thanks!!!

  8. TOMAS

    @Sam – It definitely is a time saver; glad you could make use of the info.

  9. Andrew Johnston

    Really neat.
    Really going to help.
    Thanks for sharing.

  10. Tammy

    Thanks for the tip!
    I realized a shorter way to do it though:
    Edit > Replace > ?*[space]
    That removes the need for the @ intermediate.

  11. @Andrew Johnston – No problem—glad the tip helped you out!

    @Tammy – Hey that’s a great tip and saves a step, thanks for sharing!


  12. Arshdeep Singh

    I had to play around your solution to suit my needs but it worked. Thanks a lot.

  13. @Arshdeep Singh – Glad you were able to make it work for you! 😉



  15. @MARIANO – You’re welcome, and thanks for taking the time to leave a comment! 😉

  16. Sohail Rizki

    that is real good tip and easy too.

  17. @Sohail Rizki – Glad to hear that it worked for you. 😉

  18. Folu

    This came in handy today…thank you very much

  19. apachemono

    Excellent…. Thanks so much…this really helped me alot

  20. Luis

    AWSOME!, saved me some hours of work! Im always working with excel by doing RSVP’s . Thanks a million

  21. 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!

  22. Amna

    This was very useful. I just used it for a bunch of lists!

  23. 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!!

  24. Alec Stanley

    Thank you so much, you just saved me about 3 hrs of work. You are my hero.

  25. 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!

  26. Pat Brady

    Fantastic tip! After several hours (!), I was about to give up sorting by last name. This worked! Thanks a million! –PB

  27. I just had to leave a comment to say what a GREAT tutorial this is! Saved me TONS of time!

  28. 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!

  29. Chris Bellevue

    Thanks for the this simple and effective tutorial!

  30. hunter

    AWESOME!!!!!!! Thanks so much ^,^

  31. Brandy

    Amazing! Thank you so much!!

  32. James

    Great work, the best way to do it without using ugly macros 🙂 Thanks

  33. 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!

  34. Rina

    Thank you so much, very helpful and innovative on your part.

Leave a Comment

Please type the answer to the math equation. Click the pic to hear the numbers.
Click to hear an audio file of the anti-spam equation