Generally, when someone asks how to sort by last name in a cell that contains both first and last name, I advise people to use the Text to Columns feature, but I’ve always wondered if there was another option. Today I found this video about how using the replace feature can isolate the last name in a separate column, making it easy to sort.
Before you watch the video, here are a couple of things to note:
1. This video is in a pre-2007 version of Excel. The Sort and Replace tools in 2007 and 2010 are on the right side of the Home tab on the ribbon. Everything else works the same.
2. This method assumes that you do not have any Jr’s or III’s or PhD’s tacked on to the end of your names. If you might, you should scan the Sort Name column afterward to check for these and correct them as needed, or do a Replace for the common ones and replace them with nothing.
Here’s a link to the post and video: