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)
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:
- 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 Concatenate Cells with If Condition in Excel
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.
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Download Practice Workbook
You can download the workbook to practice yourself.
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.
Thank you!
Related Articles
- Excel INDEX MATCH to Concatenate Multiple Results
- How to Concatenate If Cell Values Match in Excel
- How to Concatenate with VLOOKUP in Excel
- How to Concatenate Email Addresses in Excel
- How to Concatenate Decimal Places in Excel
- How to Add Parentheses with CONCATENATE Function in Excel
- How to Concatenate Different Fonts in Excel
