Excel TRIM & CLEAN: Fix Messy Text Data
Data from imports, copies, and web scraping often has invisible junk characters and extra spaces. TRIM and CLEAN fix them.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Clean up text that has extra spaces, line breaks, and non-printing characters from a database import"
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))Works from inside out: SUBSTITUTE replaces non-breaking spaces (CHAR 160) with regular spaces. CLEAN removes non-printing characters (CHAR 0-31). TRIM removes leading/trailing spaces and collapses internal double-spaces to single spaces.
Step-by-step breakdown
- CHAR(160) is the non-breaking space — common in web data, invisible but breaks lookups
- SUBSTITUTE replaces it with a regular space (CHAR 32)
- CLEAN removes all non-printing characters (ASCII 0-31: tabs, line feeds, etc.)
- TRIM removes leading/trailing spaces and collapses multiple spaces to one
- Nest order matters: SUBSTITUTE first (converts CHAR 160), then CLEAN, then TRIM
Edge cases & warnings
- CLEAN doesn't remove CHAR(160) non-breaking spaces — that's why SUBSTITUTE is needed
- CLEAN doesn't remove Unicode characters above CHAR(127) — some imported data has these
- Tabs (CHAR 9) and line breaks (CHAR 10, 13) are removed by CLEAN
- After cleaning, VLOOKUP/XLOOKUP may start finding matches that previously failed
Examples
" Hello World "
Hello World"Data\nWith\nLinebreaks"
DataWithLinebreaks"Name\xa0Here (non-breaking space)"
Name HereFrequently asked questions
Why does VLOOKUP fail even when values look identical?
Usually invisible characters: non-breaking spaces, tabs, or non-printing chars. Apply =TRIM(CLEAN(SUBSTITUTE(cell, CHAR(160), " "))) to both the lookup value and the table.
How do I find cells with hidden characters?
Compare LEN(A1) with LEN(TRIM(CLEAN(A1))). If they differ, the cell has hidden characters.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.