How to Concatenate Rows in Excel (11 Methods)

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.


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

Apply ‘&’ Operator to Combine Rows in Excel

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

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

Steps:

  • First, type the below formula in Cell D5 and hit Enter.
=IF(B5=B4,D4&","&C5,C5)

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

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

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

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

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

  • 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","")

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

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

Insert CONCATENATE Function to Combine Rows in Excel

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

Excel CONCAT Function to Combine Rows

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

Excel TEXTJOIN Function to Join Rows

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

TRANSPOSE  and CONCATENATE Functions to Concatenate Rows

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

TRANSPOSE  and CONCATENATE Functions to Concatenate Rows

  • 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")

TRANSPOSE  and CONCATENATE Functions to Concatenate Rows

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


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.

IF and TEXTJOIN Functions to Concatenate Multiple Rows in Excel

Steps:

  • Type the below formula in Cell D5 and press Enter.
=IF(B5="","",TEXTJOIN(", ",TRUE,C5:C7))

IF and TEXTJOIN Functions to Concatenate Multiple Rows in Excel

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

IF and TEXTJOIN Functions to Concatenate Multiple Rows in Excel

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

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

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

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

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

Fill Justify Command to Join Rows in Excel

  • Then go to Home > Editing group > Fill > Justify.

Fill Justify Command to Join Rows in Excel

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

Use Power Query to Concatenate Rows

  • Go to Data > From Table/Range option.

Use Power Query to Concatenate Rows

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

Use Power Query to Concatenate Rows

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

Use Power Query to Concatenate Rows

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

Use Power Query to Concatenate Rows

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

Use Power Query to Concatenate Rows

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

Use Power Query to Concatenate Rows

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

Use Power Query to Concatenate Rows

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

Concatenate Rows in Excel Using VBA in Excel

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

Concatenate Rows in Excel Using VBA in Excel

  • 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

Concatenate Rows in Excel Using VBA in Excel

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


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo