Hidden Characters That Break CSV Imports: Non-Breaking Spaces, Smart Quotes, and Invisible Failures

If a CSV cell looks correct but the import still fails, silently drops the row, or creates a duplicate you swear should have matched, the problem is often a character you cannot see. Non-breaking spaces, smart quotes, en and em dashes, soft hyphens, and zero-width spaces are all valid UTF-8, so they pass every encoding check. They also break equality comparisons, duplicate detection, number parsing, and strict importers, because to a computer 'Acme Inc' and 'Acme Inc' are two different strings when one of those spaces is a non-breaking space. This guide shows you which invisible characters cause the most damage, why your spreadsheet's TRIM function does not remove them, how to detect them, and how to strip them before upload.

This is a different problem from corrupted encoding. If your file shows garbage like Garcéa instead of Garcia, that is mojibake or a BOM mismatch, and it is covered in our encoding posts. Here, every character is a legitimate, correctly-encoded Unicode code point. The bytes are fine. The characters just happen to be invisible and destructive.

Why does a CSV cell look fine but still fail to import?

A monospace or proportional font renders a non-breaking space, a zero-width space, and a regular space almost identically, or not at all. Your eyes see 'John Smith' and read it as normal. The importer sees the underlying bytes: J, o, h, n, U+00A0, S, m, i, t, h. When it compares that value against an existing record, runs a required-field regex, or tries to parse it as a number, the hidden character changes the outcome. Nothing warns you, because from the file's perspective nothing is wrong. This is why these bugs survive a visual review, a spell check, and even an encoding validation pass.

The failures cluster into four categories: rejected rows (a strict importer refuses a value that contains an unexpected character), silent mismatches (a lookup or key match fails because the key has a hidden character), false duplicates (two records that should be one, because only one copy carries the invisible mark), and broken number parsing (a numeric field arrives as text because a non-breaking space is wedged between the digits).

The non-breaking space (U+00A0): the most common offender

The non-breaking space is Unicode code point U+00A0, decimal 160, the same character HTML writes as  . It shows up constantly in data copied from web pages, PDFs, Word documents, and reports that were formatted for print. It looks exactly like a normal space (U+0020, decimal 32) but behaves nothing like one.

The trap that catches almost everyone: your spreadsheet's TRIM function will not remove it. Excel's TRIM was designed to strip only the regular space character, decimal 32. A non-breaking space is decimal 160, so TRIM walks right past it. You run TRIM, the cell still looks like it has a trailing space, and you conclude the data is 'clean' when it is not. The same is true of Google Sheets, whose TRIM removes leading, trailing, and repeated regular spaces but leaves non-breaking spaces in place.

What you see in the cell:   Acme Inc → looks like a trailing space
What is actually stored:    Acme Inc\u00A0 → a non-breaking space

After =TRIM(A1):            Acme Inc\u00A0 → STILL there, unchanged
After =TRIM(SUBSTITUTE(A1,CHAR(160)," ")):  Acme Inc → fixed

In Excel, the working formula is =TRIM(SUBSTITUTE(A1,CHAR(160)," ")). SUBSTITUTE swaps every non-breaking space (CHAR(160)) for a normal space first, and then TRIM can finally remove it. If the value also carries other non-printing characters, wrap the whole thing in CLEAN: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))). CLEAN removes the first 32 non-printing ASCII characters (values 0 through 31), such as stray tabs and line breaks, but note that it does not touch CHAR(160) either, which is exactly why SUBSTITUTE has to run first.

Zero-width spaces and stray BOMs (U+200B, U+FEFF)

Zero-width characters are worse than the non-breaking space because they occupy no visual width at all. There is nothing to see, not even a suspicious gap. The main offenders are U+200B (zero-width space), U+200C (zero-width non-joiner), U+200D (zero-width joiner), U+2060 (word joiner), and U+FEFF, which is the byte order mark when it sits at the start of a file but becomes an invisible zero-width no-break space when it appears anywhere else.

A stray U+FEFF in the middle of a file is a classic way for CSV imports to break. When a BOM lands on the first header, you get a column named U+FEFF plus 'Account' instead of 'Account', and the importer reports a missing required column it is quite literally looking at. When a zero-width space lands inside a value, it silently splits your data into non-matching versions of the same thing.

