Trim Whitespace in a CSV

Four different problems hide under the word whitespace: padding at the ends of a value, runs of spaces inside it, non-breaking spaces that look normal and compare unequal, and zero-width characters that are completely invisible. This page handles all four and counts each separately, so you find out which one your file actually has. It runs in this tab.

Still not matching after a clean? Try the fuzzy matcher, which measures how far apart two values are.

Four problems, one word

"Trim whitespace" sounds like one operation. In a real export it is four, and only the first is the one people expect.

Padding at the ends. " Ada Lovelace ". Breaks every join, every group-by and every lookup, and is at least visible if you click into the cell.
Runs of spaces inside. Grace Hopper. Breaks exact matching and not much else. Collapsing it changes the value rather than tidying it, which is why that switch is off by default.
Non-breaking spaces. U+00A0. Arrives whenever data has been pasted out of a web page, a Word document or a PDF. It renders as a space, it is not a space, and no amount of squinting will tell you it is there. A search for a normal space will not find it and a trim that only knows about \s in some languages will not remove it.
Zero-width characters. U+200B and its neighbours, plus the byte order mark when it ends up mid-string. These have no width at all. Two cells that are pixel-identical on screen can differ by one of these, and that is the single most common reason a lookup returns nothing on a value you are looking straight at.

The last two are the reason this page exists rather than being a checkbox on some other page. They are on by default and counted separately, so the report tells you which problem your file actually had.

Worked example: three cells that look fine

This file has one visible problem and two invisible ones. The quoting is what makes the first one visible at all:

name,city
"  Ada Lovelace ","Lon[U+200B]don"
"Grace  Hopper","New York"

The [U+200B] is written out here because on your screen it would be nothing at all: London with a zero-width space after the third letter. Run the page with Runs of spaces inside switched to Collapse to one and everything else on its default:

name,city
Ada Lovelace,London
Grace Hopper,New York

2 rows · 2 columns covered · both whitespace trimmed · internal runs collapsed
· 3 cells changed · 1 cell had padding · 1 cell had double spaces
· 1 cell held an invisible character

Three cells changed and the report says why each one did. The line that matters is the last: 1 cell held an invisible character. Nothing you could have seen would have told you that, and the warning underneath spells out the consequence: those are the usual reason two identical-looking values refuse to match in a lookup.

What the CSV reader already did

Worth saying plainly, because it explains a result that otherwise looks like the tool doing nothing. The CSV parser used across this site strips padding around unquoted fields as it reads them. A file whose spaces were outside the quote marks arrives here already clean, and the page will honestly report that nothing needed changing.

name ,  city        already trimmed on the way in
"name ","  city"    still padded: the spaces are inside the quotes

So the cells this page changes are the quoted ones, plus every case involving an exotic space or an invisible character, which no parser touches. If your file has a whitespace problem you can see and this page reports zero changes, the odds are the value is not what you think it is: open it in the viewer and look at the actual cell.

The byte order mark at the start of a Windows Excel export is also removed before any of this runs, on every CSV page on the site. Left in place it attaches to the first column name and that column then refuses to match its own name for the rest of the pipeline.

The exotic spaces, in full

Non-breaking spaces is a slightly modest name for what that switch does. It converts every space character Unicode defines to an ordinary one:

U+00A0  no-break space          pasted from the web, Word, PDFs
U+1680  ogham space mark
U+2000  to U+200A               en quad through hair space, from typesetting
U+202F  narrow no-break space   French thousands separators
U+205F  medium mathematical space
U+3000  ideographic space       CJK input methods

The two you will actually meet are U+00A0 and U+202F. The first comes from copy and paste. The second turns up in numbers formatted for a French or Scandinavian audience, where it separates thousands; the number cleaner handles that case as part of parsing, but if the column is text you want it dealt with here.

Invisible characters covers U+200B (zero-width space), U+200C and U+200D (the zero-width joiners, which arrive with emoji and with some Indic and Arabic text), U+2060 (word joiner), and U+FEFF wherever it appears inside a value. These are removed rather than converted, because there is nothing to convert them to.

One caution about the joiners: in genuinely multilingual text U+200C and U+200D are meaningful, and removing them can change how a word renders. If your file holds Persian, Hindi or Malayalam text and the rendering matters, turn that switch off and rely on the others.

Trim, but only as far as you meant

Both ends is the default and is what almost everyone wants.
The start only or the end only exist for fixed-width work, where the padding on one side is structural and the padding on the other is not.
Neither turns the trimming off entirely and leaves the other three switches doing their work. Use it when you want the invisible characters gone and the layout of the values untouched.
The header row is cleaned by default. A header with a trailing space is a column whose name will not match a string literal anywhere downstream, and it is worth fixing at the same time. Switch it off if the header is deliberately padded, and use the header cleaner when the header needs more than whitespace work.
Tabs and newlines inside a quoted cell are treated as whitespace by the trimming, so a value that ends in a stray newline comes back without it. Collapsing runs only affects spaces and tabs, not line breaks in the middle of a cell.

Where whitespace bugs actually surface

The reason to run this before anything else is that whitespace problems never announce themselves. They show up as some other tool being wrong.

A dedupe that finds nothing, because Acme Inc and Acme Inc are two distinct strings.
A diff that reports every row as changed, because one export pads its cells and the other does not.
A pivot with two rows for the same region, one of them holding almost nothing.
A join that drops half its rows, with no error, because the key column on one side has a non-breaking space in it.
A database import that fails a uniqueness constraint on values that look identical in the error message.

In every one of those, the counts on this page are the diagnostic. If it reports 4,000 cells holding a non-breaking space, you have found your bug and you did not have to guess.

Frequently Asked Questions

Why does the tool say nothing changed when my cells clearly have spaces?

Because the CSV reader already strips padding around unquoted fields as it reads them, so a file whose spaces were outside the quote marks arrives here clean. The cells this page changes are the quoted ones, plus everything involving an exotic space or an invisible character, which no parser touches.

What is a non-breaking space and why does it matter?

U+00A0. It renders exactly like an ordinary space and compares unequal to one, so a value containing it will not match its normal-looking twin in any join, lookup or dedupe. It arrives whenever data has been pasted out of a web page, a Word document or a PDF, which is often.

What are zero-width characters?

Characters with no width at all, mainly U+200B and the zero-width joiners, plus a byte order mark that has ended up in the middle of a value. Two cells that are pixel-identical on screen can differ by one of these, and that is the most common reason a lookup returns nothing on a value you are looking straight at.

Should I collapse runs of spaces inside a value?

Only when you mean to. Collapsing changes the value rather than tidying it, which is why the switch is off by default. It is right for names and addresses being used as match keys and wrong for anything where the spacing is structural, such as a fixed-width extract.

Does it clean the header row too?

Yes, by default. A header with a trailing space is a column whose name will not match a string literal anywhere downstream. Switch it off if the padding is deliberate, and use the header cleaner when the header needs more than whitespace work.

Will it remove line breaks inside a cell?

Only from the ends. A quoted value that finishes with a stray newline comes back without it, because trimming treats tabs and newlines as whitespace. A line break in the middle of a cell is left alone, and collapsing runs only affects spaces and tabs.

Is it safe on non-Latin text?

Mostly, with one caution. The zero-width joiners U+200C and U+200D are meaningful in Persian, Hindi, Malayalam and similar scripts, and removing them can change how a word renders. Turn Invisible characters off for those files and rely on the other switches.

Is anything uploaded?

No. There is no upload endpoint on this page. The file is read, cleaned and rewritten by JavaScript in your own tab, and nothing is kept between visits.

Find the space you cannot see

Free, no account, no upload. Drop the file, read the four counts, take the CSV.

Back to the whitespace cleaner