TRIM vs CLEAN: the import-cleanup pair (and what both miss)
Pasted and imported text carries invisible junk that breaks lookups and duplicates 'identical' values. TRIM and CLEAN each remove a different kind — and the most common web-import culprit, the nonbreaking space, slips past BOTH.
The differences at a glance
| TRIM | CLEAN | SUBSTITUTE | CODE | |
|---|---|---|---|---|
| Removes | Leading/trailing spaces + collapses internal runs to one | Nonprintable control characters (codes 1-31) | Whatever you tell it — the gap-filler | Removes nothing — DIAGNOSES by revealing char codes |
| Nonbreaking space (CHAR(160)) | NOT removed | NOT removed | SUBSTITUTE(A2,CHAR(160)," ") converts it | CODE(MID(...))=160 exposes it |
| In-cell line breaks (CHAR(10)) | Kept | Removed | Or targeted: SUBSTITUTE(...,CHAR(10),", ") | code 10 |
| Typical source | Human typing, CSV padding | Legacy exports, database dumps | Web pages, HTML copy-paste | — |
| Compatibility | Universal | Universal | Universal | Universal — all verified by execution |
Which should you use?
- TRIM — First resort whenever lookups mysteriously miss or 'duplicates' won't dedupe — stray spaces cause most of it.
- CLEAN — Data from old systems or binary-ish exports showing boxes/garbage glyphs, or when in-cell line breaks need flattening.
- The full chain — Web-pasted data: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) handles all three kinds in one pass — the order (SUBSTITUTE inside, TRIM outside) matters so converted nbsp-spaces get collapsed too.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
All four execute identically in every LibreOffice version we test; Google Sheets matches in our executed run; desktop Excel behavior matches per Microsoft's documentation — we do not run desktop Excel. Diagnose before cleaning: LEN(A2)-LEN(TRIM(A2)) counts suspect spaces, and CODE(MID(A2,k,1)) at the failing position names the exact culprit — 160 means nonbreaking space, and only SUBSTITUTE will save you. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 16 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| The full cleanup chain | =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) |
| Why doesn't my lookup match? | =CODE(MID(A2,LEN(A2),1)) |
| Flatten line breaks readably | =SUBSTITUTE(A2,CHAR(10),", ") |
Full per-version details on each function page: TRIM · CLEAN · SUBSTITUTE · CODE.