Fix Excel Secondary Sort Not Working Due to Trailing Spaces
Question details
The user is experiencing issues with secondary sorting because hidden trailing spaces or imported characters make identical-looking text values behave differently.

- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Sorting multiple columns after importing data from file listings or splitting text using Text to Columns.
- Observed behavior
- The secondary sort fails to order the rows correctly because the spreadsheet program interprets values with trailing spaces as distinct from those without, causing incorrect data grouping.
Before proceeding, select your entire data set and visually inspect for inconsistent spacing, and ensure you are selecting all relevant columns before sorting so row data stays intact.
Clean Trailing Spaces Using the TRIM Function
Use the built-in TRIM function to quickly remove extra leading and trailing spaces from your text before applying multiple sort levels.
The most common reason for sorting failures on imported text data is invisible trailing spaces. The TRIM function is designed to strip all extra spaces from text, leaving only single spaces between words.
Right-click the column header next to the data you want to clean and select 'Insert' to add a new blank column.
In the first cell of the new column, type `=TRIM(A2)` (assuming A2 contains your uncleaned text) and press Enter.
Click the cell with the formula and drag the fill handle (the small square at the bottom right) down to apply it to the entire column.
Copy the newly populated column, right-click the original uncleaned column, and choose 'Paste Values' (the clipboard icon with a 123) to overwrite the messy text.
Select your entire dataset, go to the Data tab, click 'Sort', and set up your primary and secondary sort levels. The data will now group accurately.

Use Find and Replace for Nonbreaking Spaces
If the TRIM function does not resolve the issue, your imported data likely contains nonbreaking spaces which must be removed using Find and Replace.
Clean and Sort Your Data Flawlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful, user-friendly data cleaning tools and advanced multi-level sorting capabilities, making it easy to handle messy imports and organize your files.
- 1. Open your spreadsheet: Launch WPS Office and open the file containing the messy data.
- 2. Clean the data: Use the `=TRIM()` function in an adjacent column to automatically strip all trailing spaces from your imported text.
- 3. Highlight your dataset: Select all the columns and rows you wish to sort to ensure your row data stays synchronized.
- 4. Apply multi-level sort: Navigate to the Data tab, click 'Sort', add your Primary and Secondary criteria, and click OK to see your data perfectly ordered.

Frequently Asked Questions
Why does the spreadsheet treat trailing spaces as different values?
Spreadsheet software reads every individual character in a cell, including invisible spaces. A cell containing "Apple " is treated as an entirely different text string than "Apple", which disrupts alphabetical grouping and logical sorting operations.
Can I remove all trailing spaces without using a formula?
Yes, you can use the Find and Replace feature. Press Ctrl + H, type a single space in the 'Find what' box, leave 'Replace with' completely blank, and click 'Replace All'. However, be cautious as this will also remove the necessary spaces between words in sentences.
Does the Text to Columns feature automatically remove trailing spaces?
Not automatically. However, when using the delimited option in the Text to Columns wizard, you can check the 'Treat consecutive delimiters as one' option, which can help mitigate extra spaces during the data splitting process.
Why didn't the TRIM function remove the spaces from my imported web data?
Web data often utilizes nonbreaking spaces (known as HTML or character code 160), which the standard TRIM function ignores. To remove them, you must use the CLEAN function, the SUBSTITUTE function like `=SUBSTITUTE(A2, CHAR(160), "")`, or the Find and Replace tool.




