Guides › How to Convert a Column to a Comma-Separated List (Excel, Sheets, SQL)
How to Convert a Column to a Comma-Separated List (Excel, Sheets, SQL)
You have a column of values — IDs, names, email addresses — and you need them as one line separated by commas, for a database query, a form field, or an email “To” box. Or the reverse: one long string that needs to become rows. Here is how.
Excel and Google Sheets: TEXTJOIN
With your values in A2:A100:
=TEXTJOIN(", ", TRUE, A2:A100)The first part is the separator, TRUE tells it to skip empty cells, and the last part is the range. It works in Excel 2019 and later, Excel 365 and Google Sheets. Use "," for a comma with no space, or ";" for the semicolon some email programs need.
Adding quotes around each item
Text values in SQL and most programming languages need quotes. In Excel 365 or Google Sheets:
=TEXTJOIN(",", TRUE, "'" & A2:A100 & "'")Older Excel versions need this entered as an array formula with Ctrl+Shift+Enter, or a helper column that adds the quotes first.
Building a SQL IN list
To find rows whose ID is in your list, the query looks like this:
SELECT * FROM customers
WHERE email IN ('ann@example.com', 'bob@example.com', 'cy@example.com');Numbers do not need quotes; text does. Remove duplicate and blank values first, because they only make the query longer. Very long lists may hit a database limit, so split them into batches if you get an error.
Without a spreadsheet
If the data is in an email, a document or a text file, paste it into the List Converter. Choose the output separator (comma, comma and space, semicolon, pipe or tab), optionally wrap each item in quotes, and remove duplicates and blank lines in the same step. Copy the result into your query or form.
The reverse: a comma-separated string to rows
- Google Sheets:
=SPLIT(A1, ",")spreads the values across columns; wrap it inTRANSPOSEto get a column. - Excel 365:
=TEXTSPLIT(A1, ","), or the older Data then Text to Columns command. - Online: set the converter’s input separator to comma and the output separator to new line. Choose “Auto” to split on commas, semicolons, tabs and line breaks all at once.
Common problems
- Stray spaces around items cause failed matches in queries. Trim whitespace as part of the conversion.
- Commas inside values (such as “Smith, John”) break a comma-separated list. Use a different separator, such as a pipe, or quote the items.
- A trailing comma at the end is a common error when building lists by hand. Joining with a function avoids it, because separators only go between items.