Text Tools·4 min read

Comma Separator: Turn a Column Into a CSV List

Copy a column out of a spreadsheet and paste it somewhere that expects a list, and you get one value per line when you needed apple, banana, cherry. It happens with SQL queries that want an IN list, with tag fields that want commas, with API filters, with ad platform bulk editors. The fix is mechanical: join the lines with a separator, maybe quote each value, maybe wrap the result in brackets. A comma separator does it in one paste and also runs the other way, splitting a comma list back into a column. This guide walks through the common formats, the escaping rules that trip people up, and the spreadsheet formulas that do the same job.

Open the free Comma Separator All insights

Column to list and back again

One paste, both directions.

Paste a column into Comma Separator and the result joins every value with a comma and a space. The separator used to split your input is detected automatically: line breaks first, then tabs, then whichever of commas, semicolons or pipes appears most, then spaces. The counter above the result tells you which one it used and how many items it found, so a wrong guess is easy to spot and fix from the Split the input on menu.

Going the other way is the same tool with a different output. Paste a comma list, click the Back to a column preset, and every item lands on its own line, ready to paste into a spreadsheet column where each line becomes one cell.

Formats for SQL, JSON and CSV

Quotes and brackets, done for you.

A plain comma list is only one of the shapes people need. The presets cover the others. SQL IN wraps each value in single quotes, joins with commas and adds parentheses, so a column of IDs becomes ('A1','A2','A3') ready to follow WHERE id IN. JSON wraps each value in double quotes and the list in square brackets. CSV row quotes each value with double quotes and joins with commas, which keeps values that contain commas intact.

You can also set each part yourself: Join items with for the separator, Quote each item for the quote style, and Wrap the whole list for parentheses, brackets or braces.

SQL IN: ('red','green','blue')
JSON array: ["red","green","blue"]
CSV row: "red","green","blue"
Tag field: red, green, blue

Escaping quotes inside values

O'Brien breaks naive SQL.

The trap with quoting is a value that already contains the quote character. Wrap O'Brien in single quotes without care and the SQL string ends after the O, which breaks the query and, in code built from user input, opens the door to SQL injection. With Escape quotes inside items ticked, a single quote inside a value is doubled, so O'Brien becomes 'O''Brien', which is how standard SQL writes a literal quote. Double quotes are doubled in the same way, matching the CSV rule.

JSON uses a backslash instead of doubling, so for values that contain double quotes, check the JSON output before pasting it into code, or build the array with a JSON tool. Hand-built SQL lists are fine for one-off queries you run yourself, but application code should always use parameterized queries.

Cleaning the list while converting

Trim, dedupe, sort, chunk.

Columns copied from spreadsheets carry leftovers: empty cells, trailing spaces, repeated values. Trim spaces and Drop empty items are on by default, so a blank cell never becomes an empty item between two commas. Tick Remove duplicates to keep one copy of each value, and choose a Sort order when the list should be alphabetical.

Very long lists are easier to read and paste when they are split over several lines. Set Items per line to a number such as 10 and each row holds that many values, with the separator kept at the end of each row so the rows still form one list.

Doing it with spreadsheet formulas

TEXTJOIN and Split text.

Excel 365 and Google Sheets both have TEXTJOIN. =TEXTJOIN(", ",TRUE,A2:A100) joins a range with a comma and a space, and the TRUE skips empty cells. To quote each value, join a built range: =TEXTJOIN(",",TRUE,"'"&A2:A100&"'"), which older Excel versions need entered as an array formula. For the reverse, Google Sheets has Data, Split text to columns and Excel has Data, Text to Columns, while =SPLIT(A1,",") in Sheets and =TEXTSPLIT(A1,",") in Excel 365 split one cell with a formula.

Formulas win when the list must update as the sheet changes. For a one-time conversion, pasting the column here is quicker than writing and then deleting a formula, and it also handles the quoting, deduplication and sorting that would otherwise need extra helper columns or nested functions.

Mistakes that break the pasted list

Small characters, big errors.

These cause most failed pastes:

A trailing separator after the last item, which many SQL and JSON parsers reject. The tool never adds one when joining.
Numbers quoted as text, such as '42' in a numeric SQL column. Choose No quotes for numbers.
Commas inside values in a plain comma list, which split one value into two for whoever reads it. Quote the values, or use a semicolon or pipe.
Leading zeros lost when a list is pasted back into a spreadsheet. Format the column as text first.