Split column into range of rows

Question

I have a cell like this:

1
2
3
4
5
6
7
8
9
10

I would like to sort the data from one column to become 2 rows of 5 columns using Google Sheets.

Does someone know how to do that?

The result should be:

1 2 3 4 5
6 7 8 9 10

Answer 1

The solution should work regardless of how many values there are in the string inside the cell. Assume the cell is G4. Here goes the formula:

={array_constrain(split(G4, " "), 1, columns(split(G4, " "))/2);
  transpose(sort(transpose(array_constrain(transpose(sort(transpose(split(G4, " ")), 1, false)), 1, columns(split(G4, " "))/2))))
 }

Answer 2

There are many ways to do that. One is:

=TRANSPOSE({OFFSET(A1,,,COUNTA(A:A)/2),OFFSET(A1,COUNTA(A:A)/2,,COUNTA(A:A)/2)})

More general solution

To split the column into any given number of rows, use the formula:

=ArrayFormula(IFERROR(VLOOKUP( COLUMN(OFFSET(A1,,,1,ROUNDUP(COUNTA(A:A)/numRows)))+ ROUNDUP(COUNTA(A:A)/numRows)*(ROW(INDIRECT("a1:a"&numRows))-1), {ROW(A:A),A:A},2,)))

where numRows is a number of rows.

This:

COLUMN(OFFSET(A1,,,1,ROUNDUP(COUNTA(A:A)/numRows))) + ROUNDUP(COUNTA(A:A)/numRows)*(ROW(INDIRECT("a1:a"&numRows))-1)

generates indexes:

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

and VLOOKUP(indexes,{ROW(A:A),A:A},2,) is to return the result: