Remove Non-Breaking Spaces (TRIM Won’t)

Excel Formulas › Data Cleaning

All versionsTRIM

You ran TRIM but stray spaces remain — the culprit is the non-breaking space (CHAR 160) from web and PDF pastes. Substitute it for a normal space first, then TRIM and CLEAN.

Quick formula: fully clean text TRIM alone can't fix:
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
Convert non-breaking spaces to regular spaces, strip control characters with CLEAN, then TRIM the rest.

Functions used (tap for the full reference guide):

TRIMSUBSTITUTE

The example

Web-pasted text with hidden spaces.

AB
1RawClean
2" Hello  world "Hello world

The formula

The formula:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) // swap CHAR(160), CLEAN, TRIM

How it works

How it works:

  1. TRIM only removes CHAR(32) — the normal space. It ignores CHAR(160), the non-breaking space.
  2. SUBSTITUTE(A2, CHAR(160), " ") turns non-breaking spaces into normal ones.
  3. CLEAN strips other non-printable control characters from imports.
  4. Then TRIM collapses the now-normal extra spaces — the order matters.

This is the #1 “TRIM isn’t working” cause. Web pages and PDFs use CHAR(160) to prevent line breaks, and it survives TRIM and breaks lookups (“Acme Inc” ≠ “Acme Inc”). Always SUBSTITUTE(text, CHAR(160), " ") before TRIM when data came from a browser or PDF — it’s the fix for invisible mismatches.

Try it: interactive demo

Live demo

Text with extra/odd spaces.

Cleaned: · len

Variations

Just the substitution

Swap CHAR(160):

=SUBSTITUTE(A2, CHAR(160), " ")

Remove all spaces

For a match key:

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""), " ", "")

Find a hidden char

Its code:

=CODE(MID(A2, position, 1))

Pitfalls & errors

TRIM can’t see CHAR(160). Substitute it first — TRIM only handles CHAR(32).

Order matters. Substitute → CLEAN → TRIM.

Other invisibles. CHAR(9) tabs, CHAR(13) returns — CLEAN catches most control characters.

Practice workbook

📊
Download the free Remove Non-Breaking Spaces (TRIM Won't) practice workbook
A non-breaking-space sheet with the substitute-only, remove-all, and find-char variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

Why doesn't TRIM remove all my spaces in Excel?
TRIM only removes the normal space (CHAR 32). Non-breaking spaces (CHAR 160) from web/PDF survive. Use =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
How do I fix lookups that fail on identical-looking text?
A hidden CHAR(160) is usually the cause. Substitute it for a normal space on both sides before matching.
How do I find a hidden character?
Use =CODE(MID(A2, position, 1)) to see the character code at a position.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Clean text · Remove line breaks · Normalize match key

Function references: TRIMSUBSTITUTE