Comma Separator

How to use the comma separator

1. Choose the direction under Convert: Column → list joins lines into one list, List → column splits a list into lines.
2. Paste your items into Your items, or click Example to load five order numbers. Open file reads .txt, .csv and similar plain-text files.
3. Pick a Separator, and for Column → list, choose Quotes and Wrap if the list goes into code or SQL.
4. Leave Trim spaces and Skip empty lines on unless you need the spaces or blanks. Tick Remove duplicates to keep only the first copy of each item.
5. The result updates as you type, with the number of items next to it. Click Copy, Download .txt, or Use as input to keep working on it in another tool.

What each option does

The options are applied in that order: trim, skip empty lines, remove duplicates, then add quotes, separators and brackets. Duplicates are therefore compared after trimming, and an item that is only spaces counts as empty when trimming is on. Quotes, Wrap, Remove duplicates and Skip empty lines only affect Column → list.

A line is ended by any kind of line break (Windows, Mac or Unix), so a column copied from a spreadsheet on any system splits correctly. Accented letters and emoji pass through unchanged: café, naïve and 😀 with double quotes give "café", "naïve", "😀".

Quotes, escaping and SQL IN lists

Text values in SQL need quotes; numbers do not. The quote option also escapes a quote that is already in an item, so the list stays valid:

A doubled single quote is the SQL way to write an apostrophe inside a string. A backslash before a double quote is how JSON, JavaScript and Python write it. CSV files use a different rule (a doubled ""), so for a CSV line with quotes in the data, check the result before using it.

For a Python list, use double quotes: Python does not read '' inside single quotes as an apostrophe.

Turning a list back into a column

In List → column mode the tool:

• removes one opening ( or [ at the start and one closing ) or ] at the end of the whole text;
• splits at the chosen separator and always at line breaks, so a list that runs over several lines (a, b on one line, c, d on the next) gives four items. For Comma and space and Comma it splits at every comma, with or without a space after it; Semicolon and Pipe likewise split at every ; or |, with or without spaces around it. New line splits only at line breaks;
• keeps an item together when it starts with a double quote, even if it contains the separator: "Smith, John", "Doe, Jane" gives Smith, John and Doe, Jane. A single quote does the same when a matching closing quote ends the item, so 'Smith, John', 'Doe, Jane' (the tool’s own Single ‘a’ output) also gives Smith, John and Doe, Jane;
• trims spaces around each item (if Trim spaces is on) and removes a matching pair of " or ' around it, undoing the escaping Column → list adds: 'O''Brien' gives O'Brien, and "say \"hi\"" gives say "hi" (a CSV-style "" inside double quotes also becomes ");
• always drops empty items, so a,,b, ,c gives three lines.

Some lists need care:

• A leading apostrophe is ordinary text. If no closing quote ends the item, a single quote at the start is kept and does not protect a separator: '90s, rock gives '90s and rock. To keep such text whole, put the item in double quotes with Replace text first, or choose a separator that does not occur in the data.
• Numbers with thousands separators such as 1,000 are split at the comma unless they are in quotes ("1,000" or '1,000') or the list uses another separator.

Worked examples

The default settings with the built-in example:

1001
1002
1003
1004
1005

give 1001, 1002, 1003, 1004, 1005 (5 items).

A pasted email column with a blank line and a repeat, with Single quotes, ( … ) and Remove duplicates:

[email protected]
[email protected]

[email protected]
[email protected]

gives ('[email protected]', '[email protected]', '[email protected]') (3 items), ready for WHERE email IN ….

When people need it

• Database queries. A list of order numbers or customer IDs from a report becomes an IN (…) filter.
• Code. Test data or a list of country codes becomes a JavaScript, JSON or Python array.
• Marketing and SEO. A column of keywords or tags becomes one line for a tag or keyword field that takes comma-separated values; the reverse turns such a field back into a list you can sort or dedupe.
• Email. A column of addresses from a spreadsheet becomes one line you can paste into the To or Bcc field.
• Spreadsheets. A comma list from an email or a form becomes a column you can paste into Excel or Google Sheets, one item per row.

Doing the same in Excel

Excel 2019 and later, and Excel for Microsoft 365, can join a range with TEXTJOIN: =TEXTJOIN(", ", TRUE, A1:A100) uses a comma and a space and skips empty cells. If the result is longer than 32,767 characters, the cell limit, the formula returns #VALUE!; this tool has no such limit.

To go the other way, Excel’s Data > Text to Columns wizard (choose Delimited, then the delimiter) splits a cell into columns side by side, not rows. If you need one item per row, split the list here and paste the result into the column.

Questions