FormulaText
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
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
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.