Join Cells with Comma in Excel: 4 Ways
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This guide explains how to combine text from multiple cells into one cell with commas separating each value, a common task in Excel and Google Sheets. You’ll learn the fastest formulas and tools for joining cells with a comma, whether you need a simple list or a more complex concatenation. By the end, you’ll be able to choose the right method for your data and avoid common pitfalls.
Formula
The core formula for joining cells with a comma in Excel is:
=A1 & ", " & B1 & ", " & C1
Where A1, B1, C1 are the cell references you want to join. The ampersand (&) is the concatenation operator, and the comma and space inside quotes are the delimiter. For a range of cells, you can use the TEXTJOIN function (Excel 2016 and later):
=TEXTJOIN(", ", TRUE, A1:A10)
Here, “, ” is the delimiter, TRUE tells Excel to ignore empty cells, and A1:A10 is the range. In Google Sheets, the equivalent is =TEXTJOIN(", ", TRUE, A1:A10) or =JOIN(", ", A1:A10).
Worked Example
Let’s use the default values from the calculator: Apple, Banana, Cherry in cells A1, B1, and C1.
- In cell D1, type the formula:
=A1 & ", " & B1 & ", " & C1 - Press Enter. The result will be:
Apple, Banana, Cherry
Using the TEXTJOIN function with the range A1:C1 would give the same result: =TEXTJOIN(", ", TRUE, A1:C1) returns Apple, Banana, Cherry.
When to Use Each Method
Choosing the right method depends on your Excel version and the size of your data.
- Ampersand (&): Works in all versions. Best for joining a few cells (up to 5-10) where you want full control over the separator and any extra text.
- TEXTJOIN: Excel 2016+, Excel 365, and Google Sheets. Best for joining a range of cells (e.g., a column of names) and automatically ignoring blanks. This is the modern, efficient choice.
- CONCATENATE function: Older function, still works, but TEXTJOIN is more flexible. Use
=CONCATENATE(A1, ", ", B1)for a few cells. - Flash Fill: Excel 2013+. Best for quick, one-off conversions when you don’t need a live formula. Type the desired result in the first cell, then press Ctrl+E to fill the rest.
How to Join a Column with Commas (e.g., for a List)
Often you need to turn a column of data into a comma-separated list in a single cell. Here’s how:
- In a new cell, type
=TEXTJOIN(", ", TRUE, A2:A100)(adjust the range to your data). - Press Enter. The list will appear in that cell.
- If you want the list to update when the source data changes, keep the formula. If you need a static list, copy the cell and paste as values (right-click > Paste Special > Values).
For older Excel without TEXTJOIN, you can use a workaround with the ampersand and a helper column, but it’s tedious for long lists.
Common Problems and Mistakes
- Missing spaces: If you use
=A1&","&B1you getApple,Banana. Always include the space in the delimiter:", ". - Empty cells: With the ampersand method, empty cells produce extra commas (e.g.,
Apple,,Cherry). TEXTJOIN with TRUE ignores them. - Numbers turn to text: The result is always text, even if the source cells are numbers. This is fine for lists, but if you need to use the result in calculations, you may need to convert it back.
- Line breaks: If your data has line breaks, the comma join might look messy. Use
CLEAN()to remove non-printable characters:=TEXTJOIN(", ", TRUE, CLEAN(A1:A10))(array formula in older Excel).
Alternatives: Flash Fill and Power Query
If you don’t want a formula, Flash Fill (Excel 2013+) is the quickest manual method. Type the first joined value, then press Ctrl+E. Excel detects the pattern and fills the rest. For a one-time transformation of a large dataset, Power Query (Get & Transform) can combine columns with a delimiter: go to Data > From Table/Range, select the columns, then Transform > Merge Columns. This is more advanced but very powerful.
Frequently Asked Questions
How do I add a comma and space between cells in Excel?
Use the formula =A1 & ", " & B1. The comma and space are enclosed in quotes. For a range, use =TEXTJOIN(", ", TRUE, A1:A10).
Why is my TEXTJOIN function not working?
TEXTJOIN is only available in Excel 2016, 2019, 365, and Excel for the web. In older versions, it returns a #NAME? error. Use the ampersand method instead, or consider upgrading.
How do I ignore blank cells when joining with commas?
In TEXTJOIN, set the second argument to TRUE (ignore_empty). For the ampersand method, you can use an IF formula, but it’s complex. TEXTJOIN is the right tool.
Can I join cells with a comma and then split them back?
Yes, use Data > Text to Columns, choose Delimited, and select Comma. Or use the TEXTSPLIT function in Excel 365: =TEXTSPLIT(A1, ", ").
How do I add a comma after each cell in a column?
Use a helper column with the formula =A1 & "," and drag down. Then copy the helper column and paste as values. Or use TEXTJOIN to create a single list.
Does the comma join work in Google Sheets?
Yes, Google Sheets supports TEXTJOIN and the ampersand operator. You can also use the JOIN function: =JOIN(", ", A1:A10).
Related
Formula used
in a | First text | - | Apple | 0 in b | Second text | - | Banana | 0 in c | Third text | - | Cherry | 0 out joined | Joined with commas | - | a & ", " & b & ", " & c | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.