Turn a Column Into a Comma-Separated List (With Quotes for SQL)

The short answer

Paste your values, one per line, into Find and Replace and tick Use regex. For a plain comma-separated list, find \n and replace with , . For a quoted list for SQL, first find (.+) and replace with '$1', copy the result back into the input, then find \n and replace with , . Wrap the result in IN ( ... ) and your query is ready.

Why you need this

You copy a column of IDs, emails or product codes from a spreadsheet and need them as one line:

• 101, 102, 103 for an API parameter or a form that accepts comma-separated values,
• 'apple', 'banana', 'cherry' for an SQL WHERE name IN (...) clause,
• "apple", "banana", "cherry" for a JSON array or a programming language list.

Typing commas and quotes by hand is slow and easy to get wrong after the twentieth item.

Method 1: one line, separated by commas

Open Find and Replace and paste your list into Input text, one item per line.

Tick Use regex and make sure Replace all is ticked.

In Find, type \n (backslash and the letter n). This matches each line break.

In Replace with, type , (comma and space), or just , if you want no spaces.

Copy the output.

Example:

Input	Find	Replace with	Output
apple / banana / cherry (one per line)	\n	, (comma space)	apple, banana, cherry
101 / 102 / 103 (one per line)	\n	, (comma)	101,102,103

The match counter under the options shows how many line breaks were found. It should be one less than the number of items.

Method 2: quoted list for SQL IN

This takes two passes.

Pass 1: add quotes around each line

Paste the list and tick Use regex.

Find: (.+)

Replace with: '$1'

Each line is now wrapped in single quotes: 'apple', 'banana', 'cherry' on separate lines.

How it works: . matches any character except a line break, so (.+) matches one whole line at a time, and $1 puts that matched line back between the quotes.

Pass 2: join the lines

Copy the output and paste it into the input box, replacing the old text.

Find: \n

Replace with: ,

The output is 'apple', 'banana', 'cherry'.

Now wrap it: SELECT * FROM fruit WHERE name IN ('apple', 'banana', 'cherry');

For double quotes, as in JSON, use "$1" in pass 1 instead.

Doing it in one pass with a trailing comma

If you prefer a single step, find (.+) and replace with '$1', (note the comma after the closing quote). Each line becomes 'apple', and so on. Then join the lines with Method 1. The catch is a trailing comma after the last item, which most SQL databases reject. Delete it by hand before running the query.

Common problems and fixes

There is a comma at the very end

Your list ended with an empty line, so there was one more line break than items. Delete the last empty line from the input, or remove the final comma from the output.

Empty lines become empty items

Blank lines in the middle produce apple, , banana or '' entries. Remove them before joining: with Use regex on, find \n(?=\n) and leave Replace with empty. That deletes every line break that is immediately followed by another one, so runs of blank lines collapse and each value sits on its own line again.

Alternatively, run the list through Remove Duplicate Lines first. With its default settings it drops empty lines, trims spaces from each line and removes repeated values, which is often exactly what you want before building an IN list.

Spaces before or after values

Values copied from a spreadsheet can carry stray spaces, giving 'apple ' in SQL, which will not match 'apple'. With regex on:

• To remove spaces and tabs at the end of each line, find [ \t]+(?=\n|$) and leave the replacement empty.
• To remove them at the start of each line, find (^|\n)[ \t]+ and replace with $1, which puts the line break back.

Run these before adding quotes.

A value contains an apostrophe

Names like O'Brien break a single-quoted SQL string: 'O'Brien'. In standard SQL you escape an apostrophe by doubling it. Before pass 1, turn off Use regex, find ' and replace with ''. Then run the two passes. O'Brien becomes 'O''Brien', which SQL reads correctly.

Typing \n in the replace box gives backslash-n

That is expected. Regex patterns such as \n work in the Find box, but the Replace with box inserts exactly what you type, apart from $1-style references to captured groups. The tool cannot turn commas back into line breaks for that reason. To split a comma-separated list back into lines, use a spreadsheet's split-text-to-columns feature, or paste the list into a code editor that supports line breaks in replacements.

Check the result before using it

• Count the separators. A list of 25 values should have 24 commas. The match counter in pass 2 tells you how many line breaks were replaced.
• Look at both ends. Make sure the first value has an opening quote and the last value a closing quote, with no comma after it.
• Test on a small query first, such as a SELECT with a LIMIT, before using the list in an UPDATE or DELETE.

Large lists in SQL

Very long IN (...) lists work but can become slow or hit limits on query length in some databases. For thousands of values, loading them into a temporary table and joining is usually better. For a few dozen or a few hundred, the list method is fine.

Related tasks

• Sort the list first so it is easier to check: Sort Text Lines.
• Remove line breaks from pasted paragraphs rather than lists: How to Remove Line Breaks From Text.
• More regex replacement patterns, including capture groups: Find and Replace With Regex: Capture Groups, Whole Words, Examples.