How to Remove Non-Breaking Spaces from Numbers in Excel
Question details
The user needs to remove invisible non-breaking spaces from numbers copied from external sources like Outlook so they can be accurately recognized as numeric values in Excel.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up imported data copied from Outlook or web sources that contains non-breaking spaces instead of standard spaces.
- Observed behavior
- Standard TRIM functions fail to remove the trailing spaces, causing the numbers to be treated as text strings, which leads to calculation errors.
Identify the columns containing the problematic data and ensure you have an empty adjacent column available if you plan to use a formula-based solution.
Use the SUBSTITUTE and CHAR Functions
Replace the non-breaking space character code with an empty string and mathematically convert the result back to a number.
The standard TRIM function only removes standard spaces (character code 32). Non-breaking spaces use character code 160. To properly clean the data, you must isolate this specific character code using the SUBSTITUTE function.
Click on an empty cell next to your problematic number. For example, if your number is in cell C7, select D7.
Type the formula =SUBSTITUTE(C7,CHAR(160),"")*1 into the formula bar. The CHAR(160) component identifies the non-breaking space, while multiplying by 1 forces Excel to store the cleaned result as a numeric value.
Press Enter to execute the formula. Click the cell again and drag the fill handle down to apply this formatting to the rest of the column.
Use the Find and Replace Feature
Manually copy the non-breaking space and replace it across the selected range without using formulas.
Remove Non-Breaking Spaces Easily in WPS Spreadsheet
WPS Spreadsheet is a powerful, free, and lightweight alternative to Microsoft Excel. It offers full compatibility with Excel formulas, allowing you to use SUBSTITUTE, CHAR, and TRIM functions to clean up your imported data effortlessly while providing a highly familiar user interface.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the imported numbers.
- 2. Enter the cleaning formula: Select an empty column next to your data and enter =SUBSTITUTE(A1,CHAR(160),"")*1 into the formula bar.
- 3. Fill the column: Press Enter, then double-click or drag the fill handle in the bottom right corner of the cell to rapidly clean your entire dataset.
Frequently Asked Questions
Why doesn't the TRIM function remove non-breaking spaces?
The standard TRIM function is programmed to recognize and remove only standard ASCII spaces, which use character code 32. Non-breaking spaces, often generated when copying from web pages or Outlook emails, use character code 160. Because it is a different character entirely, TRIM ignores it.
How do I convert text to numbers after removing spaces?
When you use text-manipulation functions like SUBSTITUTE, the result is formatted as a text string. You can convert it back to a number by performing a mathematical operation on it, such as multiplying the formula result by 1 (e.g., adding *1 to the end), or by wrapping the entire formula in the VALUE() function.
What is the CLEAN function and does it remove non-breaking spaces?
The CLEAN function is specifically designed to remove non-printable characters from text, which correspond to character codes 0 through 31. Because the non-breaking space operates on character code 160, the CLEAN function cannot remove it. You must rely on SUBSTITUTE and CHAR(160) instead.
Can I remove non-breaking spaces without using formulas?
Yes, you can use the Find and Replace utility. Because you cannot type a non-breaking space using your spacebar, you must highlight the invisible space directly from the cell, copy it, and paste it into the 'Find what' box in the Find and Replace dialog. Leave the 'Replace with' box empty and click 'Replace All'.
