I have two different methods to split a column into multiple n columns in Google Sheets.

I’ll explain one method below. The other you can learn in a different tutorial later.

You may find it easy to enter your data in a single column, especially sequential numbers, dates, month names, etc.

If you want to convert that data into multiple columns, you can follow my tip.

Let’s learn how to split a column into multiple N columns in Google Sheets.

Before starting, you must know how the split output will appear.

Why is it important?

It’s because you can split a column into multiple equal-sized columns that return two different types of outputs – horizontal and vertical.

Here I am following the below approach.

I have split the month in text format (the formula will work with numbers also) in column A into different columns.

Instead of Jan, Feb, Mar in one column, the formula moves them into rows (multiple columns).

I think it is more useful when you split month names, month numbers, dates, sequential numbers, product codes, years, etc., from one column into multiple columns.

Are you impressed?

Then read on to get the formula and tips.

How to Split a Column into Multiple Columns in Google Sheets

I have used the MOD function to mark the Nth position to split. See how we can do that.

It is a step-by-step approach.

If you see the formula first, you would be under the impression that it is difficult to digest.

MOD to Mark the Row to Split into Column

In the following formula, cell B2 represents the nth position.

Assume cell B2 contains the number 5, which means I want five columns after the split.

=ArrayFormula(MOD(row(A3:A14)-row(A2),B2))

In this, the ROW formula =ArrayFormula(row(A3:A14)-row(A2)) returns the numbers 1 to 12.

These are the dividend in the MOD function, and the divisor is, of course, the number 5, which is in cell B2.

The MOD function returns the remainder after a division.

So, the numbers 5, 10 in the ROW formula output will become 0. You can see the screenshot above to understand it.

MOD Output as Logical Expression in IF

I am going to use the above output in an IF logical test as below.

=ArrayFormula(if(MOD(row(A3:A14)-row(A2),B2)=0,"|",""))

It will convert 0s in the output to a Pipe symbol. All the other numbers (remainders) will become blank.

Then let me combine the above output with the actual data in column A.

I have additionally placed a comma in between as a separator to each value in column A.

=ArrayFormula(A3:A14&","&if(MOD(row(A3:A14)-row(A2),B2)=0,"|",""))

The Role of QUERY to Split a Column into Multiple N Columns

In the above steps, we could mark the column position to split.

We can split the above formula output using the SPLIT function. The delimiter in the SPLIT will be the Pipe symbol.

But before that, we must join all the values using a QUERY!

Usually, Google Sheets users use the TEXTJOIN function here, but I am using QUERY.

Because it will work more efficiently if the number of rows to split into columns are large.

I have already elaborated on that topic in detail – The Flexible Array Formula to Join Columns in Google Sheets.

Formula:

=ArrayFormula(Query(A3:A14&","&if(MOD(row(A3:A14)-row(A2),B2)=0,"|",""),,9^9))

Output:

Jan, Feb, Mar, Apr, May,| Jun, Jul, Aug, Sep, Oct,| Nov, Dec,

We are only three steps away from splitting a column in Google Sheets into multiple N columns.

Sheets Formula to Split a Column into Multiple N Columns

Here I will apply two SPLIT and one TRANSPOSE. Didn’t get it?

First, I will split the above formula output using the Pipe as the delimiter. Then Transpose that output.

One more Split using Comma as the delimiter will generate our final output.

Here is that final formula.

=ArrayFormula(TRIM(split(transpose(SPLIT(Query(A3:A14&","&if(MOD(row(A3:A14)-row(A2),B2)=0,"|",""),,9^9),"|")),",")))

I have included TRIM to remove any extra spaces in the output. That’s all.

If you want to split the numbers from 1 to 500 in one column to 20 columns, use the below formula.

As a side note, if you use TEXTJOIN instead of QUERY to join values, the formula won’t work with these many rows of values.

Make the below changes in the formula:

Replace A3:A14 with row(A1:A500)

Then change row(A3:A14)-row(A2) to row(A1:A500)

If you want, you can specify the N, which is 20 here within the formula.

The finished formula would be as follows. I have removed the TRIM function here as the values are numbers.

=ArrayFormula(split(transpose(split(query(row(A1:A500)&","&if(MOD(row(A1:A500),20)=0,"|",""),,9^9),"|")),","))

Cool, right? But to generate numbers as above, you can simply use the below SEQUENCE formula.

=sequence(25,20)

Here is the second method – Move Single Column to Multiple Columns Using Hlookup in Google Sheets.