Have you ever needed to sort a list by last name, but the names are in a Word file or other text-based document?  If so, then you know how time consuming it can be to manually separate them into First Name and Last Name.  Well, Excel to the rescue!

Here’s how you can use Excel to separate and sort the names.

  1. Select the list of names from the “text-based” document and copy them.
  2. Open a blank workbook in Excel. Place your cursor in cell A1 and paste the list you copied. Excel puts each name on a new row, but to sort by last name, the first name and the last name must be in separate columns.

    Pasted list from text-based document into Excel column A
    Pasted list from text-based document into Excel column A
  3. To separate them, select the column with the names and then on the Data tab, in the Data Tools group, select Text to Columns

    From Data Tab select Text to Columns
    From Data Tab select Text to Columns
  4. In the Convert Text to Columns wizard, use the Delimited option suggested by the wizard, and then select Next.

    Select Delmited option
    Select Delmited option
  5. Uncheck the suggested selection, Tab, made by the wizard, and check the Space option.  Then select Next.

    Uncheck Tab and select Space
    Uncheck Tab and select Space
  6. Use the suggestions made by the wizard, and select Finish

    Select Finish to complete
    Select Finish to complete
  7. The first names remain in column A while the last names are now in column B
  8. Select both the columns, and then on the Data tab, in the Sort & Filter group, select Sort.

    Names are now in two columns
    Names are now in two columns
  9. In the Sort dialog box, from the Sort by drop down list, select Column B, and then select OK.

    Sort by column B to sort by last name
    Sort by column B to sort by last name
  10. The list is now sorted by last name alphabetically, starting with A.

Thanks for reading, and I hope you find this useful.

Posted in: