Split a column of values with Excel

The tip presented here will allow you to split a column of values into multiple columns (without modifying the original column), for example to distribute a list of names across 4 columns.

This formula works even if the length of the data column is variable.

Note that although the formula may seem 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 in column A into 4 columns:

Copy the following formula:

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

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

To get the 4 columns:

excel formula split column into 4 columns

If you'd rather 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,0),MAP(SEQUENCE(rc,c),LAMBDA(v,IF(v<=r,INDEX(a,v),""))))

To then obtain:

excel formula split column into 4 columns row by

If you want to better understand the meaning of the variables in LET, a represents the range containing the values (you can define a whole column if you wish), 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 (maximum).

Custom function

To simplify its use, you can create a custom function to then use it very easily:

=SPLIT_COLUMN(A:A,4)

excel function split column

In this case, create a new custom function, enter SPLIT_COLUMN in the first field, then the following formula in the last field:

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

To then be able to use a much simpler formula:

excel custom function split column

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

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

There is also an Excel function named WRAPROWS that can do this job row by row, however, this function is less convenient because it does not allow entering an entire column for a dynamic result and displays 0 or #N/A in the last row if it is incomplete.

Note: requires Office 365 (or a sufficiently recent version of Excel).