Skip to content

Remove extra spaces and hidden characters

Clean up text pasted from websites or other systems so lookups and comparisons work.

The formula

Argument separator
Excel and Google Sheets
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

How it works

  1. SUBSTITUTE(A2, CHAR(160), " ")

    Turns non-breaking spaces (common in web copy) into normal spaces.

  2. CLEAN(…)

    Removes line breaks and other non-printing characters.

  3. TRIM(…)

    Removes leading and trailing spaces and squeezes repeated spaces to one.

Example

A: RawB: Result
" Acme Ltd "Acme Ltd
"North⏎East"NorthEast

Tips

  • Paste the results back as values (Ctrl+Shift+V in Sheets, Paste Special > Values in Excel) to replace the originals.