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
First time here? Check out the FAQ!
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
Hello,
Assumptions (for the description)
A1:A5000
(5000 rows)5000 / 10 = 500
rows)B1
(hence in total is B1:K500
Perform the follow actions:
B1
B1
: =OFFSET($A$1;(COLUMN()-1)+(ROW()-1)*10-1;0)
B1 -> K1
B1:K500
into the Name Box (leftmost in the formula bar) and press ENTER
(the range B1:K500
gets selected)CTRL+D
B
through D
and Data -> Calculate -> Formula to Value
Please note The formula needs to be adapted if
B1
and/or A1
and/or 10
(i.e. in any case the assumptions are not fulfilled)
Hope that helps.
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).
Asked: 2020-09-08 11:00:04 +0100
Seen: 45 times
Last updated: Sep 08 '20
Why can't Calc 3.5 open Excel XML files? [closed]
Is there an alternative to Oracle Crystal Ball for LibreOffice Calc? [closed]
Missing Toolbar text in all LibreOffice (Base, Writer, Calc, Draw, etc) [closed]
Can I import a MS Excel file into Base on Linux? [closed]
is there something like quickbooks in libre office? [closed]
How do I export a chart in an image format from LibreOffice Calc? [closed]
Are there plans for a "papercut" project for libreoffice [closed]
Is it normal for Calc goal seek to take very long? [closed]
Please refine "Search" in Calc - implement functions in Gnumeric [closed]
I don't know how to do in Excel, but there is a solution for Calc - see below.