How to combine text values using CONCATENATE & TRANSPOSE functions - Excel Trick

Ever wondered how to go from a bunch of cells with text to one big combined text?

[COLLINESBATUNDE]

This is a remakable day in the journey of growth of my career. Thanks Chandoo

[HocExcelOnline]

ExcelTutorials , Thanks for the video!
We can also create a concatenate formula that will update when you change the cells' content (B2:B19):
1. In C1 put x, this will be our separator character
2. In C2 put C1&B2 and fill down to C19
3. Use Concatenate and Transpose with C2:C19, remove the curly braces and replace the quote " in the formula with a zero length character
-> We have a concatenate formula that will update and we can choose our delimiter :)

[Sean]

Although this video is old, THANK YOU!! You have helped streamline so many things for me using this function.

[jamesperry]

Friend of mine wanted me to solve something similar and this video came to my
CONCATENATE + TRANSPOSE + f9 + comma +
Thank u a truly awesome!!

[grant_fitz]

Thanks for the shout out, Chandoo! This made my day.

[jaganmohanraghua]

Chandu you may thank Mr Grant in the Video, but i would like to thank you for the wonderful trick - "Thanks a Ton" - Raghu

[asimasim]

Excellent. Thanks for providing such an information in one go.

[wnenu]

SIR UNABLE TO MAKE IT WITH UR FORMULA, ITS SHOWING '' VALUE'' . INSTEAD OF COMBINATION OF WORDS

[dirkwangke]

Wow.. I have no idea until I saw your video.. Thanks

[sri]

This is really awesome trick... you made my day.... but one question i have .. how can i apply same method in VBA

[shadisamaan]

Thanks so much, this was excatly what i was looking for.

[samkab]

Nice trick..but what if we have several columns like column b ?
It takes time to do them seperatly..is there a quick way ?

[AAmtb]

What if you have a controlled/locked spreadsheets, and users can't access the calculation and highlight/CTRL+= the Transpose calculation?

[teams]

Thank you very much for the useful information ! If there is BLANK cell in between of the rows, will this formula work ? if not, what will be the solutions ? Thanks.

[krismaly]

Awesome
Awesome

Chandoo.org you an Awesome teacher

[harishkumar]

suppose i want to transpose more columns at a time then ??
i want to transform multiple columns to rows separate with comma- with no duplicate values


Ex: column data : a b a c
output should come : row a, b, c


can u explain this

[sachinpawar]

I want to change the column and it should reflect in formula how should u do it

[eredruin]

Dude, thank you, this is so useful !

[Glamador]

Who knew Transpose() could be so useful?

[aidilhalim]

Awesome, this is very saving my work.... keep up posting  a good video brothers... :D