Cleaning messy lists of text

Normalise line endings, trim spaces, drop blank lines, then remove duplicates, then sort, then add any wrapping. Doing it in that order avoids most of the surprises.

What's hiding in a pasted list

Text that looks tidy on screen can carry characters you can't see. The usual suspects are spaces at the start or end of a line, tabs where you expected spaces, and non-breaking spaces copied from web pages and word processors, which look identical to ordinary spaces but aren't the same character. Lists exported from Windows programs often end each line with two invisible characters, a carriage return and a line feed, where most other systems use just the line feed. And a list pasted from a PDF may break lines in the middle of an entry, wherever the original page ran out of width.

None of this matters when you're reading the list. It matters as soon as a computer compares lines, because to a computer "Tūī Road" with a trailing space and "Tūī Road" without one are different.

Why some duplicates survive

Here's a short address list (dots stand in for spaces so you can see them):

A strict duplicate check, matching letter for letter, finds no repeats and keeps all 5 lines, because each one differs from its near-twin by a capital letter or a space. Trim the spaces and ignore capitals, and only 3 lines remain. Which is right depends on the job. For street names, "Kererū Street" and "kererū street" are clearly the same place. For case-sensitive codes or passwords, they aren't. Decide what counts as the same before you remove anything, and keep the original until you've checked the result.

Why sorting gives 1, 10, 2

Ordinary alphabetical sorting compares text one character at a time. "1" comes before "2", so "photo 10" lands before "photo 2", just as "ba" comes before "c". The result looks wrong to a person:

Natural sorting reads runs of digits as whole numbers, so 2 comes before 10. It's what you want for file names, invoice numbers and street addresses. Another fix, common in spreadsheets, is padding numbers with leading zeros (002, 010), which makes alphabetical and number order agree. Accents can cause a similar surprise; a good sort keeps "Kōwhai" with the other words starting with K rather than pushing it to the end.

Line endings and blank lines

The carriage-return characters from Windows files are the reason a list can look fine yet refuse to match, or show odd symbols when opened in another program. Converting all line endings to one style before anything else fixes this. Blank lines come in two kinds: truly empty ones, and ones holding a space or a tab, which look empty but aren't. If "remove blank lines" leaves gaps behind, those gaps contain whitespace; removing whitespace-only lines as well clears them.

The order matters

The steps affect each other, so the sequence changes the result. Trim before removing duplicates, or entries that differ only by a trailing space survive. Remove blank lines before sorting, or a block of empties collects at the top. Add quotes and commas last, so they aren't counted as part of an entry when comparing or sorting. The text cleaner always works in this order, which is why it can do several jobs in one pass.

From a list to a query or a CSV row

A common job is turning a column of IDs into something a database query can use. Starting from IDs copied out of a spreadsheet, with Windows line endings, a blank line and a repeat, trimming, removing the blank and the duplicate, wrapping each line in single quotes and joining with commas gives:

That's ready to drop inside the brackets of an IN ( ) clause. Going the other way, a comma-separated row can be split into one entry per line: "Aroha, Ben ,Mere,, Sione" becomes 4 clean lines once the spaces are trimmed and the empty entry between the two commas is dropped. If an entry might itself contain a comma, such as "Smith, J", a plain split will break it in two; for anything like that, use a spreadsheet's import tool, which understands quoted fields.

Good habits

• Keep a copy of the original before cleaning, so you can start again.
• Count the lines before and after, and make sure the difference makes sense.
• Check a few entries by eye, especially ones with accents or macrons.
• Don't paste passwords or other private data into online tools that send text to a server; the tools here work entirely in your browser.

For single jobs there are quicker pages: remove duplicate lines, remove blank lines, sort lines and add text to each line. To check length limits afterwards, use the word and character counter.

Last reviewed: 3 October 2026