Trim spaces

How to trim all spaces in a spreadsheet, including non-breaking spaces

1. Drop the file. Every cell of the chosen sheet is checked. Nothing is uploaded.
2. Read the report. Per column: how many cells changed, and whether they held leading, trailing or double spaces, non-breaking spaces, zero-width characters, tabs or line breaks.
3. Adjust two options. Keep line breaks inside cells if they are meant to be there; keep double spaces if the column is fixed-width text.
4. Download the cleaned sheet. CSV or .xlsx. Values are kept as text, so codes such as 00123 are not turned into numbers.

Why TRIM did not remove the space

Microsoft's own TRIM documentation says it plainly: TRIM was designed to remove the 7-bit ASCII space, character 32. The Unicode non-breaking space, character 160, is not removed. That is the character you get when you copy a table from a web page, an e-mail or many PDFs, and it is why =TRIM(A2) returns a value that still does not match in a VLOOKUP. CLEAN does not help either: it removes control characters 0–31, not 160.

The usual workaround is =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in a helper column, then paste as values, then delete the original — per column. This page does that for every column at once, and for the other invisible characters as well.

Everything it removes, by name

• Leading and trailing spaces — what TRIM removes.
• Double spaces inside a cell — collapsed to one, as TRIM does. Optional.
• Non-breaking space (U+00A0, CHAR(160)) — turned into an ordinary space, then trimmed.
• Zero-width characters (U+200B, U+200C, U+200D, U+2060 and the byte-order mark U+FEFF) — deleted outright; they are invisible even in the formula bar.
• Other space characters — the full-width ideographic space (U+3000) common in Japanese and Chinese data, and the thin and narrow spaces typesetting programs insert.
• Tabs and line breaks — turned into spaces. Line breaks can be kept if they belong in the cell.

The report is the useful part

Knowing which character was in a column tells you where the data came from and whether the source will keep sending it. A column with non-breaking spaces was copied from a web page; zero-width characters point at a PDF extraction or a CMS; trailing spaces usually come from a fixed-width export. The table above the cleaned sheet lists every column that changed with a count for each kind.

What it leaves alone

• The header row, unless you untick First row is a header.
• Numbers and dates stored as numbers in the .xlsx — they have no spaces to trim.
• Spaces between words — one space stays one space.

When the same file arrives with the same spaces every month

If an export comes with stray spaces every period, cleaning it is a step in a job, and the job is where the next month's file gets checked before anything is matched. TableDI 2 is a desktop app for file work you redo every period: it keeps your sources, rules and delivery as a job, so next month you drop in the new files and run it again. Everything runs on your own machine.