Split a column of values with Google Sheets

This tip allows you to split a column of values into multiple columns (without modifying the original column) and works even if the data column length is variable.

Note that although the formula might look complex here, you don’t need to understand it to be able to use it.

Example of use

The goal here is to split the values from column A into 4 columns:

Simply copy the following formula:

=LET(a,A:A,c,4,r,COUNTA(a),rc,ROUNDUP(r/c),MAP(TRANSPOSE(SEQUENCE(c,rc)),LAMBDA(v,IF(v<=r,INDEX(a,v),""))))

And, if necessary, replace the data range (A:A) and the desired number of columns (4) in the formula.

To obtain 4 columns:

google sheets formula split column into 4 columns

If instead you want to distribute the values row by row (instead of column by column), use this formula:

=LET(a,A:A,c,4,r,COUNTA(a),rc,ROUNDUP(r/c),MAP(SEQUENCE(rc,c),LAMBDA(v,IF(v<=r,INDEX(a,v),""))))

To then obtain:

google sheets formula split column into 4 columns row by

If you want to better understand the meaning of the variables used in LET: a represents the range containing the values (you can define an entire column if desired), c represents the desired number of columns, r represents the number of value rows in the range, and rc represents the number of rows per column (at most).

Named function

To simplify its use, you can create a named function to use it very easily afterward:

=SPLIT_COLUMN(A:A,4)

In this case, simply add a new named function, enter the 2 arguments range and columns, then the following formula:

=LET(r,COUNTA(range),rc,ROUNDUP(r/columns),MAP(TRANSPOSE(SEQUENCE(columns,rc)),LAMBDA(v,IF(v<=r,INDEX(range,v),""))))

To then be able to use a simplified formula:

google sheets custom function split column

Or enter this formula in the field for the row-by-row version:

=LET(r,COUNTA(range),rc,ROUNDUP(r/columns),MAP(SEQUENCE(rc,columns),LAMBDA(v,IF(v<=r,INDEX(range,v),""))))

There is also a Google Sheets function named WRAPROWS that can do this job, but with a row-by-row result only, and it doesn’t allow entering an entire column directly (ignoring empty cells below the data) for a dynamic result.