How to split a column with very big number into a smaller pieces-CodePudding

I have a big data around 189.000 rows in 1 column. I want to cut it into 5.000 pieces. So I will have 5.000 data in every column. Example like 5000 in A, B, C, D .... Is there any way to do this in excel?

CodePudding user response：

You could do the following:

In cell B1, enter the formula =A5001.

In cell C1, enter the formula =A10001.

In cell D1, enter the formula =A15001.

etc. up to however many columns you need, say Z1.

Copy the block of cells with the new formulas, and insert them into the range B2:Z5000.

Now select the complete columns B to Z and copy them. Leave them selected and select "insert values" from the start menu. That will overwrite the formulas with the actual values themselves, without changing the appearance of the sheet.

Verify that the values themselves are really in the cells, not formulas, and that you have all the data you need.

Now you can delete all the rows in column A below 5000.