How to Concatenate Rows in Excel (11 Methods)

In this tutorial, I will discuss how to concatenate cell contents that are spread over several rows in Excel. Basically, concatenate means merge/combine/join two or more cells’ contents into a single cell. To illustrate, often we have to concatenate names, and 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.

1. Applying Ampersand(&) Operator to Concatenate 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.

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

2. Using 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 the 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.

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

• 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 Multiple Rows into One Cell in Excel

3. Inserting Excel 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.

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

4. Using CONCAT Function to Combine Rows in Excel

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.

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

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

• Consequently, we will get the following concatenated text.

6. Joining TRANSPOSE and CONCATENATE Functions to Add Rows

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

Steps:

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

• 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:

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

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

7. Merging Excel IF and TEXTJOIN Functions to Concatenate Multiple Rows

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.

• 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. Combining TEXTJOIN, OFFSET, and COUNTIF  Functions to Add Rows

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.

• 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}

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

10. Using Excel Power Query Tool 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.

11. Concatenating Rows in Excel Using VBA Macro

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.

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

Download Practice Workbook

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

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.

<< Go Back to Range | Concatenate | Learn Excel