Comma Separated List to Column

When you need a column instead of a list

Data tends to travel as a single line: a comma separated field in a CRM export, a JSON array in an API response, a semicolon separated recipient list in an email header. But spreadsheets, form fields and many scripts want each value on its own row. This tool makes that switch without opening a text editor and doing a find-and-replace, and it strips the brackets and quotes that a JSON array, Python list or SQL IN (…) clause wraps around the values.

Paste into a spreadsheet column

Convert the list, copy the result, click a cell in Excel or Google Sheets and paste. Because each item is on its own line, the spreadsheet fills one cell per row automatically.

Read an ID list out of a query

Copy the contents of an IN (…) clause, paste it here, and the parentheses and quotes are stripped so you are left with the raw IDs, ready for a lookup or a VLOOKUP.

Count and de-duplicate

The item counter shows how many values you actually have. Tick Remove duplicates and the counter shows how many were dropped (a quick sanity check on messy exports). For a column that is already one-per-line, the dedicated Remove Duplicate Lines tool adds case-insensitive matching and keep-last.

Example

A JSON array pasted whole:

["red", "orange", "yellow", "green"]

comes out as one bare value per line. The brackets and quotes are removed and the comma is detected as the separator:

red
orange
yellow
green

Options explained

• Separator: Detect automatically counts commas, semicolons, pipes, tabs and new lines and splits on the most frequent one; on a tie the earlier in that list wins (comma first). If none appears at all, the text is split on whitespace.
• Comma / Semicolon / Pipe / Tab / Any whitespace / Custom… force a separator. Any whitespace splits on runs of spaces, tabs and newlines together. Custom… uses exactly the characters you type, spaces included; leaving the custom box empty falls back to automatic detection.
• Trim spaces around items: a , b gives a and b, not a and b.
• Skip empty items turns a,,b into two items and reports one dropped; untick it to keep an empty line in the output.
• Remove surrounding quotes strips one matching pair of ', " or ` from each item; a quote on one side only is left alone. Inside a stripped item it also undoes quote escaping: a doubled quote (SQL and CSV style) or a backslash before a quote or backslash (JSON and Python style). ('O''Brien', "say \"hi\"") gives O'Brien, say "hi". Other sequences such as \n are kept as typed.
• Remove duplicates matches exact text, so a and A are different items.
• Reverse order, Sort and Letter case behave as in the column to list tool: natural-order A → Z, numeric sorts that read the first number in each item, and simple case changes.
• Surrounding [ ], ( ) or { } around the whole input are removed before splitting, so a JSON array, a Python tuple or an SQL IN (…) clause can be pasted as-is.

Edge cases and common errors

• Quoted CSV fields containing the separator are split anyway. "Smith, John", "Doe, Jane" becomes 4 items, not two. The tool does not parse CSV quoting; use a separator that is not present in the values, or export from the spreadsheet with tabs.
• Nested arrays lose only the outer brackets. [["a","b"],["c"]] gives ["a", "b"] and ["c"]. Flatten the structure first, or use the JSON Formatter to reshape it.
• Mixed separators go to the majority. a;b;c;d,e has three semicolons and one comma, so it splits on semicolons and d,e stays one item. Pick the separator manually if the data is inconsistent.
• A trailing separator such as a, b, c, produces 3 items and 1 dropped, not an error.
• Spaces are only a separator as a last resort. a b c with no other separator gives three items, but a b, c gives a b and c, because the comma wins.
• An unbalanced quote ("a, b) is kept: quote stripping needs a matching pair on both ends of an item.
• Empty input, or just [], gives an empty result and a count of 0.

Do the same job in a spreadsheet or in code

Excel and Google Sheets

Both applications can split text to columns from the Data menu. To split into rows instead, use a formula:

=TRANSPOSE(SPLIT(A1, ","))          Google Sheets
=TEXTSPLIT(A1, , ",")               Excel 365

Python

items = [s.strip().strip('"\'') for s in text.split(",") if s.strip()]
print("\n".join(items))

Command line

tr ',' '\n' < list.txt                   # Linux / macOS
(Get-Content list.txt) -split ','       # PowerShell