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.

`📁`

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.

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

**📕 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)**

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

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

- 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

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

**2 Steps to Concatenate Multiple Results with INDEX-MATCH in Excel****6 Ways to Concatenate Numbers with Leading Zeros in Excel****6 Ways to Concatenate with Carriage Return in Excel Formula****5 Examples to CONCATENATE If Date Becomes Number in Excel****5 Easy Ways to Add Comma in Excel to Concatenate Cells**