Split a Column into Multiple N Columns In Google Sheets

Have you ever found yourself in a situation where you need to convert data from a single column into multiple columns in Google Sheets? Well, I have not one, but two methods that can help you achieve that. Today, I’ll be sharing one of these methods with you. But don’t worry, I’ll save the other method for another tutorial.

We all know how easy it is to enter data in a single column, especially when dealing with sequential numbers, dates, or even month names. But what happens when you need to convert that data into multiple columns? Don’t worry, I’ve got a tip that can help.

Before we get started, let’s take a moment to understand the importance of how the split output will appear. This is crucial because you have the option to split a column into multiple equal-sized columns that can return two different types of outputs – horizontal and vertical.

Let me walk you through the approach I am going to explain, using a practical example. In the image below, I have split the month names in text format (the formula will work with numbers as well) from column A into different columns.

Instead of having “Jan, Feb, Mar” all in one column, the formula moves them into separate rows (multiple columns). But this approach is not limited to month names; it can also be used for splitting month numbers, dates, sequential numbers, product codes, years, and much more.

Now that I’ve piqued your interest, let’s dive into the formula and tips.

How to Split a Column into Multiple Columns in Google Sheets

The method I am going to share involves using the MOD function to mark the Nth position to split. Here’s how it works:

1. We start by using the ROW formula to generate a series of numbers from 1 to the number of rows in the column we want to split.

=ArrayFormula(ROW(A3:A14)-ROW(A2))

2. Next, we use the MOD function to determine the remainder after dividing the above series of numbers by the number of columns we want after the split. This will mark the rows where the split should occur.

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

3. To make the output more visually appealing, we can use the IF function to replace the marked rows with a pipe symbol (|) and leave the other rows blank.

=ArrayFormula(IF(MOD(ROW(A3:A14)-ROW(A2), B2) = 0, "|", ""))

4. Finally, we combine the above output with the actual data in column A, using a comma as a separator between each value.

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

The result is a column with the values separated by commas and the marked rows replaced by pipe symbols.

Now, we are very close to achieving our goal of splitting the column into multiple N columns. We just need to take a few more steps.

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

In the previous steps, we marked the column positions where the split should occur. Now, we can use the SPLIT function to split the formula output using the pipe symbol as the delimiter. But before that, we need to join all the values using a QUERY.

While most Google Sheets users would use the TEXTJOIN function for this purpose, I prefer using QUERY as it tends to work more efficiently, especially when dealing with a large number of rows. If you’re interested in learning more about joining columns in Google Sheets, check out my detailed article on “The Flexible Array Formula to Join Columns in Google Sheets”.

Here’s the formula to join the values and mark the split positions:

=ArrayFormula(QUERY(A3:A14 & "," & IF(MOD(ROW(A3:A14)-ROW(A2), B2) = 0, "|", ""),, 9^9))

The result will look something like this:

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

With just a few more steps, we can finally split the column into multiple N columns.

Sheets Formula to Split a Column into Multiple N Columns

In this final step, we will be applying two SPLIT functions and one TRANSPOSE. Here’s how it works:

1. We start by splitting the joined values using the pipe symbol as the delimiter.

=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(QUERY(A3:A14 & "," & IF(MOD(ROW(A3:A14)-ROW(A2), B2) = 0, "|", ""),, 9^9), "|")), ","))

2. We then transpose the above result to convert the rows into columns.

3. Finally, we split the transposed result using a comma as the delimiter. This will generate our final output, with each column containing the values from the original column.

And that’s it! We’ve successfully split a column into multiple N columns in Google Sheets.

So, the next time you find yourself in need of splitting a column into multiple columns, give this method a try. You’ll be amazed at how efficient and easy it is to use.

But wait, there’s more! If you’re feeling adventurous, you can also try generating numbers from 1 to 500 in one column and splitting them into 20 columns using the SEQUENCE formula. Here’s the formula for that:

=SEQUENCE(25, 20)

Now you have two powerful methods at your fingertips to split columns in Google Sheets. Feel free to experiment and adapt these formulas to suit your specific needs.

And remember, if you’re hungry for more tips and tricks like this, visit Crawlan.com, your go-to resource for all things Google Sheets.

Happy splitting!