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.

**Table of Contents**hide

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

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

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

**Similar Readings**

**How to Concatenate Cells but Keep Text Formatting in Excel****Merge Rows in Excel (2 Easy Methods)****How to Concatenate with Space in Excel (3 Suitable Ways)****Concatenate Date and Time in Excel (4 Formulas)****How to Concatenate Multiple Cells in Excel (7 Easy Ways)**

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

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

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

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

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

**How to Concatenate Cells with If Condition in Excel (5 Examples)****Copy CONCATENATE Formula in Excel (5 Easy Ways)****Excel CONCATENATE Showing Formula Not Result (5 Solutions)****How to Concatenate Formula and Text in Excel (4 Examples)****CONCATENATE vs CONCAT in Excel (2 Ideal Examples)****How to Concatenate Number and Text in Excel (6 Methods)****Concatenate Names in Excel (5 Easy Ways)**