How to Sort an Excel Table by Last Name Without Changing Names
Question details
The user needs to sort a list of full names alphabetically by surname without permanently altering the original full name formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a contact list or employee roster where first and last names are combined within a single column cell.
- Observed behavior
- By default, Excel sorts alphabetically from the beginning of the text string, causing the table to be sorted by the first name instead of the last name.
Review your list to ensure names follow a generally consistent format and remove any accidental trailing spaces that could interfere with text extraction and sorting.
Use Text to Columns and a Helper Column
Split the full names into separate columns temporarily so you can sort by the generated last name column.
This method involves using a helper column to house the split data. It is the most visual and accessible method for users who do not want to use complex formulas or coding.
Right-click the column header next to your name list and select 'Insert' to create blank columns where the split names will go.
Highlight the column containing the full names. Go to the 'Data' tab on the ribbon and click 'Text to Columns'.
Select 'Delimited' and click Next. Check the box for 'Space' to split the text at each space between the first and last names, then click Finish.
Select your entire table, go to the 'Data' tab, click 'Sort', and choose to sort by the column that now contains the last names. After sorting, you can delete or hide the helper columns to keep the original formatting.
Use a Formula to Extract the Last Name
Create a formula that dynamically identifies the last space in a cell and extracts the surname into an adjacent column for sorting.
Run a VBA Macro
Automate the text separation and sorting process with a custom macro.
Sort and Manage Contact Lists Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data management tools, including an intuitive Text to Columns wizard and advanced formula capabilities, allowing you to manipulate and sort complex lists effortlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the document containing your list of names.
- 2. Insert a helper column: Right-click the column adjacent to your names and choose Insert to create a temporary workspace.
- 3. Split the text: Select the name column, navigate to the Data tab, and click 'Text to Columns'. Choose Space as the delimiter to separate first and last names.
- 4. Sort the data: Highlight the whole dataset, click Sort under the Data tab, and select your new last-name column to organize everything alphabetically.
Frequently Asked Questions
How do I handle prefixes or suffixes like 'Jr.' or 'Dr.' when sorting?
Prefixes and suffixes complicate automatic sorting because they alter the position of the last name. You will need to manually remove these titles, or use advanced nested formulas/Find and Replace to strip them out before splitting the text by spaces.
Can I merge the first and last names back together after splitting them?
Yes, you can easily combine them using the CONCATENATE function or the ampersand (&) operator. For example, using the formula =A2 & " " & B2 will merge the contents of cell A2 and B2 with a space in between.
Why is my column header being sorted into the middle of my data?
When using the Sort function, ensure you check the box that says 'My data has headers' in the Custom Sort dialog box. This tells the spreadsheet to lock the first row in place and exclude it from the alphabetical sorting process.
Does sorting by a helper column mess up the rest of my row data?
Not if you select the entire table before sorting. Always ensure you highlight all columns associated with your rows, or click 'Expand the selection' when prompted by the sort warning, to keep your row data locked together.
