Data Import & Export

How to Remove Nonbreaking Spaces from Imported Excel Data

Anum Mushtaq Oct 14, 2026 868 views

Question details

The user needs to remove persistent nonbreaking spaces from imported Excel data, as standard cleanup methods fail and spaces reappear when formulas are pasted as values.

How to Remove Nonbreaking Spaces from Imported Excel Data

Product: Excel
Device & OS: not provided
Scenario: Importing data from external sources (such as web pages) into an Excel spreadsheet, resulting in unwanted spaces that interfere with data formatting and calculations.
Observed behavior: Spaces in the data appear to be removed initially but return when the formula result is copied and pasted as values. These are likely nonbreaking spaces represented by character code 160.

Before you start

Verify that the spaces in your data are actually nonbreaking spaces. These often occur when copying text from HTML web pages (character code 160) and cannot be removed using the standard TRIM function alone.

Solution 1Recommended

Use SUBSTITUTE and TRIM Functions

Combine the CLEAN, TRIM, and SUBSTITUTE functions to specifically target and replace nonbreaking spaces (CHAR 160) with an empty string.

The standard TRIM function only removes regular spaces (character code 32). By nesting functions, you can force Excel to replace the nonbreaking spaces first before cleaning and trimming the remaining text.

Select a blank cell

Click on an empty cell adjacent to your imported data. For example, if your data is in cell A3, select cell B3.

Enter the SUBSTITUTE formula

Type the formula =CLEAN(TRIM(SUBSTITUTE(A3,CHAR(160),""))) to remove nonbreaking spaces. If you want to remove regular spaces as well, use =CLEAN(SUBSTITUTE(SUBSTITUTE(A3,CHAR(160),""),CHAR(32),"")).

Apply to the entire column

Press Enter to see the cleaned result. Then, click and drag the fill handle at the bottom right corner of the cell to apply the formula to the rest of your dataset.

Paste as values

Select the newly cleaned cells and copy them (Ctrl + C). Right-click the destination cells and select 'Paste as Values' to permanently keep the cleaned text without the formula.

Verification Tip: You can verify the formula result directly in the Formula Bar by highlighting the formula text and pressing F9. Be sure to press Esc to exit without accidentally overwriting your formula.

Solution 2

Use the Find and Replace Tool

Use a keyboard shortcut within the Find and Replace dialog to manually locate and eliminate nonbreaking spaces across your entire worksheet.

Effortless Data Management in WPS Office

Seamlessly Clean Imported Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including advanced text functions like SUBSTITUTE, CLEAN, and TRIM, helping you to rapidly sanitize imported data without formatting issues.

1. Open your imported data: Launch WPS Spreadsheet and open the file containing the messy imported data.
2. Apply the cleanup formula: In an adjacent blank column, input the combined =SUBSTITUTE function targeting CHAR(160).
3. Smart Fill across rows: Use the smart fill handle to drag and apply the formula to all relevant cells instantly.
4. Paste Special as Values: Copy the cleaned results, right-click, and choose 'Paste Special' > 'Values' to finalize your sanitized data.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx, .csv).Advanced Find & Replace capabilities for rapid data cleaning and manipulation.Lightweight installation with rapid processing for large imported datasets.Free and highly intuitive interface familiar to Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the TRIM function remove all spaces from my imported data?

The standard TRIM function is designed only to remove regular spaces (character code 32). It does not recognize or remove nonbreaking spaces (character code 160), which are commonly embedded in data copied from HTML web pages.

How can I check if a space is a nonbreaking space?

You can use the CODE function to identify the exact character. For example, if the unknown space is the first character in cell A1, type =CODE(LEFT(A1, 1)). If the formula returns 160, it is a nonbreaking space. If it returns 32, it is a regular space.

Can I remove nonbreaking spaces on a laptop without a numeric keypad?

If you don't have a numeric keypad to type Alt + 0160 for Find and Replace, you can copy the nonbreaking space directly from the cell. Select the cell, highlight the blank space in the Formula Bar, copy it (Ctrl + C), and then paste it into the 'Find what' field in the Find and Replace dialog.

Excel & Data Problems