How to convert a column to a comma separated list
You have a column of values in Excel, Google Sheets or a SQL result. You need them in one line, with commas between them. This page does that. Paste the column into the left box and the list appears on the right. There is no button to press and nothing to install.
- Copy the cells. Select the column in your sheet and press Ctrl+C (Cmd+C on a Mac).
- Click the left box and paste. You can also tap Paste.
- Pick a separator and, if you need them, quotes.
- Press Copy, or hit Ctrl+Enter (Cmd+Enter on a Mac), and paste the list where you need it.
Worked example: build a SQL IN list
Say you have four customer IDs in a sheet and you want to look them up in a database. Paste these four rows:
1001
1002
1002
1004Click SQL IN list, then tick Remove duplicates. You get this:
(1001, 1002, 1004)Paste it after WHERE id IN and run the query. For text values such as names or codes, the quotes stay on, so you get ('AB-1', 'AB-2'). A name like O'Brien becomes 'O''Brien', which is the correct way to escape it in SQL.
One warning: Oracle allows at most 1,000 items inside one IN list. If your list is longer, split it into several lists.
Common settings
| You want | Separator | Quotes | Result |
|---|---|---|---|
| Plain list | Comma | None | a,b,c |
| Readable list | Comma and space | None | a, b, c |
| SQL IN list | Comma and space | Single, wrap in ( ) | ('a', 'b', 'c') |
| JSON array | Comma and space | Double (JSON), wrap in [ ] | ["a", "b", "c"] |
| CSV row | Comma | Double | "a","b","c" |
| Pipe list | Pipe | None | a|b|c |
Column to comma in Excel and Google Sheets
Both programs have a built-in way to join cells. In Excel and Sheets, =TEXTJOIN(",", TRUE, A1:A100) joins the range with commas and skips empty cells. It works well for a fixed range, but it cannot add quotes, drop duplicates or sort without extra formulas. This tool does all of that in one step, and you do not need to change your file.
The tool also accepts text copied from a database client, a CSV file, an email or a web page. It handles Windows, Mac and Linux line breaks.
What each option does
- Trim spaces removes spaces and tabs around each value. Cells copied from a sheet often carry them.
- Remove blank lines drops empty rows, so you never get
a,,b. - Remove duplicates keeps the first copy of each value. Tick Ignore case for duplicates to treat Ab and ab as the same.
- Sort orders values from A to Z or Z to A. Numbers inside text sort the natural way, so item2 comes before item10. The number options sort by value.
- Letter case changes values to upper case, lower case or Title Case before duplicates are checked.
- Before and after each item adds text around every value, such as
<li>and</li>. The same pair of fields for the whole list adds text at the start and end. - Use only the first column keeps the first column when you copy several columns from Excel.
- Do not quote numbers leaves 42 and 3.5 bare but quotes 007, so leading zeros survive.
Tips for clean results
If your values contain commas, such as "Smith, John", turn on double quotes so the next program reads each name as one value. Check the count under the output. It shows how many lines went in and how many items came out, so you can see that nothing was lost.
Convert a list back to a column
Switch to List to column to go the other way. Paste 'a', 'b', 'c' and you get three rows. The tool removes the quotes and brackets, and it keeps a comma that sits inside a quoted value. Leave Split on at Auto-detect to split on commas, semicolons, pipes or tabs.
When a comma separated list helps
- Database queries. Fill an IN clause with IDs from a report instead of typing them one by one.
- Email and marketing tools. Many forms ask for tags, addresses or keywords in one comma separated field.
- Code. Turn a column of names into an array for Python, JavaScript or JSON.
- Search and filters. Tools that filter by a list of SKUs or order numbers usually want one line.
- Spreadsheet formulas. Paste a list of values into a function that expects them inline.
If a tool rejects your list, check three things: stray spaces, a trailing comma, and quotes of the wrong kind. Trim spaces and Remove blank lines are on by default, which fixes the first two. The Quotes menu fixes the third.
Large lists and privacy
The tool works on lists of 100,000 rows in well under a second. Everything runs in your browser, and your text is never uploaded.