How to make a comma-separated list with quotes for a SQL query in Excel
Formula (Excel and Google Sheets)
="'"&TEXTJOIN("','",TRUE,A2:A6)&"'"Join the values with ',' between them and add the outer quotes: ="'"&TEXTJOIN("','",TRUE,A2:A6)&"'".
- Result on the example
- 'C-1042','C-1057','C-2290','C-1501','C-7731' in
C2 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
How it works
TEXTJOIN puts the separator between values, so making the separator ',' (quote, comma, quote) closes one value and opens the next. Only the very first and very last quotes are missing, and the two "'" pieces add them.
="'"&TEXTJOIN("','",TRUE,A2:A6)&"'"Result"'C-1042','C-1057','C-2290','C-1501','C-7731'"
A2:A65 rows × 1 col: "C-1042", "C-1057", "C-2290", "C-1501", …A range: 5 cells in one column.
TEXTJOIN("','",TRUE,A2:A6)"C-1042','C-1057','C-2290','C-1501','C-7731"TEXTJOIN: Joins text with a delimiter between items.- delimiter
"','" - ignore_empty
TRUE - text1
A2:A65 rows × 1 col: "C-1042", "C-1057", "C-2290", "C-1501", …
- delimiter
"'"&TEXTJOIN("','",TRUE,A2:A6)"'C-1042','C-1057','C-2290','C-1501','C-7731"The & operator joins as text.
"'"&TEXTJOIN("','",TRUE,A2:A6)&"'""'C-1042','C-1057','C-2290','C-1501','C-7731'"The & operator joins as text.
In Google Sheets
The same formula works in Google Sheets: ="'"&TEXTJOIN("','",TRUE,A2:A6)&"'". We ran it in Google Sheets mode on the same data and it returns 'C-1042','C-1057','C-2290','C-1501','C-7731'.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TEXTJOIN(",",TRUE,"'"&A2:A6&"'") | 'C-1042','C-1057','C-2290','C-1501','C-7731' | Wraps each ID in quotes first, then joins with commas. Same result, but a blank cell becomes '' instead of being skipped. |
| ="WHERE customer_id IN ('"&TEXTJOIN("','",TRUE,A2:A6)&"')" | WHERE customer_id IN ('C-1042','C-1057','C-2290','C-1501','C-7731')Different result | Builds the whole clause, ready to paste. Includes WHERE customer_id IN ( … ). |
Common mistakes
- A value with an apostrophe, like O'Neil, breaks the SQL. Double it first:
TEXTJOIN("','",TRUE,SUBSTITUTE(A2:A6,"'","''")). - Numeric IDs usually shouldn't be quoted. Use
=TEXTJOIN(",",TRUE,A2:A6)for those.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Make a comma-separated list with quotes for a SQL query
C2, turn the IDs in A2:A6 into one list for a SQL IN clause: each ID in single quotes, separated by commas.Questions
How do I put each value on its own line?
Use a comma and CHAR(10) as the separator: TEXTJOIN("',"&CHAR(10)&"'",TRUE,A2:A6).
Does this work in Google Sheets?
Yes. TEXTJOIN works the same there.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.