# 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. ## 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. 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` • Then we press OK.
• The final result will be like this. 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)` • Then we drag down the formula.
• The final result will be like this. 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)` • Then we press OK.
• The final result will be like this. 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) • Then we press OK.
• The final result will be like this. 📕 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)` • Then we press OK.
• The final result will be like this. 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) • Then we must not press. We take the cursor just above the formula, and then we press F9. We will see this now. • 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")` • The final result will be like this. 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))` • Then we press OK.
• The final result will be like this. 🔨  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))` • Then we press OK.
• The final result will be like this. `🔨`  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. • Then we press OK.
• The final result will be like this. 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. • We select table range here, then click OK.
• When we look at Power Query Editor again, we will see the table below. • Then we 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. • After that, Merge Columns dialog box will appear. We select “Space” as Separator, we type the name of new column, and click on 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. • The final result will be like this. 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.

⬇️⬇️ 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. • 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. • 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. • Now we will see the result. ## `📄`  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  