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 :)
Friend of mine wanted me to solve something similar and this video came to my rescue........ CONCATENATE + TRANSPOSE + f9 + comma + space......... Thank u Chandoo......u a truly awesome!!
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.
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 case........
many thanks how do combine 2 columns with 2 sets of number for example 53531613 g1 x12084256 h1 =g1&h1 53531613x12084256 can you please advise how to remove the "x"
Dear, Please help to know as how to arrange data in one cell by use comma (,) from a list in one column such as from one column 504020 504020 302080 504020 302080 504020 to in one cell like as 302080, 504020 Thanks/Maksud
Hi Hope this email finds you well. How can I join the below numbers. Raw data In the 1st column - 1 In the 2nd column - 234 In the 3rd column - 11 Required output 001-000234-011
This is a remakable day in the journey of growth of my career. Thanks Chandoo
Although this video is old, THANK YOU!! You have helped streamline so many things for me using this function.
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 :)
Thanks for the shout out, Chandoo! This made my day.
Friend of mine wanted me to solve something similar and this video came to my rescue........
CONCATENATE + TRANSPOSE + f9 + comma + space.........
Thank u Chandoo......u a truly awesome!!
Excellent. Thanks for providing such an information in one go.
Saved a lot of time. Thanks
Wow.. I have no idea until I saw your video.. Thanks
Awesome trick! Thanks for sharing.
you just made my day.
Chandu you may thank Mr Grant in the Video, but i would like to thank you for the wonderful trick - "Thanks a Ton" - Raghu
This video helped me much. Thanks
THANK YOU FOR YOUR ALL TEAM
Thanks so much , this was excatly what i was looking for.
Good formula overview in excel.
Great solution - MANY THANKS
Useful trick, thanks!
Dude, thank you, this is so useful !
Who knew Transpose() could be so useful?
SIR UNABLE TO MAKE IT WITH UR FORMULA, ITS SHOWING '' VALUE'' . INSTEAD OF COMBINATION OF WORDS
Awesome
Awesome
Chandoo.org you an Awesome teacher
This is really awesome trick... you made my day.... but one question i have .. how can i apply same method in VBA
Great work. Thank You very much...
Awesome, this is very saving my work.... keep up posting a good video brothers... :D
Great explanation , very useful formula thank you !
thank you so much for this!!!
Great ......
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.
Nice trick..but what if we have several columns like column b ?
It takes time to do them seperatly..is there a quick way ?
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 case........
I want to change the column and it should reflect in formula how should u do it
What if you have a controlled/locked spreadsheets, and users can't access the calculation and highlight/CTRL+= the Transpose calculation?
very useful
how to merge: John Nov-01 1,500 in one column? thank you.
Awesome!!
Thanks a lots
Brother i would like to keep arabic and english numbers in single cell .. it should type arabic when i change language in keyboard..
Thanks sir
You are welcome Rahul..
many thanks how do combine 2 columns with 2 sets of number for example 53531613 g1 x12084256 h1
=g1&h1
53531613x12084256 can you please advise how to remove the "x"
Thanks a lot :-)
How can I add a separator coz in my excel it has no space or separator when I use this formula
How you convert "Transpose" into words? any short cut for that?
2:56 press what?? Press L or alt?
how to press control equal two? i dont understand it
THANKS
How To Bold Some Text On merged cells, Please Provide Salutation
Sweet...
How do I concatenate cell values until it finds a blank cell. Note: - The answer should display in the blank cell.
Hi Rajesh... You should use either VBA or manual methods for this.
Excel & Power BI Tuts from Chandoo Thanks for replying. I got the perfect answer through VBA code.
Hi, How can i select a string from a cell and change its formatting. Please help thanks
What do you want to change the formatting to? Strings have limited options to format thru cell formatting.
wouldn't it be much easier to use the function "concat"
I use this very often and get the same results
so eg: =concat(A2:A19&" ")
Yes. This video is for people who are using an older version of Excel without CONCAT or TEXTJOIN.
How iIf one word italic form more word
Trop fort
like how you say "CONCATENATE"
it does'nt work with huge amount of list.
If you have lots of data or need to use conditions, try the new TEXTJOIN formula or use Power Query.
has his computer not got any colour.
Dear, Please help to know as how to arrange data in one cell by use comma (,) from a list in one column such as
from one column
504020
504020
302080
504020
302080
504020
to in one cell like as
302080, 504020
Thanks/Maksud
Add Comma
302080 , =A5&B5 =CONCATENATE(C5,C6,C7,C8,C9,C10)
302080 , =A6&B6
504020 , =A7&B7
504020 , =A8&B8
504020 , =A9&B9
504020 , =A10&B10
302080 , 302080, 302080,302080,504020,504020,504020,504020,
302080 , 302080,
504020 , 504020,
504020 , 504020,
504020 , 504020,
504020 , 504020,
Useful but why bother with the transpose element when we can just use CONCATENATE alone and replace the ";" with ","?
I think with transpose, you get to avoid an extra step. But better still, if you have new versions, just use TEXTJOIN or CONCAT
@@chandoo_ I appreciate that the original video is 6 years old and Excel moves on. However, simplicity of not using TRANSPOSE should win out.
first tell how to make this table
Not sure I follow. What do you mean by "make this table"?
@@chandoo_ sir I was asking that how did u make this table where u applied concatenate and transpose function
Hi
Hope this email finds you well.
How can I join the below numbers.
Raw data
In the 1st column - 1
In the 2nd column - 234
In the 3rd column - 11
Required output
001-000234-011
Thanks for the shout out, Chandoo! This made my day.
Excellent. Thanks for providing such an information in one go.