Split long column in excel

i have a column that contain 5000 cell is there any speed way to split this column to 10 other column and not doing this manual

split long column in excel

I don’t know how to do in Excel, but there is a solution for Calc - see below.


Assumptions (for the description)

  • Current data are in range A1:A5000 (5000 rows)
  • You want to get the values into 10 columns (resulting in 5000 / 10 = 500 rows)
  • Target range starts in B1 (hence in total is B1:K500

Perform the follow actions:

  • Move to cell B1
  • Add the following formula into B1: =OFFSET($A$1;(COLUMN()-1)+(ROW()-1)*10-1;0)
  • Drag the formula from B1 -> K1
  • Type B1:K500 into the Name Box (leftmost in the formula bar) and press ENTER (the range B1:K500 gets selected)
  • Type CTRL+D
  • Optional Select columns B through D and Data -> Calculate -> Formula to Value

image description

Please note The formula needs to be adapted if

  • the starting cell of the target range is not B1 and/or
  • the first cell of the current data is not A1 and/or
  • the number of columns the data should be distributed to is not 10

(i.e. in any case the assumptions are not fulfilled)

Hope that helps.

thank you very much this is very helpful

This way to divide it horizontally, is there a way to solve the issue vertically

What do you mean by that (1 through 10 in B1:B10; 11 through 20 in C1:C10 and so on)? If - yes, that’s of course possible, but keep in mind that due to a limit of 1024 columns (16k limit in version 7 still an experimental feature), you’ll reach the limit pretty soon (Formula: =OFFSET($A$1;(COLUMN()-2)*10+(ROW())-1;0) and range: B1:SG10 (add the formula to B1,copy [CTRL+C] the formula, select the range via Name Box and paste [CTRL+V] the formula into the range).

yes that what i means
thanks again