TRIM Isn’t Removing the Spaces — It’s a Non-Breaking Space

Updated July 2026 · By the CSVUndo team · 5 min read

The quick answer: Excel’s TRIM was built to remove one thing — the ordinary ASCII space, CHAR(32). The “space” that survives it, the one that keeps your VLOOKUP, joins and de-duplication failing on values that look identical, is almost always a non-breaking space: CHAR(160), Unicode U+00A0. It comes in whenever data is copied from web pages, PDFs, HTML tables or certain CSV exports. Convert it to a real space first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). It is a legitimate character, not encoding corruption — and it can be cleared across a whole file in one click.

Why TRIM leaves the space behind

Because TRIM only recognises one specific character as “a space.” Microsoft designed it to strip the 7-bit ASCII space, CHAR(32) — leading, trailing, and repeated runs collapsed to one. That is the space your keyboard’s spacebar makes. A non-breaking space is a different character with a different code, so as far as TRIM is concerned there is nothing there to remove. The cell looks trimmed, the value looks clean, and an exact-match comparison still says "Smith " and "Smith" are different rows.

That is why the failure is so quietly maddening: the two values look identical, so a lookup returning #N/A, a join that drops rows, or a de-dupe that leaves obvious duplicates behind all read like formula bugs rather than a hidden byte. Here is exactly which whitespace TRIM handles and which it does not:

The two escapees sit on opposite sides of the range: tabs and control codes fall below 32 and yield to CLEAN, while the non-breaking space sits above it at 160 and is missed by both TRIM and CLEAN — which is why it needs SUBSTITUTE first.

Detect it with CODE(RIGHT(...))

Read the last character’s number. Point a helper formula at the end of a suspect cell:

1. In an empty column type =CODE(RIGHT(A2,1)) and fill down.
2. A result of 32 is an ordinary trailing space — plain TRIM will clear it.
3. A result of 160 is a non-breaking space — that is your culprit, and you need the fix below.

To check a leading character instead use =CODE(LEFT(A2,1)). The trailing case is by far the most common, because a stray space at the end of a value is exactly what silently breaks matching.

Remove it in Excel

Turn the non-breaking space into a normal one, then trim. SUBSTITUTE can target the exact character by its code, which is what makes it work where TRIM alone cannot:

1. =SUBSTITUTE(A2,CHAR(160)," ") replaces every CHAR(160) with a real space.
2. Wrap that in TRIM to strip the now-ordinary spaces: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
3. To also remove tabs and other non-printing control characters, add CLEAN: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).

Copy the helper column, then Paste Special → Values back over the original so the cleaned text replaces the formulas. One honest caveat: CLEAN removes control codes 0–31 but does not remove CHAR(160) itself — 160 is above its range — so the SUBSTITUTE step is still required even when you add CLEAN. If your data also carries other Unicode spaces, each needs its own SUBSTITUTE, which is where a bulk tool starts to save real time.

Clean a whole CSV at once

Fix the raw file, not one column at a time. Excel’s TRIM is deliberately narrow, but a general whitespace trimmer need not be. The CSVUndo CSV Cleaner’s “Trim extra spaces” setting trims every Unicode whitespace character, clearing CHAR(160) along with ordinary spaces in one pass over the sheet — no helper columns, no Paste Special, no per-character SUBSTITUTE. The file arrives already clean, so the matching problem is gone before the data ever reaches Excel.

This is also why the same invisible byte quietly sabotages other jobs. If your de-duplication is leaving obvious duplicates behind, a trailing CHAR(160) on one copy is a prime suspect — the rows are not byte-for-byte equal, so they are not treated as duplicates. And if a column of what should be numbers won’t add up, the culprit is often the related problem of a number stored as text, sometimes with a non-breaking space padding it.

It is not encoding corruption

A non-breaking space is a real, intended character — nothing is broken. It is written as   in HTML and is used deliberately on web pages to stop text wrapping at an awkward point, which is exactly why copying from a browser or a PDF drags it into your spreadsheet. Unlike mojibake — where names turn into é or ’ because a file was read in the wrong encoding — the data here is perfectly intact. You are simply removing one whitespace character you did not want, with no lost information, so this is a clean, fully reversible fix rather than a salvage job.

Frequently asked questions

Sources: The behaviour of TRIM versus the non-breaking space is discussed in Microsoft’s Q&A on TRIM not behaving as expected. The non-breaking space is Unicode code point U+00A0, decimal 160, the HTML entity  .