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

Get FREE Advanced Excel Exercises with Solutions!

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).

concatenate rows in excel with comma

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.
=CONCAT(B5:C11)

Utilize the CONCAT Function to Concatenate Rows

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

Utilize the CONCAT Function to Concatenate Rows


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:
=CONCATENATE(C4:I4&",")

Concatenate Rows By Applying CONCATENATE Function

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

Concatenate Rows By Applying CONCATENATE Function

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

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

Concatenate Rows By Applying CONCATENATE Function


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.
=TEXTJOIN(",",TRUE,B5:B13)

Utilize TEXTJOIN Function to Concatenate Rows

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.

Utilize TEXTJOIN Function to Concatenate Rows


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 &“,”.

Concatenate Rows Using Ampersand Operator

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

Concatenate Rows Using Ampersand Operator

  • In this way, you need to include all the cells. Here is the total formula.
=C4&","&D4&","&E4&","&F4&","&G4&","&H4&","&I4

Concatenate Rows Using Ampersand Operator

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo