Column to a comma separated list

Turning a column into a comma separated list

You have a column of IDs in a spreadsheet. You need them in a query, a config file, or a script. Copying the column gives you one value per line, and what you need is one line with values separated by commas — quoted, escaped, and wrapped in parentheses if it is going into SQL.

It is worth naming the confusion directly: Excel's Text to Columns does the opposite of this. That feature splits one cell into several. This page joins a column into one line, which is the direction that has no button anywhere and the reason people end up doing it by hand.

It is a thirty-second job done by hand for ten rows and a genuinely annoying one for four hundred. Doing it with find-and-replace works until a value contains an apostrophe, at which point the query breaks in a way that is hard to spot.

The four output formats

Comma gives a plain separated list. Right for pasting into a form field that accepts multiple values, or a spreadsheet formula.

Quoted wraps each value in double quotes and escapes any quotes and backslashes inside. Right for JavaScript arrays written by hand when you already have the brackets or need a comma-separated list of string literals.

JSON produces a properly formatted array. Every character that needs escaping is escaped by the JSON serializer itself rather than by string manipulation, so the output is valid by construction.

SQL IN produces a parenthesised list of single-quoted values ready to drop after WHERE column IN. Apostrophes are doubled, which is the SQL escape — so O'Brien becomes 'O''Brien' rather than breaking the statement.

A word about values that start with = + - or @

If a value begins with =, +, - or @, a spreadsheet reads it as a formula rather than as text. The summary line under the output counts those values and says so, and that is all it does — the output is left exactly as you wrote it.

That is deliberate. This tool hands you a string you can read in full before you use it, and the Comma format is recommended above for pasting into a formula, where a value like -5 or =A1 is very often the whole point. Rewriting it for you would break the case the format exists for. The tools that download a .csv file are a different situation — nobody reads those bytes before a spreadsheet opens them — and the CSV viewer neutralizes formula-leading cells in its CSV download and states how many it changed. Here the count is the whole answer, because you are the one placing the result.

A word about the SQL output

Escaping quotes correctly makes the output syntactically safe, and that is all it does. Pasting values from an untrusted source into a query is still a bad idea, and the right answer for anything running repeatedly is a parameterized query rather than a literal list.

This format exists for the ad-hoc case: you are in a database client, you have four hundred order numbers, and you want to look at those rows once. That is a legitimate and extremely common thing to do.

Blank rows and whitespace

Blank lines are dropped. A copy-paste out of a spreadsheet almost always brings a trailing newline, and turning that into an empty string in the middle of a SQL clause produces a query that runs and quietly returns the wrong rows.

Each value is trimmed of surrounding whitespace for the same reason: a leading space in an ID will not match anything, and it is invisible in the output.

One caveat if the column is phone numbers rather than IDs. Spreadsheets store them in whatever shape they were typed, so the same list arrives part formatted and part bare digits, and joining that produces a line nobody can dial or import. Pulling the numbers out first normalizes the shape and drops the rows that were order references all along, and the result comes back here as a clean column.