In this tutorial, I will discuss how to concatenate cell contents that spread over several rows in excel. Basically, concatenate means merge/combine/join two or more cells’ contents to a single cell. To illustrate, often we have to concatenate names, parts of addresses to specific desired formats. In this tutorial, I will use excel functions, operators, **VBA**, Power Query & **Fill** option to concatenate rows in excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

## 11 Methods to Concatenate Rows in Excel

### 1. Apply ‘&’ Operator to Combine Rows in Excel

You can concatenate strings using the** Ampersand **(**&**) operator. The advantage of using the **Ampersand** operator is that there is no string limit for allowable strings to join. Let’s suppose I will concatenate the contents from the dataset **B5:B9**.

**Steps:**

- Type the below formula in
**Cell C5**.

`=B5&" "&B6&" "&B7&" "&B8&" "&B9`

- Hit
**Enter**from the keyboard and the texts from**5**rows are joined as below.

**Read More:** **How to Merge Rows in Excel (2 Easy Methods)**

### 2. Use Excel IF function and ‘&’ Operator to Concatenate Rows

Now, I will apply **the IF function** in excel with the ‘**&**’ operator to concatenate rows. Let’s assume I have a dataset containing fruit’s type in column** B **and related fruit names are listed in column **C**. So, I will concatenate these fruits in single cells according to their fruit name.

**Steps:**

- First, type the below formula in
**Cell D5**and hit**Enter**.

`=IF(B5=B4,D4&","&C5,C5)`

- We will get the below result. Next, use the
**Fill Handle**(**+**) tool to copy the formula to the range**D5:D10**.

- As a result, we will get a growing list of fruit names to their corresponding fruit type.

- However, the above is not the ultimate result we want. As our dataset is sorted alphabetically according to fruit type, the cell containing the longest text will be our result. Now. I will mark cells that have the longest text by putting ‘
**Last**’ so that we can find the ultimate resulting cells easily. So, type the below formula in**Cell E5**.

`=IF(B5<>B6,"Last","")`

- Press
**Enter**and use the**Fill Handle**tool to copy the formula to the rest of the cells. Finally, we will get the below result; returning ‘**Last**’ to the longest concatenated texts.

⏩** Note:**

➥ You have to sort the dataset accordingly before applying the above formulas.

**Related Content: How to Combine Rows into One Cell in Excel**

### 3. Insert CONCATENATE Function to Combine Rows

Let’s use simply **the CONCATENATE function** to join excel rows. I will separate the joined text with space.

**Steps:**

- Type the following formula in
**Cell C5**.

`=CONCATENATE(B5," ",B6," ",B7," ",B8," ",B9)`

- Press
**Enter**and here is the joined text you will get.

**Read More:** **How to Combine Multiple Cells Into One Cell Separated By Comma In Excel (Functions & VBA)**

### 4. Excel CONCAT Function to Combine Rows

In **Excel 365** and** Excel 2019** versions, you can use** the CONCAT function **to combine text from a range spread over different rows.

**Steps:**

- First, type the below formula in
**Cell C5**to join text from the range**B5:B7**.

`=CONCAT(B5:B7)`

- Press
**Enter**, you will get the combined text as below.

**Read More:** **Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)**

### 5. Excel TEXTJOIN Function to Join Rows

You can use **the TEXTJOIN Function **in excel to combine text from different rows.

**Steps:**

- Type the following formula in
**Cell C5**and hit**Enter**from the keyboard.

`=TEXTJOIN(" ",TRUE,B5:B9)`

- Consequently, we will get the following concatenated text.

**Read More: Concatenate Not Working in Excel (3 Reasons with Solutions)**

### 6. TRANSPOSE and CONCATENATE Functions to Concatenate Rows

This time, I will combine **the TRANSPOSE function** along with **CONCATENATE** function to get a joined text from rows.

**Steps:**

- Firstly, type the below formula in
**Cell C5**but do not press**Enter**.

`=TRANSPOSE(B5:B7)`

- Next, put the cursor on the above formula and press
**F9**from the keyboard. As a result, the formula will look like below. Now, remove both of the curly brackets from the formula.

- After deleting the curly brackets, type
**CONCATENATE**before the first value and close the formula with parentheses as we do in excel formulas. Finally, the formula looks like below:

`=CONCATENATE(221,"Baker","Street")`

- Now, press
**Enter**. - In the end, you will get the joined text as below.

**Related Content:** **Opposite of Concatenate in Excel (4 Options)**

**Similar Readings:**

**How to Bold Text in Concatenate Formula in Excel (2 Methods)****Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)****How to Concatenate Date That Doesn’t Become Number in Excel (5 Ways)****Add Leading Zeros in Excel by CONCATENATE Operation****Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)**

### 7. IF and TEXTJOIN Functions to Concatenate Multiple Rows in Excel

