Ask Your Question

split long column in excel

asked 2020-09-08 11:00:04 +0100

mohamedzaki636 gravatar image

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

edit retag flag offensive close merge delete


split long column in excel

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

Opaque gravatar imageOpaque ( 2020-09-08 11:53:59 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-09-08 11:36:34 +0100

Opaque gravatar image

updated 2020-09-08 11:56:10 +0100


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.

edit flag offensive delete link more


thank you very much this is very helpful

mohamedzaki636 gravatar imagemohamedzaki636 ( 2020-09-08 12:11:05 +0100 )edit

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

mohamedzaki636 gravatar imagemohamedzaki636 ( 2020-09-09 18:16:17 +0100 )edit

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

Opaque gravatar imageOpaque ( 2020-09-10 10:10:11 +0100 )edit

yes that what i means thanks again

mohamedzaki636 gravatar imagemohamedzaki636 ( 2020-09-10 23:21:49 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-09-08 11:00:04 +0100

Seen: 45 times

Last updated: Sep 08 '20