You have text in Excel that looks like it has extra spaces, but the TRIM function does not clean it. This happens because the spaces are not standard space characters. They are non-breaking spaces, often copied from web pages or other applications. This article explains what these characters are and provides several methods to remove them from your cells.
Key Takeaways: Removing Non-Breaking Spaces
- SUBSTITUTE function with CHAR(160): Replaces the non-breaking space character with a standard space or nothing.
- Find and Replace (Ctrl+H): Lets you manually paste the non-breaking space character into the Find field for a quick cleanup.
- Power Query Editor: Transforms an entire column of data by replacing non-breaking spaces in a repeatable process.
Why the TRIM Function Fails on Non-Breaking Spaces
The Excel TRIM function is designed to remove only the standard space character, which is represented by the ASCII code 32. A non-breaking space is a different Unicode character, often with a code of 160. It is commonly used in HTML to prevent line breaks between words. When you copy data from a webpage, email, or PDF into Excel, these characters often come along. TRIM does not recognize them as spaces, so it leaves them in the text. This causes issues with formulas, lookups, and data sorting because the cell content is not visually uniform.
Methods to Delete Non-Breaking Spaces
You can use Excel formulas, the Find and Replace tool, or Power Query to clean these characters. The best method depends on whether you need a one-time fix or a reusable solution for imported data.
Using the SUBSTITUTE Function
The SUBSTITUTE function can target the specific character code for a non-breaking space. Use CHAR(160) to generate it within the formula.
- Identify the cell with the problem
Select a cell that contains the stubborn spaces. For example, cell A1. - Enter the SUBSTITUTE formula
In a new cell, type the formula: =SUBSTITUTE(A1, CHAR(160), ” “). This replaces each non-breaking space with a standard space. - Remove all spaces completely
To delete the characters entirely, use: =SUBSTITUTE(A1, CHAR(160), “”). This leaves no spaces between words. - Apply TRIM for final cleanup
Combine the functions to remove non-breaking spaces and then trim standard extra spaces: =TRIM(SUBSTITUTE(A1, CHAR(160), ” “)). - Copy the results as values
Select the cells with the new formulas, copy them, and use Home > Paste > Paste Values to replace the original text.
Using Find and Replace (Ctrl+H)
This is a fast method for cleaning data in a selected range without formulas.
- Select your data range
Highlight the cells, columns, or entire sheet you want to clean. - Open the Find and Replace dialog
Press Ctrl+H on your keyboard. - Enter the non-breaking space in the Find field
Click in the “Find what” box. Hold the Alt key and type 0160 on the numeric keypad, then release Alt. A small dot may appear. - Set the Replace field
Leave the “Replace with” box empty to delete the character, or type a standard space. - Execute the replace
Click “Replace All”. Excel will report how many replacements were made.
Using Power Query
Power Query is ideal for cleaning data that you import regularly, like from a web source.
- Load your data into Power Query
Select your data range and go to Data > From Table/Range. Click OK if prompted to create a table. - Select the column to transform
In the Power Query Editor, click the header of the column containing the text. - Open the Replace Values dialog
Go to the Transform tab and click “Replace Values”. - Enter the values to find and replace
In the “Value To Find” box, type #(160) including the parentheses. In the “Replace With” box, type a space or leave it blank. - Apply the changes and load data
Click OK. Then click Home > Close & Load to send the cleaned data back to a new worksheet.
Common Mistakes and Data Cleaning Issues
Formula Returns #VALUE! Error
The CHAR function may return a #VALUE! error if used in a non-English locale where the character code is different. Use the UNICHAR(160) function instead for better compatibility across language versions of Excel.
Find and Replace Does Not Find the Character
If typing Alt+0160 does not work, copy an actual non-breaking space from a cell. Double-click a cell, highlight the invisible character, and press Ctrl+C. Then paste it into the “Find what” field using Ctrl+V.
Spaces Remain After Using SUBSTITUTE
Your data might contain multiple types of non-printing characters. Use the CLEAN function in combination with your formula: =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), ” “))) to remove other line breaks and control characters.
Comparison of Non-Breaking Space Removal Methods
| Item | SUBSTITUTE Formula | Find and Replace | Power Query |
|---|---|---|---|
| Best for | One-time cleanup within a worksheet | Quick manual fix on selected data | Automated cleaning of recurring data imports |
| Learning curve | Low, uses basic Excel functions | Very low, uses common dialog | Medium, requires learning Power Query interface |
| Reusability | Formula must be copied to new data | Action is not saved, must be repeated | Steps are saved in a query and refresh automatically |
| Handles large datasets | Can slow down with thousands of formulas | Fast processing on any range size | Optimized for large data transformation |
| Removes other hidden characters | No, requires adding CLEAN function | No, only targets the pasted character | Yes, has additional transformation options |
You can now clean text data that contains non-breaking spaces. Use Find and Replace for a fast manual fix on a single sheet. Apply the SUBSTITUTE and TRIM formula combination for a more controlled cleanup within your workbook. For advanced, repeatable data cleaning, explore the Replace Values feature in Power Query. Remember that the CHAR(160) code works in most Western language versions, but UNICHAR(160) is the more modern and reliable function to use in newer versions of Excel.