How to Concatenate Rows in Excel with Comma (4 Easy Methods)

Concatenate is a fancy word for joining something. Microsoft Excel offers many ways to join two or more cell contents. But to join multiple cell contents using a specific delimiter such as a comma or space can be a bit tricky. In this article, we will learn how we can concatenate rows in Excel with commas.

How to Concatenate Rows in Excel with Comma: 4 Easy Methods

In this section, we will demonstrate 4 effective methods to concatenate rows in Excel which will be separated by commas. To illustrate, let’s take a look at the following example where we have a list of G-7 countries in 7 rows (B5 to B11).

Now, our target is to concatenate or join them and make them a single text string where each cell content will be separated by a comma. So let’s begin with our first method.

1. Utilize the CONCAT Function to Concatenate Rows

The CONCAT function is a very useful tool when it comes to joining multiple cell contents. It also allows us to select a range. Now follow the steps below to apply the CONCAT function.

Steps:

• Before using the CONCAT function, we need to insert another column containing delimiter commas.

• Here, I have inserted the comma ”,” in each cell from C5 to C10 but kept the cell C11 empty cause we don’t want a comma at the end of the joint text.
• Now, on cell F7, write down the following formula.

• Then press the Enter key, and you will see your desired result.

2. Concatenate Rows By Applying CONCATENATE Function

The CONCATENATE function is the older version of the new CONCAT function. You can also use this function to join text. But you can not give a range as an argument in CONCATENATE function which is a major drawback. Let’s look at the steps on how to use the function.

Steps:

• First, we need to rearrange the data into a single row as this function doesn’t work properly when data are in multiple rows.

• Now, in the B7 cell, write down the following formula:

• Then, select the C4:I4&”,”  in the formula and press the F9. It will transform the cell reference into cell content.

• After that, eliminate the braces {} from both ends.

• Now, press Enter. You will get your desired result.

3. Utilize TEXTJOIN Function to Concatenate Rows

The TEXTJOIN function is another alternative to the first two methods. Here we will be able to join a range of cells even if they contain blank cells like this below.

Now to apply the formula, follow the steps below.

Steps:

• Enter the formula below in cell D7.

Here,

• In the first argument of the TEXTJOIN function, we give the delimiter which is a comma of course(“,”).
• In the second argument, we choose TRUE as we want to ignore the empty cells.
• And in the 3rd argument, we give the range of the cells. (B5:B13).
• Now, click Enter button. You will have your desired result.

4. Concatenate Rows Using Ampersand Operator

This is a very basic way of joining data in excel. The ampersand operator(&) can be used easily to achieve our desired result. But you have to manually enter all the cell references one by one which makes the process very cumbersome. Let’s see the steps.

Steps:

• Go to cell B7 and first give an equal sign(=) in it.

• Then, select cell C4 and write &“,”.

• Then give a comma, and again select D4 and write &“,”.

• In this way, you need to include all the cells. Here is the total formula.

So, this is how we can get a similar result to the first 3 methods.

Things to Remember

• Try to use the CONCAT function instead of CONCATENATE function as it is becoming obsolete.
• Do not use the Ampersand Operator if your data set is very large.
• All the methods described here except the CONCATENATE function are applicable to both row-wise and column-wise joining

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

That is the end of this article. If you find this article helpful in learning how we concatenate rows in Excel with commas, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.

<< Go Back to Range | Concatenate | Learn Excel