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

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

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 narrow in the wrong dimension: it handles only CHAR(32), so it walks straight past the character that is actually there. The CSVUndo CSV Cleaner’s “Trim extra spaces” setting recognises every Unicode whitespace character, CHAR(160) included, and clears it from the start and end of every cell in one pass — no helper columns, no Paste Special, no per-character SUBSTITUTE. That is the whole fix for the usual symptom, because a stray non-breaking space almost always sits on the end of a value that was copied out of a web page or a PDF.

Know the one thing it deliberately does not do. Its label says leading and trailing and means it: a non-breaking space sitting inside a value — the 1 234 thousands separator that European sites and PDFs use, or a Mr Smith pasted from a web page — is left exactly where it is. Nor does it collapse a run of interior spaces down to one, which Excel’s TRIM does do. Interior non-breaking spaces are still a job for SUBSTITUTE, or for a find-and-replace in a text editor. That restraint is on purpose: an interior space is usually part of the value, and silently deleting it would turn 1 234 into 1234 in a column you never asked us to touch.

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  .