Arin Islam
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)
- 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)
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&" ")
- 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.
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)
