=TEXTJOIN(", ",TRUE,A1:A5) is the one to use — it takes a whole range, puts a comma and space between each value, and the TRUE skips blanks. For a handful of specific cells, =A1&", "&A2&", "&A3 works in any version. TEXTJOIN needs Excel 2019 or later; on 2016 and earlier, the ampersand version is the practical option.
CONCAT accepts a range but has no delimiter argument, so it only helps here if you add a helper column of commas — which also leaves a trailing comma on the final value.
In Excel, there are multiple methods that you can use to concatenate cells by using a comma between the values. In this tutorial, we are going to look at all those methods.
TEXTJOIN Function to Combine Cells with a Common (or any other character)
In the following example, you have some numeric values in the range A1 to A5, and you need to combine all those values in cell B1.
You can use the following steps:
- First, type TEXTJOIN in cell B1.
- After that, add a comma (“, “) for the delimiter argument. This also includes a space after the comma.
- Next, enter TRUE to ignore_empty.
- Now, select the range A1:A5 from which you need to combine the values.
- In the end, enter the closing parenthesis and then hit enter.
=TEXTJOIN(", ",TRUE,A1:A5)
Use the CONCAT Function to Concatenate Values with a Comma
You can also use the CONCAT function for this as well. But you must add a helper column with the commas next to the actual values. In the following example, we have added comma values in column B (B1:B5).
Now, the formula is quite easy. You must enter the CONCAT function in cell C1 and refer to the entire range (A1:B5). When you hit enter, you will get all the values in cell C1, with a comma afterward.
=CONCAT(A1:B5)
Using the Simple Concatenate Method
You can also use the simple concatenate method, where you refer to each cell and add a comma after each reference.
=A1&", "&A2&", "&A3&", "&A4&", "&A5
Each method has its benefits from all the methods we have used. But I like to use TEXTJOIN as it gives me more flexibility to combine values in a single cell without adding a new helper column.
1024