I have a spreadsheet with a column of over 500 unique product IDs that I need to use in a SQL "IN" clause. Manually adding commas and moving them into a single line is taking too much time. Is there a specific Excel formula or a built-in feature that can automatically join these cells into a single string separated by commas? I also need to know if I can wrap each value in single quotes for database compatibility.
3 answers
The most efficient modern solution is the TEXTJOIN function, available in Office 2019 and Microsoft 365. You can use the formula =TEXTJOIN(",", TRUE, A1:A500). The first argument is the delimiter (a comma), the second argument TRUE tells Excel to ignore empty cells, and the third is your range. If you need single quotes for a SQL query, you can use ="'"&TEXTJOIN("','", TRUE, A1:A500)&"'". This wraps the entire string and place quotes around the delimiters. This method is significantly better than the old CONCATENATE function because it handles large ranges instantly and manages the delimiters between values automatically without leaving a trailing comma at the end of your list.
Does the TEXTJOIN function have a character limit if I am trying to merge a very large column with thousands of rows into one cell?
If you don't have the latest Excel, you can copy the column, paste it into Word, and use "Find and Replace" to swap the paragraph marks (^p) with a comma.
I agree with Robert. This "low-tech" way is a lifesaver when you're working on an older version of Excel at a client site. I’ve used the Find and Replace trick many times for quick SQL updates; it’s often faster than writing a complex formula if it's just a one-time task.
Steven, that is a great question. Excel has a limit of 32,767 characters per cell. If your joined string exceeds this, TEXTJOIN will return a #VALUE! error. For datasets that large, it is better to use a simple VBA macro or Power Query. In Power Query, you can select your column, go to the "Transform" tab, and use "Group By" with a "Sum" operation on a custom text column to concatenate the values. This bypasses the single-cell character limit and is much more robust for professional data science workflows involving massive datasets.