How to Clean Text Before Pasting It Into Excel, Sheets or a Database

To clean text for a spreadsheet or a database, remove the characters that make two values look identical while being different: spaces at the start or end of a value, non-breaking spaces, zero-width characters, stray tabs and line breaks inside a value, and the wrong kind of quotation mark. Do it before you paste or import. Afterwards the damage is spread across cells and rows, and most of it is invisible.

For a list with one value per line, the Text Cleaner does most of this in a single pass. For data with several columns it still helps, within limits.

What goes wrong, character by character

A reader skips over an extra space. A lookup, a join, a sort or a duplicate check compares character by character, so one unseen character is enough to make a match fail.

Leading and trailing spaces

Leeds and Leeds· (the · marks a space) are two different strings. RFC 4180, the reference description of CSV, says so directly: spaces are part of a field and should not be ignored. An exact-match lookup for one will not find the other, a duplicate check keeps both, and a value with a leading space sorts out of place.

Non-breaking spaces, and what TRIM and CLEAN miss

Excel’s TRIM function was designed for the ordinary space character, code 32. Microsoft’s documentation states that it does not remove the non-breaking space, code 160, which is common in text copied from web pages.

CLEAN has a similar limit. It removes the non-printing characters with codes 0 to 31, which include the tab and the line break. A non-breaking space is outside that range, and so is a zero-width space.

The usual repair is =TRIM(SUBSTITUTE(A2,CHAR(160)," ")), which swaps each non-breaking space for an ordinary one and then trims. CHAR looks its number up in the operating system’s character set, ANSI on Windows and the Macintosh set on a Mac, so UNICHAR(160), which takes the Unicode value, is the less ambiguous choice where it is available. The non-breaking space explained covers where the character comes from.

Tabs and line breaks inside a value

When plain text is pasted into a spreadsheet, a tab normally moves to the next cell and a line break starts the next row. An address typed over two lines becomes two rows, and a stray tab pushes everything after it one column to the right.

Under RFC 4180, a field that contains a line break, a comma or a double quote should be enclosed in double quotes, and a line break inside those quotes belongs to the value. A tool that works line by line cannot see that distinction, and that includes the Text Cleaner.

Line endings

RFC 4180 separates records with CRLF and notes that some implementations use other values, so files with plain LF endings turn up too. A mismatch has a classic symptom: a CRLF file read by a program that splits on LF alone leaves a carriage return attached to the last field of every row, and the last column never matches anything.

The Text Cleaner converts all line endings to LF and has no CRLF output. If a strict importer requires CRLF, set it when you save the file in your editor. CRLF, LF and CR compared explains the conventions.

Zero-width characters and the byte order mark

A byte order mark (U+FEFF) at the very start of a file is a signature for its encoding. A program that does not expect it can treat it as data, and then it becomes the first character of the first header: a column that appears to be called id is really [BOM]id, and a request for id finds nothing. A zero-width space (U+200B) does the same damage inside a value. Both are described in invisible Unicode characters.

In pasted text a byte order mark has no job to do. Removing one from a saved file is a separate decision, because there it can serve as the encoding signature.

Curly quotes around fields

CSV quoting uses the straight double quote, ". If the text passed through a word processor, autocorrect may have replaced those with “ and ”, which a parser treats as ordinary data. A field with a comma in it then splits in two.

Convert them only when the quotes are acting as delimiters. A curly quote inside a value is harmless, and turning it into a straight one creates a quote character inside a field, which CSV requires to be doubled and the whole field enclosed.

Numbers stored as text

A spreadsheet may not recognise 1200[NBSP] as a number, or 1[NBSP]200 where a non-breaking space separates the thousands. Microsoft’s Excel documentation notes that numbers stored as text sometimes carry extra spaces or non-printing characters picked up when data is copied or imported, and that such cells can upset calculations and sort orders. In Excel they sit at the left of the cell, where a real number sits at the right.

Trimming fixes the spaces around a number. A separator inside one has to be removed on purpose: the Text Cleaner converts it to an ordinary space but does not delete it. Use the spreadsheet’s own find and replace on that column only.

A cleanup routine with the Text Cleaner

1. Keep the original export untouched and work on a copy.
2. Where you can, clean one column at a time, with one value per line. The Text Cleaner trims lines, not cells: in a tab-separated row, spaces next to the tabs in the middle of the line stay.
3. Paste the text in and leave the preset on General Clean. That converts non-breaking spaces, trims trailing spaces, collapses repeated spaces and reduces runs of blank lines.
4. Under Whitespace, tick Trim each line to remove leading spaces as well, and Remove empty lines so there are no blank rows.
5. Under Characters, tick Remove zero-width spaces and Remove byte order marks.
6. Under Typography, tick Smart quotes → straight quotes only if quotes delimit your fields.
7. Under Lists, tick Remove duplicate lines if every line should be unique, and choose how lines are compared. They are compared after the whitespace cleanup, so Leeds and Leeds· count as one. Removing duplicate lines explains the three comparison modes.
8. Press Clean Text. Read the summary of changes, then compare the line counts under the two boxes. The cleaned count should equal the number of records you expect.
9. Paste into the sheet and test it: sort the column, and run one lookup that you know should succeed.

What to leave alone

• Line Breaks. Stay on Keep line breaks unchanged. Each line break separates two records, and joining lines merges them.
• Tabs → spaces. Leave it off for tab-separated data. The tabs are the column separators.
• Trim each line, when tab-separated rows have empty cells at the edges. It removes leading and trailing tabs on every line. A leading tab is an empty first cell, so losing it shifts that row one column to the left. General Clean is safer here, because Trim entire text keeps a tab at the very start of the text. Its Trim line endings does still drop tabs at the end of a row, and Trim entire text drops them at the end of the last row, so rows that end in empty cells come out shorter. Untick both if every row must keep the same number of fields.
• Collapse multiple spaces, for fixed-width data. If columns are aligned with runs of spaces, collapsing them destroys the alignment.
• Remove control characters. It turns vertical tabs and form feeds into line breaks, which can add rows. Compare the line counts if you use it.
• CSV with multi-line quoted fields. Do not run line-based options on it. Open it in the spreadsheet first and clean the columns there.

Before and after: a small list

A list of towns collected from three places. Here · marks a space, [NBSP] a non-breaking space and [ZWSP] a zero-width space.

Before

··Leeds·
Bristol[NBSP]
leeds

York[ZWSP]
Bristol
Bath··

After

Leeds
Bristol
York
Bath

The settings were General Clean with Trim each line, Remove empty lines, Remove zero-width spaces, and Remove duplicate lines set to Ignore case. Seven lines became four, and the first occurrence of each town was kept in its original position.

Spreadsheet-side helpers

TRIM, CLEAN and SUBSTITUTE, described above, are useful after the paste, as a formula in a spare column whose results you paste back as values. Those descriptions come from Microsoft’s documentation for Excel. In another spreadsheet, test the equivalents on your own data before assuming identical behaviour.

To split one column into several at a delimiter, Excel has Data > Text to Columns and Google Sheets has Data > Split text to columns. Sheets also offers Data > Data cleanup > Trim whitespace. Splitting is not cleaning, so check the pieces for leftover spaces.

A note on databases

An import generally stores what it is given. A value with a leading space becomes a key that nothing else matches, and two spellings of one value become two values. Trailing spaces are less predictable, because the rule can depend on the column type. In PostgreSQL, for example, trailing spaces are ignored when two fixed-length character values are compared, and are significant in varchar and text values. Clean before the import, while the data is still text you can read.

One thing cleaning is not: it has nothing to do with query safety, which is the job of your database driver’s parameterised queries.