Split 1 column into multiple columns with up to 500 cells each

Question

In Google Sheets, I have a column that is arbitrarily long.

I want to split this column into separate columns of length 500.

How can I do this?

Some things I've been thinking may be involved in a solution:

• TRANSPOSE
• ARRAY_CONSTRAIN

Answer 1

Update 2023

With new Google Sheets functions whis became much easier:

=WRAPROWS(A2:A15,5,"")

https://support.google.com/docs/answer/13184285?hl=en

---

Arrayformula, an example for number 5, change to 500.

=ArrayFormula(IFERROR( vlookup( (TRANSPOSE(ROW(INDIRECT("a1:a"&ROUNDUP(COUNTA(A:A)/5))))-1)*5 + ROW(INDIRECT("a1:a"&5)), {ROW(A:A),A:A},2,) ))

• ROUNDUP(COUNTA(A:A)/5 the number of columns. Up because the last column may contain less than N rows.
• TRANSPOSE(...)*5 + ROW(INDIRECT("a1:a"&5)) to get matrix of numbers.

Matrix:

1   6   11  16
2   7   12  17
3   8   13  18
4   9   14  19
5   10  15  20

• {ROW(A:A),A:A} to get the number of a row and value to return

• vlookup to return a value

• IFERROR to show "" if error.