Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Combine CONCATENATE & TRANSPOSE Functions in Excel

Looking for ways to use CONCATENATE & TRANSPOSE functions together in Excel? Then, this is the right place for you. We usually use these two functions to first change columns to rows and then add text values. Here, you will find 3 easy examples of using CONCATENATE & TRANSPOSE functions together in Excel.


Download Practice Workbook

You can download the workbook to practice yourself.


Introduction to Excel CONCATENATE Function

Before starting on the procedure of using the CONCATENATE function with another function, let us have a look at how this function works.

  • Syntax:
=CONCATENATE(text1,[text2],...)
  • Argument:

text1– the first text, number, or array you want to join.

[text2],…– More text, numbers, or arrays that you want to join.

  • Function:

Returns the Concatenated array combining all the values or arrays.

  • Example:

Suppose you have a dataset containing 4 text values in cell range C4:F4. Now, you need to follow the steps given below if you want to join these text values using the CONCATENATE function.

Steps:

  • Firstly, select Cell C5 and insert the following formula.
=CONCATENATE(C4:F4)

Introduction to Excel CONCATENATE Function in Excel

  • Then, select the whole formula and press F9 on your keyboard to convert the formula into values like this.

  • After that, remove Second Brackets ({}) from the formula like the image shown below.

  • Finally, press Enter, and you will get your desired Concatenated Array.


Introduction to Excel TRANSPOSE Function

Secondly, let us have a look at how the TRANSPOSE function works.

  • Syntax:
=TRANSPOSE (array)
  • Argument:

array– the cell range or array you want to transpose.

  • Function:

Converts vertical range or array into horizontal or vice-versa.

  • Example:

Suppose you have text values in the vertical cell range B5:B12. Now, to convert the range into a horizontal one, insert the following formula in Cell D5 and press Enter.

=TRANSPOSE(B5:B12)

Introduction to Excel TRANSPOSE Function

Note: In Excel 365, array functions will automatically spill multiple results and insert them downward or along neighboring cells. For previous versions, press Ctrl +Shift + Enter.

3 Examples of Using CONCATENATE & TRANSPOSE Functions Together in Excel

We can use the CONCATENATE & TRANSPOSE functions together in Excel by going through some simple steps. You will find 3 such examples below.


1. Use Excel CONCATENATE & TRANSPOSE Functions to Combine Text

Firstly, we have some text values in cell range B5:B11. Now, we will join and transpose these values using the CONCATENATE & TRANSPOSE functions together.

Steps:

  • In the beginning, select Cell D5 and insert the following formula.
=CONCATENATE(TRANSPOSE(B5:B12))

Use Excel CONCATENATE & TRANSPOSE Functions to Combine Text in Excel

  • Then, press Enter and get your desired Concatenated Array.

Excel Concatenate Transpose

🔎 How Does the Formula Work?

  • Firstly, the TRANSPOSE function converts the vertical cell range B5:B12 into a horizontal one.
  • After that, the CONCATENATE function joins the values in that cell range.

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


Similar Readings


2. Apply CONCATENATE & TRANSPOSE Functions with Commas and Spaces

In the second example, we have the Names of some students. Now, we will show you how you can add commas and spaces using the CONCATENATE & TRANSPOSE functions in Excel.

Steps:

  • To start with, insert the following formula in Cell D5.
=CONCATENATE(TRANSPOSE(B5:B12&", "))

Apply CONCATENATE & TRANSPOSE Functions with Commas and Spaces in Excel

  • Finally, press Enter and get your desired Concatenated Array.

🔎 How Does the Formula Work?

  • In the beginning, the TRANSPOSE function converts the vertical cell range B5:B12 into a horizontal one and adds a comma and space using the Ampersand Operator (&).
  • Finally, the CONCATENATE function joins the values in that cell range.

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


3. Add Character Along with CONCATENATE & TRANSPOSE Functions in Excel

In the last method, we will show you how you can add characters along with CONCATENATE & TRANSPOSE functions in a merged cell. Here, you have to use these functions step by step. Follow the steps given below to do it on your own.

Steps:

  • Firstly, insert the following formula in the merged cell.
=TRANSPOSE(B5:B12&" ")

Add Character Along with CONCATENATE & TRANSPOSE Functions in Excel

Here, in the TRANSPOSE function, we inserted cell range B5:B12 as an array and added space as a character using the Ampersand Operator (&).
  • Then, select the whole formula and press F9 on your keyboard to convert the formula into values like this.

  • After that, add the CONCATENATE function at the beginning and remove Second Brackets ({}) as follows.

  • Finally, press Enter and you will see the required output.

Here, the CONCATENATE function is used to join the values of the array.

Read More: Combine Multiple Columns into One Column in Excel


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


Conclusion

So, in this article, we have shown you 3 ways to use CONCATENATE & TRANSPOSE functions together in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Additionally, please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo