Home / Guides / Why pasted text breaks spreadsheets, CSV files and code
Why pasted text breaks spreadsheets, CSV files and code
Spreadsheets, CSV importers and programming languages expect straight quotes and ordinary spaces wherever their syntax needs them. Curly quotes, non-breaking spaces and long dashes look fine to a human but are different characters to a machine. Convert quotes to straight ones and clean the spaces before text goes anywhere a program will read it.
Text that looks perfect on screen can be rejected the moment a spreadsheet, an importer or an interpreter reads it. The error messages rarely say what is actually wrong, so the real cause, a single unusual character, can take an hour to find.
Spreadsheets
- Non-breaking spaces defeat tidy-up formulas. In Excel, TRIM removes ordinary spaces but not the non-breaking space, which is character 160 (U+00A0). A formula such as SUBSTITUTE(A1,UNICHAR(160)," ") replaces it first, and UNICHAR works in Excel on both Windows and Mac.
- Look-up formulas such as VLOOKUP, XLOOKUP and MATCH fail when one cell has a trailing space or an invisible character and the other does not. Comparing LEN of both cells is a quick way to see that they differ.
- Numbers that arrive with a non-breaking space as a thousands separator, or a long dash instead of a minus sign, are usually treated as text, so sums ignore them.
- A line break inside a cell, which you enter with Alt+Enter on Windows or Ctrl+Option+Return on a Mac, can make rows look uneven and may confuse an export.
CSV files
The CSV format uses the straight double quote to wrap a field that contains a comma. A curly quote is an ordinary character, so it does nothing of the kind. A field written as a curly-quoted phrase with a comma in it is split into two columns, and every column after it moves one place to the right.
Two other traps catch people. A byte order mark at the start of a file can end up glued to the first column heading, and a file saved in the wrong encoding shows garbled characters. If an import looks wrong, check the quotes, the encoding and the first few characters of the file before anything else.
Code and configuration
- In recent versions of Python (3.10 and later), a curly quote where a straight one belongs stops the program with a syntax error that names the character, for example invalid character with the code U+201C.
- A non-breaking space in Python source gives an invalid non-printable character error that names U+00A0.
- JSON requires straight double quotes. A curly quote makes the parser reject the whole document, usually with a message about a property name that is not enclosed in double quotes.
- In a shell, a non-breaking space inside a command is treated as part of a word, which often produces command not found. A command-line flag copied from a web page with a long dash instead of two hyphens is another classic.
- YAML forbids tabs for indentation, and a non-breaking space used for indentation looks right but is not a space.
How to prevent it
Treat anything going to a machine differently from anything going to a reader. For readers, curly quotes and proper dashes are good typography. For files that a program will parse, plain ASCII punctuation is the safe choice.
In Paste & Wash, switch on Straight quotes to turn curly quotes into plain ones. That option is off by default because curly quotes are right for most prose. Leave the hidden characters and spaces options on. The Dashes menu is meant for dashes in prose, because it swaps every en and em dash for a spaced hyphen or a comma, so for command-line flags and negative numbers retype the single dash as a plain hyphen yourself. The report shows how many of each were changed.
Finding the culprit in a file
If a file already fails, ask your tools to show you the unusual characters. On Linux, running grep -nP with the pattern [^\x00-\x7F] against the file lists every line containing a character outside plain ASCII. Most code editors can highlight non-ASCII characters or ambiguous Unicode, and Visual Studio Code does this by default for many of them.
In a spreadsheet, a helper column with the formula LEN(A1) next to another with LEN(TRIM(A1)) shows which cells hide extra spaces. For invisible characters, comparing the length to the number of visible characters you expect works well.
A short checklist before you import or run
- Clean the text for hidden characters and odd spaces.
- Convert curly quotes to straight ones if a program will read the text.
- Replace long dashes in flags, formulas and negative numbers with a plain hyphen.
- Confirm the file is saved as UTF-8.
- Test with a few rows or lines first, then run the full import.
A worked example
A product list has a row for a widget whose description is large, blue, wrapped in quotes so the comma stays inside the field. Written with straight quotes, the importer reads three columns. Written with curly quotes, it reads four: the comma splits the description in two, the curly marks become part of the text, and everything after it shifts one column to the right. To the eye, the two rows are identical.
The fix is to convert the curly quotes to straight ones before the file is saved. In Paste & Wash that is the Straight quotes option, which is off by default because curly quotes are the right choice for prose that people will read.
Try the cleaner to see what is hiding in your own text.