How to Remove Extra Spaces and Hidden Characters in Excel

If two Excel values look the same but a lookup, sort, or comparison still fails, an invisible character may be the reason. TRIM removes extra standard spaces, CLEAN removes many nonprinting control characters, and SUBSTITUTE can replace specific characters such as a web-imported nonbreaking space. Keep the original text beside a helper column while you test the cleanup.

Use TRIM and CLEAN safely

Place the cleanup formula in a helper column so the imported source remains available for comparison.

1. Use LEN to compare suspicious values with clean-looking values.
2. Apply CLEAN to remove many nonprinting characters.
3. Apply TRIM to reduce repeated standard spaces and remove leading or trailing spaces.
4. For web imports, replace nonbreaking spaces with SUBSTITUTE before TRIM.
5. Copy the cleaned results and paste values only after verification.

Worked example

A practical formula for many imported text problems is `=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),” “)))`. CHAR(160) is a common nonbreaking space that normal TRIM may not remove.

Common cleanup mistakes

Mistake	Why it matters
Assuming TRIM removes every invisible character	It handles standard spaces, not every Unicode or web character.
Overwriting the source too early	The original value is needed to diagnose exceptions.
Cleaning identifiers that require spacing	Some codes intentionally contain spaces.
Using CLEAN on meaningful line breaks without review	The result may join text that should remain separated.
Ignoring formulas returning empty strings	They are not always true blanks.

Verification steps

• Compare LEN before and after cleanup.
• Test an XLOOKUP or comparison that previously failed.
• Filter for values that still contain leading or trailing spaces.
• Review names, addresses, and codes for intentional spacing.
• Paste values only when downstream formulas are confirmed.

Diagnose the character before deleting it

If two values look identical but an exact comparison fails, compare their lengths first. A copied value such as North Region may contain a nonbreaking space between the words even though it looks like a normal space. A helper such as =LEN(A2) can expose the extra character, while =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) handles a common web-import case.

Do not assume CLEAN solves every hidden-character problem. It targets a defined set of control characters and does not remove all Unicode characters. If the source comes from a website, PDF, ERP export, or copied email, test several failed records before choosing the cleanup rule. Preserve meaningful line breaks in addresses or notes, and compare the cleaned result with the original using an exact lookup or =A2=B2 after both sides are normalized.

Frequently asked questions

Why does TRIM not fix a copied web value?

The text may contain a nonbreaking space or another Unicode character.

Does CLEAN remove all Unicode characters?

No. It mainly removes a defined set of nonprinting characters.

Can I use Power Query instead?

Yes. Power Query is preferable when the same cleanup repeats.

Why do two cleaned values still not match?

Check data type, punctuation, Unicode characters, and whether one value is a number stored as text.