11 Easy Ways to Concatenate Rows in Excel

If you are looking for concatenating Excel rows, you’ve come to the right place. There are several ways to concatenate rows in Excel, and in this article, we’ll try to talk about the 11 quickest ways of concatenating rows in Excel. Here is an example of our result.

how to concatenate rows in excel


📁 Download Excel File

Download this practice book.


Learn to Concatenate Rows in Excel with These 11 Easy Methods

In this article, I’ll go through how to concatenate Excel cell contents that span multiple rows. Concatenation basically refers to joining the contents of two or more cells into one cell. To give an example, we often need to concatenate names and address components to achieve certain required formats. In this article, I’ll show you how to concatenate rows in Excel using VBA, Power Query and Fill, and Excel functions and operators.

Dataset

Method 1

1. Using Ampersand ‘&’ Operator

Ampersand (&) operator can be used to join strings together. When you use ampersand operator, there is not any limit on how many strings you can join together. Let’s say I want to join the data from dataset B6 to B10.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=B6&" "&B7&" "&B8&" "&B9&" "&B10

Putting formula Using Ampersand ‘&’ Operator

  • Then we press OK.
  • The final result will be like this.

how to concatenate rows in excel

Method 2

2. Combining IF Function with Ampersand ‘&’ Operator

Now, I’ll use the “&” operator and IF function to join rows together. IF function enables you to compare a value to what you anticipate logically. Let’s say I have dataset with the type of sport in the column B, the names of related players in the column C. Now, I’ll put each player’s name in a single cell and concatenate them together. Before you can use the above formulas, you have to put the dataset in the right order.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell D6. Then we put the formula below:
=IF(B6=B5,D5&","&C6,C6)

Putting Formula Combining IF Function and ‘&’ Operator

  • Then we drag down the formula.
  • The final result will be like this.

Dragging down the formula

Method 3

3. Inserting CONCATENATE Function

To join or put together is what the word CONCATENATE means. With Excel’s CONCATENATE function, you can put the text from several cells into one cell. This Excel tutorial will show you how to use CONCATENATE function by giving you examples and showing you the syntax. To join rows, let’s just use CONCATENATE function. I will put a space between the two pieces of joined text.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=CONCATENATE(B6," ",B7," ",B8," ",B9," ",B10)

Putting formula Inserting CONCATENATE Function

  • Then we press OK.
  • The final result will be like this.

Dragging down the formula Inserting CONCATENATE Function

Method 4

4. Utilizing CONCAT Function

To join or put together is what the word CONCAT means. With Excel’s CONCAT function, you can put the text from several cells into one cell. This Excel tutorial will show you how to use the CONCAT function by giving you examples and showing you the syntax. CONCAT does not require delimiter or IgnoreEmpty arguments for combining text from multiple ranges or strings.

CONCAT takes the place of CONCATENATE. But the CONCATENATE function will remain so that it can be used with older versions of Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=CONCAT(B6:B8)

Putting formula Utlising CONCAT Function

  • Then we press OK.
  • The final result will be like this.

Dragging down the formula Utlising CONCAT Function

📕 Read More: 7 Ways to Concatenate Multiple Cells with Space in Excel

Method 5

5. Inserting TEXTJOIN Function

To join Excel rows, let’s just use TEXTJOIN function. I will put a space between the two pieces of joined text. The TEXTJOIN function joins text from various ranges and/or strings with a delimiter you define between each text value that will be joined. If the delimiter is an empty text string, the ranges will be successfully concatenated. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=TEXTJOIN(" ",TRUE,B6:B10)

Putting formula Inserting TEXTJOIN Function

  • Then we press OK.
  • The final result will be like this.

Dragging down the formula Inserting TEXTJOIN Function

📕 Read More: 2 Ways to Bold Text in Concatenate Formula in Excel

Method 6

6. Combining CONCATENATE and TRANSPOSE Functions

This time, I’ll use CONCATENATE and TRANSPOSE functions to get a combined text from rows. It is sometimes necessary to switch or rotate cells. This can be accomplished by copying, pasting, and then using the Transpose option. However, doing so results in redundant data. Instead, you can use the TRANSPOSE function to enter a formula if you don’t want that. To join or put together is what the word CONCATENATE means. With Excel’s CONCATENATE function, you can put the text from several cells into one cell. This Excel tutorial will show you how to use the CONCATENATE function by giving you examples and showing you the syntax.To join Excel rows, let’s just use the CONCATENATE function along with TRANSPOSE. I will put a space between the two pieces of joined text. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=TRANSPOSE(B6:B8)

