Split cells themselves (not the data in them) into several columns-CodePudding

Suppose I have numbers from 1 to 1000 in column A. How do I split this column into 10 different columns ? Column A would be 1 to 100, column B would be 101 to 200; etc. Thanks in advance and sorry about my rusty english, not my native language.

CodePudding user response：

Assuming that what you actually want to do is take everything in A1:A1000 and break it into 10 columns, you can do it with an INDEX and multiplexed indexation to reshape it, but, as a lazy alternative, here is a general purpose reshaper that I made. It has a lot of overhead as a protection for error handling as well as some hooks for re-ordering, but it will do what you want.

With Office 365:

=LET( Matrix, A1:A1000,
      newRows, ,
      newCols, 10,

       rdR, ROWS( Matrix ),
       rdC, COLUMNS( Matrix ),
       rdCells, rdR * rdC,
       rdIR, SEQUENCE( 1, rdCells, 0 ),
       wrR, IF( newRows > 0, newRows,
                      IF( newCols > 0, ROUNDUP( rdCells/newCols, 0), rdR ) ),
       wrC, IF( newCols = 0, ROUNDUP( rdCells/wrR, 0), newCols ),
       wrCells, wrR * wrC,
       wrIR, SEQUENCE( wrR, wrC, 0 ),
       mux, INDEX( Matrix, rdIR/rdC   1, MOD( rdIR, rdC )   1 ),
       demux, INDEX( mux, wrIR/wrCells    1, wrIR   MOD( wrIR, wrC)*wrR - INT( wrIR/wrC ) * (wrC - 1) ),
       demux
     )

I put in the wrong write order - fixed now

You can enter whatever number of rows you want in newRows and whatever number of columns you want in newCols. If you leave either blank, it calculates the other.