Formatting Issues

How to Fix Excel Find and Replace Not Removing Spaces

Elise Williams Oct 14, 2026 872 views

Question details

The user is unable to remove space characters from worksheet cells using the standard Find and Replace tool.

How to Fix Excel Find and Replace Not Removing Spaces

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to clean up worksheet data by removing space characters from cells formatted as General.
Observed behavior: Apparent spaces remain in the cells even after running Find and Replace with a standard space character in the 'Find what' field and a blank 'Replace with' field.

Before you start

Before proceeding, verify that your worksheet is not protected and the cells are not locked, as restricted permissions will prevent the Find and Replace tool from modifying data.

Solution 1Recommended

Remove Non-Breaking Spaces by Copying the Exact Character

Use this method to target and remove hidden non-breaking spaces (ASCII 160) that standard spacebar characters cannot match.

Often, spaces imported from websites or external databases are non-breaking spaces rather than standard spaces. Since Find and Replace treats these as distinct characters, typing a regular space will fail to find them.

Edit the Target Cell

Double-click a cell containing the stubborn space, or select the cell and press F2 to enter edit mode.

Copy the Unwanted Character

Carefully highlight only the space character with your mouse, copy it by pressing Ctrl+C, and then press Esc to exit cell editing.

Open Find and Replace

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

Execute the Replacement

Paste the copied character into the 'Find what' field using Ctrl+V. Leave the 'Replace with' field completely blank, then click 'Replace All'.

Remove Non-Breaking Spaces by Copying the Exact Character

Replacement Successful: Excel will now accurately identify and remove all instances of that specific non-breaking space character across your selected range or entire sheet.

Solution 2

Use the SUBSTITUTE Function to Clean Spaces

Apply a formula to generate a clean version of your data without altering the original source column directly.

Efficient Data Cleanup Tool

Easily Remove Unwanted Spaces with WPS Spreadsheet

WPS Spreadsheet provides robust data cleaning tools, including an advanced Find and Replace feature that seamlessly handles non-breaking spaces and exact text matching.

1. Open the File in WPS: Launch WPS Office and open your spreadsheet document.
2. Access Find and Replace: Press Ctrl+H to immediately open the Find and Replace dialog box.
3. Paste the Space Character: Copy the stubborn space directly from your cell and paste it into the 'Find' box, leaving the 'Replace' box empty.
4. Complete Cleanup: Click 'Replace All' to instantly remove all hidden spaces from your worksheet.

Advanced Find and Replace supports exact character targeting.Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Lightweight software that runs quickly even with massive datasets.Completely free to use for your daily office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel treat standard spaces and non-breaking spaces differently?

Standard spaces (ASCII 32) are created using the spacebar, while non-breaking spaces (ASCII 160) are designed to prevent text from wrapping to a new line. They are often imported from web pages. Excel recognizes them as two distinct characters on a programmatic level.

Can the TRIM function remove non-breaking spaces?

No, the standard TRIM function is designed only to remove regular spaces. To remove non-breaking spaces with a formula, you must combine TRIM with the SUBSTITUTE function, such as =TRIM(SUBSTITUTE(A1, CHAR(160), " ")).

How do I type a non-breaking space in the Find and Replace dialog?

If your keyboard has a numeric keypad, you can type a non-breaking space by holding down the Alt key and typing 0160 on the numpad. Alternatively, copying the character directly from the cell is often the most reliable method.

Excel & Data Problems