Here, I will use** IF** and** TEXTJOIN** functions to combine strings from rows. Suppose, I have a dataset (**B5:C10**) where, in column** B**, fruit type is mentioned in two cells. On the other hand, in column **C **corresponding fruit names are mentioned. Now I will combine all the fruit names in a fruit type category and return accordingly.

**Steps:**

- Type the below formula in
**Cell D5**and press**Enter**.

`=IF(B5="","",TEXTJOIN(", ",TRUE,C5:C7))`

- As a consequence, you will get the below result, Use the
**Fill Handle**to copy the formula to the rest of the cells.

- Finally, you will see that all the fruits under a certain category are concatenated as below.

**Related Content:** **How to Concatenate Multiple Cells in Excel (8 Quick Approaches)**

### 8. Combination of TEXTJOIN, OFFSET, and COUNTIF Functions to Concatenate Rows in Excel

You can use the combination of **TEXTJOIN**, **OFFSET**, **COUNTIF**, and** IF** functions to join rows in excel. Let’s suppose, we have the below dataset containing fruit types and fruit names. Now, I will join the fruits according to their type.

**Steps:**

- Type the below formula in
**Cell D5**at first and press**Enter**.

`=IF(B5=B4,"",TEXTJOIN(", ",TRUE,OFFSET(C5,0,0,COUNTIF(B:B,B5),1)))`

- Upon entering the formula, you will get the below result. I have used the
**Fill Handle**tool to copy the formula to the rest of the cells.

🔎 **How Does the Formula Work?**

**➤**** COUNTIF(B:B,B5)**

Here, this part of the formula returns:

{**3**}

**➤**** OFFSET(C5,0,0,COUNTIF(B:B,B5),1)**

Next, this part replies:

{**$C$5:$C$7**}

**➤**** TEXTJOIN(“, “,TRUE,C$5:$C$7)**

Now, this part returns:

{**Lemon,Orange,Lime**}

**➤**** IF(B5=B4,””,TEXTJOIN(“, “,TRUE,OFFSET(C5,0,0,COUNTIF(B:B,B5),1)))**

Finally upon applying the **B5=B4** condition the formula returns:

{**Lemon,Orange,Lime**}

**Read More:** **How to Concatenate Columns in Excel (8 Simple Methods)**

### 9. Fill Justify Command to Join Rows in Excel

By applying the **Fill** option you can join strings from rows very quickly.

**Steps:**

- First, select the range (
**B5:C9**).

- Then go to
**Home**>**Editing**group >**Fill**>**Justify**.

- After you click on
**Justify**, all the text from the selected rows will be joined.

**Read More:** **How to Concatenate Multiple Cells With Space in Excel (7 Methods)**

### 10. Use Power Query to Add Rows

You can use **Excel Power Query** too to combine rows.

**Steps:**

- Firstly, select anywhere on the below dataset.

- Go to
**Data**>**From Table/Range**option.

- Then
**Create Table**dialog will appear, check the table range, and click**OK**.

- After that, the below table will be visible in the
**Power Query Editor**window.

- From the
**Power Query Editor**, go to**Transform**>**Transpose**.

- As a result, our texts will be separated into several columns. Select all the columns by pressing the
**Shift +****➝**keys.

- Then right-click on the selection and click on
**Merge Columns**.

- Subsequently, the
**Merge Columns**dialog will show up. Choose ‘**Space**’ as the**Separator**, type the**New column name**, and click**OK**.

- Upon clicking
**OK**, we will get the text concatenated in a single cell. Go to**Home**>**Close & Load**>**Close & Load**to close the**Power Query Editor**.

- Finally, here is the result we received in excel.

**Related Content: How to Add a 1 in Front of Numbers in Excel [7 Special Tricks]**

### 11. Concatenate Rows in Excel Using VBA

I will show you how you can combine strings from rows with a simple** VBA** code. Follow the below steps and get the desired result.

**Steps:**

- First, select the range (
**B5:B9**).

- Then right-click on the sheet name and click on the
**View Code**to bring the**VBA**window. Yow can use**Alt + F11**too keys to bring the**VBA**window.

- Type the below code in the
**Module**and run it by pressing the**F5**key from the keyboard. Specify the resulting cell in code; where you want to see the concatenated text. I have put**C5**as I want to see my result in**Cell C5**.

```
Sub ConcatenateText()
Dim text As Range
Dim i As String
For Each text In Selection
i = i & text & " "
Next text
Range("C5").Value = Trim(I)
End Sub
```

- Upon running the code, we will get the below result.

**Read More: How to Concatenate String and Integer using VBA**

## Things to Remember

➨ You can use both the **CONCATENATE** function and **Ampersand** (**&**) operator to join rows in excel. However, the **CONCATENATE** function can join up to **255 **strings.

➨ You cannot pass a range through the **CONCATENATE** function. Such as you cannot write ‘**=Concatenate(B5:B9)**’. Each of the cells must be referenced separately as: **=CONCATENATE(B5,” “,B6,” “,B7,” “,B8,” “,B9)**.

## Conclusion

In the above article, I have tried to discuss several methods to concatenate rows in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.