Function Problems

How to Remove Invisible Characters and Spaces from Blank Excel Cells

Huda Qurayshi Oct 9, 2026 872 views

Question details

The user needs to remove residual spaces and nonprinting characters from Excel cells that appear blank but are treated as nonblank by the software.

How to Remove Invisible Characters and Spaces from Blank Excel Cells

Product: Excel
Device & OS: not provided
Scenario: Importing or cleaning up data from external sources.
Observed behavior: Excel treats seemingly blank cells as nonblank because they contain hidden residual spaces or nonprinting characters introduced during data import.

Before you start

Before you begin cleaning your data, check whether the cells contain actual formulas returning blank text strings or if the data was imported from an external source like a web page, which often introduces hidden non-breaking spaces.

Solution 1Recommended

Use TRIM and CLEAN Functions Combined

Combine Excel's TRIM and CLEAN functions to strip both extra spaces and non-printable characters simultaneously.

The CLEAN function is designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. The TRIM function removes extra spaces from text, leaving only single spaces between words.

Create a helper column

Insert a new, blank column immediately next to the column containing the data you want to clean.

Enter the combination formula

In the first cell of your new helper column, type the formula =TRIM(CLEAN(A1)) (assuming A1 contains the messy data) and press Enter.

Apply formula to the entire column

Click the cell with the formula and drag the fill handle (the small square at the bottom-right corner) down to apply it to all corresponding cells.

Paste as values

Copy the newly cleaned data in the helper column, right-click the original messy data column, and select 'Paste as Values' to overwrite it with the clean data.

Pro Tip for Web Data: If you are cleaning data imported from a website, the TRIM function might not remove non-breaking spaces (CHAR 160). In such cases, use =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) instead.

Solution 2

Use Find and Replace for Standard Spaces

If you only need to remove standard residual spaces rather than complex nonprinting characters, the Find and Replace feature is a rapid alternative.

Advanced Data Cleaning

Clean Your Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet offers powerful data cleaning functions identical to Microsoft Excel, allowing you to handle messy datasets with ease. Enjoy a familiar interface and comprehensive formula support completely free.

1. Open the dataset: Launch WPS Spreadsheet and open your document containing the messy data.
2. Apply the cleaning formula: Enter =TRIM(CLEAN(A1)) in an adjacent blank cell.
3. Fill down: Drag the fill handle to apply the formula down the column and clean all non-printing characters.
4. Finalize your data: Copy the results and use 'Paste as Values' over the original data to finalize the cleanup.

Fully supports standard cleaning functions like TRIM, CLEAN, and SUBSTITUTE.100% compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Lightweight, fast, and completely free to use for daily data processing.Familiar user interface ensuring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TRIM function not remove all spaces?

TRIM is designed to remove standard spaces (ASCII character 32). If your data was imported from a web page, it likely contains non-breaking spaces (ASCII character 160). The TRIM function ignores non-breaking spaces. You must use the SUBSTITUTE function to convert them to standard spaces first.

How can I easily identify cells that look blank but contain invisible characters?

You can use the ISBLANK function to test a cell. Type =ISBLANK(A1) into an adjacent cell. If it returns FALSE even though the cell looks empty, it means there is a hidden space or nonprinting character inside.

Can I remove invisible characters using the Text to Columns feature?

Yes, if the invisible character acts as a trailing space, you can select the column, go to Data > Text to Columns, choose 'Delimited', clear all delimiter checkboxes, and click Finish. This trick forces Excel to re-evaluate the cells, often clearing out trailing spaces in the process.

Excel & Data Problems