Hi guys,
I have an Excel file with one row and more than 2000 columns. Can someone explain me how can I turn this into one single column/cell? Basically turn a table 1X2000 to a 1X1.
Thanks in advance.
Hi guys,
I have an Excel file with one row and more than 2000 columns. Can someone explain me how can I turn this into one single column/cell? Basically turn a table 1X2000 to a 1X1.
Thanks in advance.
You can merge the data into one singe cell, using concat funtion?!
Regards,
Thangavel D
Appreciate the help? CLICK *
I thought about that but the question is that I have a lot of cells to merge, it is a pain in the *** to write all this code.
Try this UDF:
in cell B1 : =ConcatenateRange(A1:BXX1)
Please Login or Register to view this content.
Do you have Power Query (Get & Transform on the Data ribbon)? If so, load the data into the PQ editor and use the transform function to pivot the data (or the unpivot function).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
=concat(A1:XFD1)
this will concatenate everything into one cell. Change the column ranges accordingly as this is technically ~16,000 columns right now
you could also use TEXTJOIN formula.
=TEXTJOIN("",TRUE,1:1)
the "" means you dont want to add a delimiter. You could always add a comma or space here and it would put this inbetween all the concatenated cells.
the TRUE means it is to ignore blank cells. False would include blank cells.
and the 1:1 means its grabbing the entire 1st row.
Last edited by dosydos; 12-20-2018 at 10:50 AM.
Thank you guys.
i just edited my post#6 above. take a look. I added a new formula that might help out a lot as you can add a delimiter if you need too.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks