Clean up text pasted from the web
Strip the invisible characters that survive a copy-paste from a webpage — non-breaking spaces and control characters — before comparing text.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →SUBSTITUTE runs first and swaps CHAR(160) — the non-breaking space HTML uses for — for an ordinary space; TRIM doesn't recognise CHAR(160) as whitespace, so skipping this step is why TRIM alone often looks like it did nothing.
- →CLEAN strips the non-printable control characters (ASCII 0 to 31) that sometimes ride along with pasted text, like a stray line break buried mid-string.
- →TRIM runs last, collapsing any run of now-ordinary spaces down to single spaces and removing them from both ends — the order, substitute then clean then trim, is what makes the whole thing work.
The fine print
- →A non-breaking space is invisible in the cell but is a DIFFERENT character from a regular space, so exact-match formulas like VLOOKUP or a plain = comparison fail against text that looks identical on screen.
- →Running TRIM by itself on text containing CHAR(160) is the classic mistake — it looks like it should work and silently doesn't, because TRIM only touches character 32.
- →Works identically in Excel and Google Sheets, CHAR(160) included.
Part by part
- =TRIM(
- Removes leading and trailing spaces, and collapses internal runs to single spaces. It handles the ordinary space character and nothing else.
- CLEAN(
- Strips non-printing characters in the range 0 to 31 — the control codes that arrive with text copied from terminals and old systems.
- SUBSTITUTE({{cell}}, CHAR(160), " ")
- The important part. CHAR(160) is the non-breaking space that web pages are full of, and neither TRIM nor CLEAN touches it — it has to be converted to a real space first.
Common mistakes
=TRIM(A2)Handles ordinary spaces and leaves non-breaking ones untouched, so a value pasted from a web page still fails to match an identical-looking one. This is the single most common cause of a lookup returning #N/A on data that looks correct.
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))=TRIM(CLEAN(A2))Closer, and still misses CHAR(160), because CLEAN only removes characters below 32. The substitution has to happen for the non-breaking space to become something TRIM can collapse.
=SUBSTITUTE(A2, " ", "")Removes every space including the ones between words, so "Ada Lovelace" becomes "AdaLovelace". TRIM removes only the ones that are surplus.
Questions
Why does my lookup fail on values that look identical?
Almost always trailing whitespace or a non-breaking space. Compare LEN of the two cells — a difference in length with no visible difference on screen confirms it immediately.
What exactly is CHAR(160)?
The non-breaking space, written as an HTML entity on web pages to stop a line breaking at that point. It renders identically to a normal space and is a completely different character to every comparison.
Does CLEAN remove every invisible character?
No, only codes 0 to 31. Zero-width spaces, directional marks and other modern invisibles are far above that range and survive untouched — the character-inspector tool on this site names them individually.
Should I clean on import or on use?
On import, into a separate column, so the raw values remain available. Cleaning inside every downstream formula repeats the work on every recalculation and makes the sheet harder to follow.