This is the failure mode behind mysterious duplicates. Picture a customer list where 'John Smith' appears twice with two different IDs, and a database uniqueness check that passed anyway. The cause is a single zero-width space in one of the two name fields: 'John Smith' and 'John​Smith' are different strings, so the dedupe never fired. The same mechanism defeats VLOOKUP and INDEX/MATCH, join keys, SKU matching, and email-based record matching in CRMs. Everything looks identical and nothing matches.

Two rows that should dedupe to one:

  id, name,               email
  1,  John Smith,          j.smith@acme.com
  2,  John\u200bSmith,      j.smith@acme.com

Column 'name' looks identical in both rows. The zero-width space (U+200B)
after 'John' in row 2 means the values compare as UNEQUAL, so duplicate
detection keyed on name misses the match and you import two customers.

Smart quotes, curly apostrophes, and fancy dashes

These are visible, but people rarely notice them, and strict importers care a great deal. When you copy text from Word, a web page, or a design tool, autocorrect quietly replaces straight punctuation with typographic versions. The straight double quote (U+0022) becomes curly quotes U+201C and U+201D. The straight apostrophe (U+0027) becomes a curly apostrophe, U+2019. Hyphens get promoted to en dashes (U+2013) and em dashes (U+2014), and a soft hyphen (U+00AD) can hide inside a word as an invisible optional line-break point.

Two things go wrong. First, the curly double quote is not a quoting character to a CSV parser. Only the straight double quote wraps a field, so a value like a product title with curly quotes may parse correctly but fail an exact-match validation, while a stray straight quote elsewhere throws a quoting error. Second, a curly apostrophe in a name like O’Brien will not match the same name typed with a straight apostrophe, so O'Brien and O’Brien become two different customers. Dashes cause the same silent mismatches in SKUs and part numbers: 'ABC-123' with a hyphen and 'ABC–123' with an en dash are not the same key.

Straight vs. smart, and why keys fail to match:

  Straight apostrophe:  O'Brien      (U+0027)
  Curly apostrophe:     O’Brien      (U+2019)   → different string

  Hyphen SKU:           ABC-123      (U+002D)
  En-dash SKU:          ABC–123      (U+2013)   → different key

  Straight quote:       15" monitor  (U+0022)
  Curly quote:          15” monitor  (U+201D)   → fails exact match

How to detect invisible characters before they cost you an import

You cannot fix what you cannot see, so the first job is to make the invisible visible. A few reliable techniques:

How to strip hidden characters before upload

Once you know what to hunt for, the removal is straightforward. In a spreadsheet, the layered formula =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) handles non-breaking spaces plus the low-range non-printing characters, but you still need separate SUBSTITUTE passes for zero-width and typographic characters, because neither TRIM nor CLEAN removes them. If you are comfortable with code, a single regular expression covers the zero-width family: replace [\u200B-\u200D\uFEFF] with an empty string, then substitute smart quotes and dashes back to their straight ASCII equivalents.

The full cleanup, conceptually:

  1. Replace U+00A0 (non-breaking space) → regular space, then trim
  2. Remove U+200B, U+200C, U+200D, U+FEFF (zero-width family) → nothing
  3. Replace U+2019 → '  and  U+201C / U+201D → "
  4. Replace U+2013 / U+2014 → -   and remove U+00AD (soft hyphen)
  5. Re-check LEN and dedupe keys after cleaning

The tedious part is doing all five passes on every text column and not missing one, which is exactly where these bugs come back. This is the kind of cleanup PipeSheets is built for: upload the file, and Quick Clean trims whitespace across every column in one pass, while a saved find-and-replace pipeline handles the non-breaking spaces, zero-width characters, and smart-quote-to-straight substitutions in a single run. The before/after preview shows you the cleaned values and the detected column types, so a number field that was importing as text because of a non-breaking space shows up as a number again before you download.

Because PipeSheets runs the same pipeline every time, you can save your invisible-character cleanup once and reuse it on every export from the same source, which matters when the problem comes from a report or a web export that will keep producing the same hidden characters next month. Export the result as CSV or XLSX and upload a file where the bytes and the characters both match what the importer expects.

The invisible-character preflight checklist

Invisible characters are frustrating precisely because they defeat the checks people trust: the file opens fine, the encoding validates, the cell looks right. The fix is to stop trusting your eyes and start checking the code points. Strip the non-breaking spaces, zero-width marks, and typographic punctuation before you upload, and the mysterious rejected rows, phantom duplicates, and numbers-as-text problems disappear with them.