Stray whitespace and invisible characters
Two values look identical on screen but differ by a trailing space, a non-breaking space, or a hidden character.
What it looks like
Before
| what is actually there | |
|---|---|
| ana@example.com | no extra characters |
| ana@example.com | trailing space |
| ana@example.com | leading non-breaking space |
After
| what is actually there | |
|---|---|
| ana@example.com | unchanged |
| ana@example.com | trimmed |
| ana@example.com | trimmed |
How it happens
- Copying from web pages, PDFs, and email brings along non-breaking spaces and zero-width characters.
- Fixed-width exports pad every value with spaces.
- A byte order mark from the start of a file ends up inside the first cell or the first header.
What it breaks
- Exact matches fail, so joins and lookups return nothing for rows that look correct.
- Duplicate checks miss pairs that differ only by a space.
- A column header with a hidden character cannot be found by name.
How to spot it
- Compare the length of each value with the length after trimming.
- Count distinct values before and after trimming and collapsing spaces.
- When a lookup fails on a value you can see, suspect a character you cannot.
How to fix it
- Trim leading and trailing whitespace, including non-breaking spaces.
- Collapse runs of internal spaces to one, unless spacing is meaningful in that column.
- Remove zero-width characters and byte order marks. Leave ordinary accented letters alone; they are not noise.
- Near-duplicate recordsTwo rows describe the same real thing but differ enough that an exact comparison misses them.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Case example: Marketing analyticsFive clients, five definitions of a campaign
TermsWhitespace, Encoding, Normalization