split long column in excel

edit

Hello,

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

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.