Non-breaking spaces hiding in my Excel data — why TRIM doesn't work
TRIM() isn't fixing your spacing problem because the character isn't a regular space — it's a non-breaking space, invisible and untouched by TRIM. Here's how to actually remove it
Why TRIM looks like it's doing nothing
TRIM was designed to clean up ordinary typing mistakes — extra spaces from double-tapping the spacebar, or a stray space left at the end of a cell. It was never built to recognize every kind of whitespace-like character in Unicode, and a non-breaking space is exactly that: a different character with a different code point that happens to render as blank, so the cell looks identical to a normal one whether or not TRIM has run.
Where non-breaking spaces come from
The most common source is copying data out of a web page — many sites insert a non-breaking space between words or numbers specifically so browsers won't wrap a line in the wrong place, and that character comes along for the ride when you paste into Excel. Some point-of-sale and ERP system exports do the same thing, and a handful of PDF-to-spreadsheet converters introduce it as an artifact of how they reconstruct text layout.
The fix: substitute it out, then trim
The reliable formula is =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) — SUBSTITUTE swaps every non-breaking space (character code 160) for a regular space first, and then TRIM can do its normal job of collapsing and removing them. It's worth knowing that CLEAN() won't help here either: CLEAN only removes the first 32 non-printable control characters, and a non-breaking space at code 160 is outside that range entirely, so it passes straight through CLEAN untouched as well.