Others

How to Fix Excel Sorting Errors Caused by Non-Breaking Spaces

Olivia Miller Sep 30, 2026 868 views

Question details

The user needs to fix an issue where Excel sorts text data incorrectly because the entries contain non-breaking spaces instead of standard spaces.

How to Fix Excel Sorting Text Incorrectly Due to Non-Breaking Spaces

Product: Microsoft Excel
Device & OS: not provided
Scenario: Sorting text data that contains imported or copied web content with irregular spacing.
Observed behavior: Excel treats non-breaking spaces (CHAR 160) differently from standard spaces (CHAR 32), resulting in unexpected or incorrect alphabetical sorting.

Before you start

Before attempting to clean the data, inspect a few affected cells to confirm that spacing is indeed the issue, and expand your column width to make hidden characters easier to spot.

Solution 1Recommended

Use Find and Replace to Remove Non-Breaking Spaces

This is the fastest method to clean up your dataset directly by replacing non-breaking spaces with standard spaces.

Non-breaking spaces often appear when copying text from websites. Since Excel recognizes them as character 160 rather than character 32 (a standard space), they must be swapped out before sorting.

Select the data range

Highlight the column or specific cells containing the text data that is sorting incorrectly.

Open the Find and Replace dialog

Press Ctrl + H on your keyboard to open the Find and Replace window.

Enter the non-breaking space character

Click inside the 'Find what' box. Hold down the Alt key and type 0160 on your numeric keypad to insert a non-breaking space.

Enter a standard space

Click inside the 'Replace with' box and press the Spacebar exactly once to insert a regular space.

Execute the replacement

Click the 'Replace All' button. Once finished, highlight your data range again and go to Data > Sort to organize it correctly.

Use Find and Replace to Remove Non-Breaking Spaces

Alternative Copy Method: If you cannot use the numeric keypad to type Alt+0160, you can double-click a cell containing the irregular space, manually highlight the space itself, copy it (Ctrl+C), and paste it (Ctrl+V) directly into the 'Find what' field.

Solution 2

Clean Up Data Using the SUBSTITUTE Function

Ideal for users who prefer keeping the original data untouched and creating a cleaned up version in an adjacent column.

Clean Data Easily

Sort and Clean Your Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data cleaning tools, full compatibility with Microsoft Excel formats, and intuitive sorting features to handle messy web data seamlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the unsorted data.
2. Access Find and Replace: Press Ctrl + H to bring up the Find and Replace dialog.
3. Replace the hidden characters: Input the non-breaking space into the 'Find what' box and a normal space into the 'Replace with' box, then click 'Replace All'.
4. Sort the data safely: Select the cleaned column, navigate to the Data tab on the top ribbon, and click 'Sort' to correctly alphabetize your list.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) files.Advanced Find and Replace features to quickly eliminate hidden web characters.Free, lightweight, and fast alternative for all your daily spreadsheet tasks.User-friendly interface that makes data cleaning and sorting highly intuitive.

microsoft office alternative - wps office

Frequently Asked Questions

Why does a non-breaking space affect how Excel sorts text?

Excel sorts data based on underlying character codes. A standard space uses character code 32, while a non-breaking space uses character code 160. Because they have different numerical values behind the scenes, Excel groups and sorts them differently, leading to unpredictable alphabetical arrangements.

Where do non-breaking spaces typically come from?

Non-breaking spaces commonly appear when you copy and paste text from websites, web-based CRMs, or PDF documents into your spreadsheet. Web browsers use the HTML entity   (which corresponds to CHAR 160) to prevent words from wrapping across lines, and this special character is carried over during the copy-paste process.

Is there a way to remove all hidden characters at once in Excel?

Yes, you can combine multiple text-cleaning functions. Using a formula like =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) will simultaneously replace non-breaking spaces, remove non-printing characters, and strip out any extra standard spaces at the beginning or end of your text.

Office Quick Solutions