Turning a List into an SQL IN Clause, JSON Array, Markdown or HTML List

To put a list of values written one per line in an Excel column or a text editor into code or a document, you need to wrap each value in quotes and join them with commas. With hundreds of values, doing it by hand is hard. The basic principle is a combination of two operations: Add text to the start and end of lines adds characters to the start and end of each line, and Remove line breaks (join into one line) joins the lines into one.

SQL IN clause

The built-in recipe trims spaces, removes blank lines and removes duplicates, then wraps each line in single quotes and joins them with , .

A-1001
A-1002
 A-1003

A-1001

The input above becomes the single line below.

'A-1001', 'A-1002', 'A-1003'

Paste the result between WHERE code IN ( and ). To add the parentheses in the tool as well, add Find and replace in regex mode at the end, with the search text ^(.*)$ and the replacement IN ($1). After joining, everything is on one line, so the whole thing is wrapped once.

When values contain single quotes

If a value contains a single quote, as in O'Brien, it becomes 'O'Brien' and causes an SQL syntax error. A single quote inside an SQL string must be written twice, so before adding the quotes, insert a find and replace step with the search text ' and the replacement ''. If the order is reversed, the outer quotes you just added are doubled too.

'O''Brien', 'D''Angelo'

For a list of numbers, joining without quotes is correct. Switch off the Add text to the start and end of lines step in the recipe.

This method is for quickly building a list. When a program puts user input into SQL, do not concatenate strings; use your database library's parameter binding.

JSON array

1. If values contain backslashes, set find and replace to plain mode, turn off 'Interpret \n \t as line break and tab', and replace \ with \\.
2. In the same way, replace " with \". Handling backslashes first keeps the backslashes you just added from being doubled again.
3. Use Add text to the start and end of lines with the prefix " and the suffix ".
4. Apply Remove line breaks with 'Custom' and the separator , .
5. Add [ and ] around the result. You can also use the regex method above with ^(.*)$ → [$1].

["Seoul", "Busan", "Daegu"]

Markdown list

For a bulleted list, enter - in the 'Prefix' of Add text to the start and end of lines. If you need a numbered list, use Number lines with the format {n}. . If the source already has numbers or bullets, add Remove line numbers and bullets in an earlier step to remove them first.

- Seoul
- Busan
- Daegu

HTML list

1. If values contain & or <, use find and replace to change & to & first, then < to <. If the order is reversed, the & in < is replaced again.
2. Use Add text to the start and end of lines with the prefix <li> and the suffix </li>.
3. Put the result between <ul> and </ul>.

<li>Seoul</li>
<li>Busan</li>
<li>Daegu</li>

Building a regex alternation

To combine a keyword list into a single regex, enter | as the separator in Remove line breaks to get something like apple|pear|grape. If values contain regex symbols such as . ( +, first set find and replace to regex mode with the search text [.*+?^${}()|[\]\\] and the replacement \$& to put a backslash in front of each symbol.

a\.b|\(c\)|1\+1

Arrays in other languages

Python lists and JavaScript arrays also wrap strings in double quotes and separate them with commas, just like JSON arrays, so you can follow the JSON steps above as they are. If a single line is too long to read, you can instead enter ", in the 'Suffix' of Add text to the start and end of lines and skip Remove line breaks, leaving one value per line. In that case, delete the comma after the last value yourself. JSON does not allow a trailing comma.

A single tab-separated row

If you want to paste a vertical list horizontally into one row of a spreadsheet, enter \t as the separator in Remove line breaks. When you paste the tab-joined result into a single cell, the values spread into the cells to the right, one per cell.

Caution

• Trim spaces before adding characters, so that spaces do not end up inside the quotes as in ' A-1003'.
• Add text to the start and end of lines has 'Skip blank lines' on by default, but removing blank lines before Remove line breaks makes the result easier to check.
• If you name the pipeline you built and save it as a recipe, you can repeat the same conversion right away next time.

Checklist

• Keep the order: trim spaces → remove blank lines → (remove duplicates) → escape → add prefix/suffix → join.
• Escape before adding the quotes.
• Join numeric values without quotes.

Last updated: 2026-09-23