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.

## 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)` • 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)` 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))` • Then, press Enter and get your desired Concatenated Array. 🔎 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.

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

`=CONCATENATE(TRANSPOSE(B5:B12&", "))` • 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.

### 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&" ")` 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.

## Practice Section

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