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.