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, for the delimiter argument, add a comma (“, “). 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 CONCAT Function to Concatenate Values with a Comma
You can also use the CONCAT function for this as well. But you need to 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 simply need to 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
And you can also use the simple concatenate method where you can refer to each cell and with each reference to the cell, you can add a comma.
=A1&", "&A2&", "&A3&", "&A4&", "&A5From all, the methods that we have used, each method has its benefit. 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.