How to clean up a messy list: duplicates, spaces and sorting
Lists collected from different places are rarely tidy. A list of email addresses merged from two spreadsheets, keywords from several reports, or product codes pasted from a PDF will have duplicates, stray spaces, inconsistent capitals and blank lines. Cleaning them in the right order gives a reliable result in a couple of minutes.
Step 1: get one item per line
Most list tools, including the ones below, expect one item per line. If one item is broken across several lines, as often happens with text copied from a PDF, join the lines first with Remove line breaks. If the list arrived on a single line separated by commas, a spreadsheet can split it: paste it into a cell and use Data › Text to Columns in Excel or Data › Split text to columns in Google Sheets, then copy the column back out.
Step 2: remove invisible spaces
Spaces at the start or end of a line are invisible but make “[email protected]” and “[email protected] ” different values. Run the list through the Whitespace remover with “Trim spaces” and “Remove blank lines” on. This also converts the non-breaking spaces that come from web pages into normal spaces.
Step 3: decide whether case matters
For email addresses, keywords and tags, “Toronto” and “toronto” should count as the same item. Either convert everything with the lowercase converter, or keep the original capitals and turn on “Ignore upper/lower case” in the next step. For names and titles, keep the capitals, because you’ll want “McDonald” to stay as it is.
Step 4: remove duplicates
Paste the list into the Duplicate line remover. It keeps the first copy of each line and removes the rest, without changing the order. That matters when order means something, such as sign-up order or priority.
Step 5: sort (if you need to)
Finally, sort alphabetically if the order doesn’t matter. Natural sorting puts “Item 2” before “Item 10”, and you can reverse the order or shuffle it, for example to pick names at random.
A worked example
Starting list:
Calgary toronto Ottawa Toronto calgary
After trimming spaces and removing blank lines, removing duplicates with “Ignore upper/lower case” on, and sorting A to Z:
Calgary Ottawa toronto
Notice that the first version of each duplicate was kept (“Calgary” and “toronto”). If you want consistent capitals as well, run the result through Capitalized Case.
Doing the same in Excel or Google Sheets
- Trim spaces:
=TRIM(A2)removes leading, trailing and repeated spaces (but not non-breaking spaces; in Excel, wrap it as=TRIM(SUBSTITUTE(A2,CHAR(160)," "))). - Lowercase:
=LOWER(A2). - Remove duplicates: in Excel, Data › Remove Duplicates; in Google Sheets, Data › Data cleanup › Remove duplicates. Newer versions of both also have a
=UNIQUE(A2:A500)function. - Sort: Data › Sort, or
=SORT(A2:A500).
Spreadsheets are the better choice when each row has several columns that must stay together. For a single column of items, pasting it into a text tool is usually faster.
Before you import a cleaned list
- Check the count: a large drop after removing duplicates is normal for merged lists, but a drop to almost nothing suggests a formatting problem.
- Spot-check a few items at the top and bottom.
- For email lists, only send to people who agreed to hear from you. Cleaning a list doesn’t change the consent rules, such as CAN-SPAM in the US and CASL in Canada.