+ Reply to Thread
Results 1 to 9 of 9

Convert 2000 columns to a single one

  1. #1
    Registered User
    Join Date
    09-27-2018
    Location
    lisbon
    MS-Off Ver
    2013
    Posts
    8

    Convert 2000 columns to a single one

    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.

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Convert 2000 columns to a single one

    You can merge the data into one singe cell, using concat funtion?!
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Registered User
    Join Date
    09-27-2018
    Location
    lisbon
    MS-Off Ver
    2013
    Posts
    8

    Re: Convert 2000 columns to a single one

    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.

  4. #4
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Convert 2000 columns to a single one

    Try this UDF:
    in cell B1 : =ConcatenateRange(A1:BXX1)

    Please Login or Register  to view this content.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Convert 2000 columns to a single one

    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.

  6. #6
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Convert 2000 columns to a single one

    =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.

  7. #7
    Registered User
    Join Date
    09-27-2018
    Location
    lisbon
    MS-Off Ver
    2013
    Posts
    8

    Re: Convert 2000 columns to a single one

    Thank you guys.

  8. #8
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Convert 2000 columns to a single one

    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.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Convert 2000 columns to a single one

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to transpose / convert columns and rows into single column?
    By chief_abound in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-09-2015, 08:04 PM
  2. Convert Single Column, Multi-Page List to Two Columns to Save Paper When Printing
    By thegnord in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2015, 08:21 AM
  3. Replies: 6
    Last Post: 12-06-2012, 11:05 AM
  4. Convert Multiple Columns to Single Row?
    By AboveBeyond in forum Excel General
    Replies: 4
    Last Post: 08-27-2012, 05:07 PM
  5. [SOLVED] Convert data in multiple columns to single column
    By rkoffy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2012, 08:03 AM
  6. convert single column to multiple columns
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-07-2007, 12:40 PM
  7. Convert one row and 50 columns of info to a single printable page
    By d5657 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1