Using TRANSPOSE Function

  • Then we must not press. We take the cursor just above the formula, and then we press F9. We will see this now.

We take the cursor kust above the formula ,then we press F9.

  • Now we remove brackets like this. After removing curly brackets, put CONCATENATE before first value to close formula with parenthesis, just as we would in an Excel calculation. Finally, formula is as follows.
=CONCATENATE("Dhanmondi,","27,","Dhaka")

 put CONCATENATE before the first value and close the formula with parenthesis

  • The final result will be like this.

Combining TRANSPOSE and CONCATENATE Functions

Method 7

7. Combining TEXTJOIN and IF Functions

Here, I’ll combine text strings from different rows by using the IF and IF function.  Let’s say I have dataset with the type of sport in the column B and the names of related players in the column C. So, I’ll put each player’s name in a single cell and concatenate them together. Before you can use the above formulas, you have to put the dataset in the right order. The TEXTJOIN function joins text from various ranges and/or strings, with a delimiter you define between each text value that will be joined. If the delimiter is an empty text string, the ranges will be successfully concatenated. IF function enables you to compare a value to what you anticipate logically. Whereas the TEXTJOIN function joins text from various ranges and/or strings with a delimiter, you define between each text value that will be joined. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=IF(B6="","",TEXTJOIN(", ",TRUE,C6:C8))

Putting formula Combining IF and TEXTJOIN Functions

  • Then we press OK.
  • The final result will be like this.

Dragging the formula down Combining IF and TEXTJOIN Functions

🔨  Formula Breakdown

👉  Here,  TEXTJOIN(“, “,TRUE,C6:C8)  joins text from the ranges

of cells C6 to C8.

👉  IF(B6=””,””,TEXTJOIN(“, “,TRUE,C6:C8)) Here IF function enables you to compare a value to what you anticipate logically. Here it looks for the match so that player’s name will be added if they play the same sport.

Method 8

8. Using TEXTJOIN, COUNTIF, and OFFSET Functions Together

To join rows in Excel, combine the TEXTJOIN, IF, COUNTIF, and OFFSET functions. The OFFSET function takes a cell or range of cells as input and returns a reference to a range with the specified number of rows and columns.  Whereas the TEXTJOIN function joins text from various ranges and/or strings with a delimiter, you define between each text value that will be joined. This formula uses the COUNTIF function to find each name of one Column in another Column. Finally, the IF function enables you to compare a value to what you anticipate logically. Let’s say I have a dataset with the type of sport in the column B and the names of related players in the column C. So, I’ll put each player’s name in a single cell and concatenate them together.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=IF(B6="","",TEXTJOIN(", ",TRUE,C6:C8))

Putting formula Using TEXTJOIN, OFFSET, and COUNTIF Functions Together

  • Then we press OK.
  • The final result will be like this.

Final result using TEXTJOIN, OFFSET, and COUNTIF Functions Together

🔨  Formula Breakdown

👉  COUNTIFS (B:B,B6) Here, the formula tells us: {3}

👉  OFFSET(B5,0,0,COUNTIF(B:B,B6),1) Next, it returns: {$C$6:$C$8}

👉  TEXTJOIN(“, “,TRUE,C$6:$C$8) returns this part: {John,Tom,Ron}

Method 9

9. Utlising Fill Justify Command

With Fill option, you can quickly join text strings from different rows. So here, we have previously copied the dataset to another column. Because the Fill option removes the original data and rewrites that data. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell range B6:C10.
  • Then from Home tab select Fill, and then Justify. We will see Fill Justify there.

Selecting FILL JUSTIFY

  • Then we press OK.
  • The final result will be like this.

 how to concatenate rows in excel Utlising Fill Justify Command

Method 10

10. Making Use of Power Query for Adding Rows

Moreover, we can use the Power Query tool of Excel for concatenating rows in Excel. Power Query, an Excel business intelligence tool, lets you ingest data from multiple sources and clean, modify, and reshape it. Refreshing a query lets you reuse it. Powerful too. Power Query can load and clean millions of rows into the data model for analysis following. The user interface is intuitive and nicely planned out, so it’s incredibly easy to pick up. Its learning curve is far shorter than formulae or VBA.

It’s great because you need not learn to program. Similar to VBA’s Macro recorder, the power query editor records your modifications step-by-step and translates them into M code. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select any cell on the dataset.
  • Then we go to the Data Tab. There we will find From Table/Range; clicking there, we will find a Create Table.

Finding From Table/Range option

  • We select table range here, then click OK.
  • When we look at Power Query Editor again, we will see the table below.

● When we look at the Power Query Editor window again, we will see the table below.

  • Then we go to Transform , then to Transpose in the Power Query Editor.

go to Transform , then to Transpose in the Power Query Editor.

  • So, the texts will be split up into different columns. Pressing Shift and Left arrow (➝) key together, we can choose all the columns.
  • Then, with a right-click, we select Merge Columns.

we select Merge Columns.

  • After that, Merge Columns dialog box will appear. We select “Space” as Separator, we type the name of new column, and click on OK.

. We select "Space" as the Separator, we type the name of the New column, and click OK.

  • When we click OK, all of the text will be put into single cell. To close the Power Query Editor, we go to Home, Close & Load, and Close & Load again.

close the Power Query Editor, we go to Home , then to Close & Load and finally to Close & Load again.

  • The final result will be like this.

Making Use of Power Query to Add Rows

Method 11

11. Implementing VBA

Now we will use VBA for concatenating rows in Excel. Before talking about the method, first, we have enabled Developer Tab on our Ribbons. You can follow the link to see the process of enabling the Developer Tab by clicking here. The Developer tab is a function that is featured in Microsoft Excel; however, it is typically buried because the default setting for Excel is to hide it.

Users are granted the ability to construct VBA applications, design forms, generate macros, import and export XML data, and a variety of other tasks using the tab.

In order for the Developer tab to become visible on the toolbar of the Excel window, it must first be enabled in the Options section of the File menu. Evaluation of the financial performance of organizations, simulation of predicted results, and even comparison of investment possibilities are all under the purview of finance specialists. They are able to carry out more advanced functions in Excel, such as designing forms, writing code, building macros, as well as importing and exporting XML file formats when the Developer tab is activated.

Let’s follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • Let’s press ALT + F11 to bring up the VBA. You can also do this by going to the Developer tab and clicking on Visual Basic.

Going to the Developer tab and clicking on Visual Basic.

  • Then we choose Module from Insert from the menu. This will bring up the window for the VBA Module. Here is where we will put our code.

Selecting Module

  • Now we type the code below:
 Sub ConcatenatingRows()
 
 Dim R As Range
 Dim S As String
 For Each R In Selection
 S = S & R & " "
 Next R
 Range("C6").Value = Trim(S)
 
 End Sub
  • Now we save and run the code.

Save and Run the Code

  • Now we will see the result.

how to concatenate rows in excel Using VBA


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So, the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the side of each Excel worksheet file, there is space where they can practice.


📝  Takeaways from This Article

📌  Readers will be able to concatenate rows in Excel with various methods.

📌  They will be able to apply the ‘&’ Operator to combine rows in Excel.

📌  Also, they can use the Excel IF function and ‘&’ Operator to concatenate rows.

📌  Again they can insert CONCATENATE function to combine rows.

📌  The readers can use Excel CONCAT function to combine rows.

📌  Again they can apply Excel TEXTJOIN function to join rows.

📌  Besides, they can apply TRANSPOSE and CONCATENATE functions to concatenate rows.

📌  They can combine IF and TEXTJOIN functions to do the same.

📌  The readers can apply the combination of TEXTJOIN, OFFSET, and COUNTIF functions to concatenate rows in Excel.

📌  They can also insert the Fill Justify Command to join rows in Excel.

📌  Again, the readers can use Power Query to add rows.

📌  Finally, they can concatenate rows in Excel using VBA.


Conclusion

That concludes today’s session. These are the methods for concatenating rows in excel. That concludes today’s session. We are hopeful that this article will be extremely beneficial to you. I have faith that utilizing these approaches will make completing your chores a breeze for you. In addition, the practice book may now be found at the beginning of the article thanks to our addition. You can download it to find out more information. In conclusion, I’d want to invite anyone who may still have questions or comments to post them in the space below for others to see. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen.com, the best Excel solutions provider.


Related Articles

(Visited 52 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo