Subsection 2.1
Where dirty whitespace comes from
Reading
Before you reach for a formula, it helps to know where dirty whitespace came from. Almost all of it comes from one of three places:
- Humans typing. A field rep typed a space before the name to make it look like a subgroup.
- Copy-paste from Word or web pages. Microsoft Word loves non-breaking spaces (
CHAR(160)). Web pages paste in line breaks (CHAR(10)). - Form exports. Survey tools and form exports often add trailing spaces to every field for padding.
You cannot fix the people. You can fix the columns.
The three whitespace formulas, in plain language
| Formula | What it does | What it does not do |
|---|---|---|
=TRIM(A2) | Removes leading, trailing, and double spaces. | Does not remove line breaks or non-breaking spaces. |
=CLEAN(A2) | Removes non-printing characters, including line breaks (CHAR(10)). | Does not touch non-breaking spaces (CHAR(160)). |
=SUBSTITUTE(A2, CHAR(160), "") | Removes non-breaking spaces specifically. | Does not remove any other character unless you nest more SUBSTITUTEs. |
The reason all three exist is because each one was written to solve a different decade’s spreadsheet problem. TRIM was written for printers in the 1980s. CLEAN was written for daisy-wheel control characters. SUBSTITUTE is the general-purpose replacement function.
Why CHAR(160) is the silent killer
A non-breaking space looks identical to a regular space. TRIM ignores it. Your VLOOKUP fails. The only way to find it is to wrap with SUBSTITUTE(..., CHAR(160), "") or to check =LEN(A2) against the expected length.
Action: Mark this page complete when you have finished the activity above.