# How to make a comma-separated list with quotes for a SQL query in Excel

Join the values with ',' between them and add the outer quotes: ="'"&TEXTJOIN("','",TRUE,A2:A6)&"'".

**Excel:** `="'"&TEXTJOIN("','",TRUE,A2:A6)&"'"` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Customer ID |  | SQL list |
| C-1042 |  |  |
| C-1057 |  |  |
| C-2290 |  |  |
| C-1501 |  |  |
| C-7731 |  |  |

Formula in C2: `="'"&TEXTJOIN("','",TRUE,A2:A6)&"'"`

Result: 'C-1042','C-1057','C-2290','C-1501','C-7731'

## 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.

## Other ways

- `=TEXTJOIN(",",TRUE,"'"&A2:A6&"'")`: 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)&"')"`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/make-a-quoted-comma-list-for-sql