The quick answer: clean, remove repeats, sort, convert
You pasted a mailing list, a keyword list or a spreadsheet column, and the same entries appear several times, with blank rows and no order. Run it through the duplicate line remover, then sort lines, then the list converter if you need commas.
- Paste one item per line into the duplicate line remover. Switch Case sensitive off and Trim spaces on, and set empty lines to Remove all.
- Copy the result into the line sorter. Choose Alphabetical for words and Natural when lines contain numbers.
- Paste the sorted lines into the list converter and join them with a comma and space, or a new line to go the other way.
- Check each summary's counts before you use the list.
If the end result is a comma separated list, the list converter can do the whole job in one pass: with Remove duplicates, Ignore case and Order set to A to Z switched on, it trims, drops empty items, removes duplicates and sorts, in that order, before it joins.
One small list, worked through with checked counts
Here are ten pasted lines, the kind a keyword export produces. Two are empty, and several repeat in other capitals or with a stray space.
| Line | What is in it | Why it matters |
|---|---|---|
| 1 | red running shoes | First copy |
| 2 | Red Running Shoes | Same words, other capitals |
| 3 | trail shoes, with a space in front | Not identical to line 5 |
| 4 | running shoes | First copy |
| 5 | trail shoes | Plain text |
| 6 | (empty) | A blank row |
| 7 | running shoes | An exact repeat of line 4 |
| 8 | cheap running shoes, with a space behind | Unique, with an invisible space |
| 9 | (empty) | A blank row |
| 10 | Trail Shoes | Same words, other capitals |
What the remover finds depends on its two matching switches, with empty lines removed:
| Case sensitive | Trim spaces | Lines left | Repeats removed |
|---|---|---|---|
| On (the default) | Off (the default) | 7 | 1 (line 7) |
| On | On | 6 | 2 |
| Off | Off | 5 | 3 |
| Off | On | 4 | 4 |
With the defaults only line 7 is a repeat, because matching is exact. Each loosened switch catches more, and 8 lines minus 4 repeats leaves 4. The chain then runs like this:
| Step | Lines | The list |
|---|---|---|
| Pasted | 10 | As above |
| Remove empty lines | 8 | Two blank rows gone |
| Trim, ignore case, remove repeats | 4 | red running shoes, trail shoes, running shoes, cheap running shoes |
| Sort A to Z | 4 | cheap running shoes, red running shoes, running shoes, trail shoes |
| Join with commas | 1 line, 4 items | cheap running shoes, red running shoes, running shoes, trail shoes |
The copy that stays is shown as it was typed, so red running shoes survives and Red Running Shoes goes. Keep the last copy instead and you get Red Running Shoes, running shoes, cheap running shoes and Trail Shoes. With the loose settings, the Most repeated lines panel reports trail shoes three times and the two other repeated lines twice each.
Removing duplicates: the choices that matter
- Exact or loose. Leave matching exact for code, file names and anything where capitals mean something. For email addresses, tags and keywords turn case sensitivity off and trimming on. In practice most mail providers treat the part before the @ as case insensitive.
- First or last. Take a, b, a, c, b. Keeping the first copy gives a, b, c. Keeping the last gives a, c, b. The first is usual. Keep the last when later lines replace earlier ones, as in a log of settings changes.
- Invisible differences. Trimming covers spaces, tabs and non breaking spaces. A zero width space is not trimmed, so two identical looking lines can stay apart. Run the list through the text cleaner with Remove invisible characters, then try again.
To find rather than remove, choose Show only repeated lines or Show only lines that appear once. With two lists pasted one after the other, and no repeats inside either, that shows what is in both and what is in only one. For two versions of a text, see compare two texts.
Sorting: A to Z, and the trap where 10 comes before 2
Alphabetical order compares one character at a time, like a dictionary, so it puts 10 before 2 because 1 comes before 2. Three orders look alike and give different answers once numbers are involved. Here are item 10, item 2 and item 1 in each:
| Order | Result | Use it for |
|---|---|---|
| Alphabetical | item 1, item 10, item 2 | Words and names |
| Natural | item 1, item 2, item 10 | File names, versions, numbered labels |
| By number | item 1, item 2, item 10 | Lines whose first number matters, such as prices. Lines with no number go last |
Versions show the difference between the last two. For 1.10, 1.9, 1.2 and 1.11, Natural gives 1.2, 1.9, 1.10, 1.11. By number reads 1.10 as the decimal 1.1 and gives 1.10, 1.11, 1.2, 1.9. Use Natural for version numbers.
- Capitals and accents. Capitals are ignored by default, so Banana and apple sort by their letters. With Case sensitive on and Follow language rules off, plain Unicode order puts every plain capital A to Z before every small letter and accented letters after z: Banana, Zebra, apple, zebra, éclair.
- Names. The tool sorts the line as typed. Write each name as Last, First to file it under the surname.
- Numbered lists. With Ignore bullets and list numbers on, 3. pear, 1. zebra and 2. apple become 2. apple, 3. pear, 1. zebra. The numbers stay on their lines, so renumber afterwards.
For a plain A to Z list, open alphabetize a list: case, bullets, empty lines and trimming are already set for lists, and a switch files The Great Gatsby under G.
Lines to a comma separated list, and back
A column copied from a spreadsheet has a line break between cells. Paste it into the list converter and the separator is detected. Join with comma and space for a sentence or form field, with no spaces for a web address or an import, or with a comma at the end of each line for a code array. The add commas to list page opens with those layouts.
To go back, paste the comma separated list and set Join items with to New line. Two details cause most trouble:
- Quotes. Wrap each item in quotes for SQL or JSON. The tool does not know what an item means, so numbers get quoted too. Set quotes to None for a list of numbers.
- Commas inside items. Smith, Anna and Lee, Bo joined with commas reads as four items. Put quotes around each item, or join with a semicolon, tab or pipe. When you read a list back, items in quotes stay whole.
Three ways it goes wrong
- Repeats survive. The cause is capitals, a trailing space or an invisible character. Fix: case sensitivity off, trimming on, and the text cleaner for the rest.
- The order looks wrong. 10 before 2, a numbered list out of sequence, or capitals first. Fix: choose Natural, renumber after sorting, or switch Follow language rules back on.
- Items split or merge. A comma inside an item cuts it in two. Fix: quote the items, or pick a separator the items never contain.
Matching is on whole lines, so apple pie and apple stay apart, and misspellings are not detected. Lists of tens of thousands of lines are fine in a browser, but a huge file suits the command line better. For empty lines alone, the remove empty lines page opens with that one fix ready.
If capitals are the problem, change the text case first. If your list lives in a spreadsheet file, see convert CSV to Excel and JSON. A column of SKUs or asset tags headed for labels needs its repeats gone too, so you do not print the same barcode twice, and make a barcode that scans covers the next step.