How to Fix Excel TRIM Not Removing Non-Breaking Spaces
Question details
The user needs to remove trailing spaces from their data, but the standard TRIM function is leaving non-breaking spaces behind.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning text data in a spreadsheet after importing or pasting it from an external source.
- Observed behavior
- The TRIM function successfully removes regular spaces but ignores non-breaking spaces (character code 160), leaving the data improperly formatted.
Identify the cell containing the text you want to clean, and ensure you have an empty adjacent column to input the cleaning formula before replacing the original data.
Use a Combined Formula to Remove All Extra Spaces
This is the most robust method to remove regular spaces, non-printable characters, and non-breaking spaces all at once.
Because the TRIM function only recognizes standard spaces (character code 32), it fails on non-breaking spaces (character code 160) often found in web-copied data. By nesting SUBSTITUTE, CLEAN, and TRIM, you can handle all unwanted spacing in a single step.
Click on an empty cell next to your data (for example, B2 if your messy data is in A2).
Type the formula =SUBSTITUTE(CLEAN(TRIM(A2)),CHAR(160),"") and press Enter.
Click the bottom-right corner of the cell containing the formula and drag the fill handle down to apply it to the rest of your dataset.
Copy the newly cleaned values, right-click the first cell of your original column, and select 'Paste as Values' to permanently replace the old data.
Remove Only Non-Breaking Spaces
Use this method if you want to keep regular spaces intact but specifically target and remove non-breaking spaces from your text.
Use WPS Spreadsheet to Clean Text Data
WPS Spreadsheet fully supports advanced text functions like SUBSTITUTE, CLEAN, and TRIM, allowing you to clean up messy data imported from the web or other software effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Input the formula: Select a blank cell next to your data and type =SUBSTITUTE(CLEAN(TRIM(A2)),CHAR(160),"").
- 3. Apply to data: Press Enter and drag the fill handle to apply the formula to the rest of your dataset.
- 4. Paste as values: Copy the cleaned data, right-click the original cells, and choose 'Paste Special' > 'Values'.
Frequently Asked Questions
Why does the TRIM function ignore some spaces?
The TRIM function is strictly designed to remove the standard space character (ASCII value 32). It does not recognize or remove non-breaking spaces (ASCII value 160), which are commonly generated in HTML web pages or imported data.
How do I know if my text contains non-breaking spaces?
If you use the TRIM function and still see trailing or leading spaces, or if testing the space with the formula =CODE(RIGHT(A2, 1)) returns 160, your text contains non-breaking spaces.
Can I use Find and Replace to remove non-breaking spaces?
Yes. You can open the Find and Replace dialog (Ctrl+H), click inside the 'Find what' field, hold the Alt key, and type 0160 on your numeric keypad. Leave the 'Replace with' field blank and click 'Replace All'.
What does the CLEAN function do in this formula?
The CLEAN function removes the first 32 non-printing characters in the 7-bit ASCII code (values 0 through 31) from text. It is often paired with TRIM to ensure imported data is completely free of hidden formatting characters before processing.
