How to Split Columns in Google Sheets (Best Method 2024)

In this article we cover how to divide columns in Google Sheets in two ways: through the SPLIT function and through the Unmerge command. We will have several examples to demonstrate how to use these ways to divide columns. Start scrolling!

Split Columns using Unmerge

‍

You can use the Unmerge command in Google Sheets to divide columns. This is useful when the column you want to divide is previously merged. There are three ways to access this:

Method 1: Format -> Merge cells -> Unmerge

‍

Method 2: Select columns to divide -> Click Merge cells icon

The Merge cells icon will appear colored green; clicking it will unmerge the merged cells, placing the value to the first column.

‍

Method 3: Unmerge from Merge cells icon in the toolbar

Merging options are also available through the dropdown box along the main toolbar. Click the arrow besides the Merge cells icon, and then select Unmerge.

Split Columns Using the SPLIT Function

‍

If the column you want to split is not previously merged, then the three methods of accessing the Unmerge command above will not work. Fortunately, there is a workaround: the SPLIT function. This function will perfectly work for columns that contain special characters for separating entries called delimiters. 

‍

In the succeeding examples, we will combine the SPLIT function with ISBLANK and ARRAYFORMULA. 

Syntax

‍

=arrayformula(if(isblank(range)=false,split(range,delimiter),""))

‍

Where

Range is the range of the column to divide; ex. A:A if you want to divide column A

delimiter is the character to be used to divide the columns, enclosed in double quotes

‍

Split Column With Commas

Step 1: Identify the range and delimiter

The range to divide is column A and the delimiter is the comma.

‍

Step 2: Apply SPLIT(range, delimiter)

‍

Given the range and the delimiter, the formula is now: 

‍

=arrayformula(if(isblank(A:A)=false,split(A:A,","),""))

‍

Voila! You are done. To keep your sheet neat, however, you can do the next step. 

Step 3: Hide original range (optional)

You can hide the original range to keep your sheet neat:

1. Select the whole range
2. Right-click
3. Select Hide column among the options

Split Column With Spaces

Step 1: Identify the range and delimiter

The range to divide is column A and the delimiter is a space.

Step 2: Apply SPLIT(range, delimiter)

‍

Given the range and the delimiter, the formula is now: 

‍

=arrayformula(if(isblank(A:A)=false,split(A:A," "),""))

Voila! You are done. To keep your sheet neat, however, you can do the next step. 

Step 3: Hide original range (optional)

You can hide the original range to keep your sheet neat:

1. Select the whole range
2. Right-click
3. Select Hide column among the options

Split Column If Delimiter has Several Characters

Sometimes the delimiter used by your source contains several characters or special characters. Here are the steps to split the column:

‍

Step 1: Identify the range and delimiter

The range to divide is column A and the delimiter is the character “&%”.

‍

Step 2: Apply SPLIT(range, delimiter)

‍

Given the range and the delimiter, the formula is now: 

=arrayformula(if(isblank(A:A)=false,split(A:A,"&%"),""))

‍

Voila! You are done. To keep your sheet neat, however, you can do the next step. 

Step 3: Hide original range (optional)

You can hide the original range to keep your sheet neat:

1. Select the whole range
2. Right-click
3. Select Hide column among the options

Split Column If There are Missing Entries

‍

By default, the missing entries are not given their own cells by the SPLIT function. However, you can set the formula to give them cells, keeping the values aligned correctly. Here are the steps:

Step 1: Identify the range and delimiter

‍

The range to divide is column A and the delimiter is the character “&%"

Step 2: Apply SPLIT(range, delimiter)

‍

Given the range and the delimiter, the formula is now: 

‍

=arrayformula(if(isblank(A:A)=false,split(A:A,"&%",FALSE,FALSE),""))

‍

The two FALSE correspond to the options in the SPLIT function:

1. The first FALSE to tell Google Sheets that the delimiter has multiple characters; and
2. The second FALSE to tell Google Sheets to interpret consecutive delimiters as containing empty cells, which is what we need.

‍

Voila! You are done. To keep your sheet neat, however, you can do the next step. 

‍

Step 3: Hide original range (optional)

‍

You can hide the original range to keep your sheet neat:

1. Select the whole range
2. Right-click
3. Select Hide column among the options

Split Only a Portion Of Column

One problem about the formulas with ARRAYFORMULA in the previous examples is that it throws an error when stray values appear in the range it is supposed to work. This happens even if its corresponding cell in the source range does not have any entry:

The solution is simple, however.

Step 1: Identify the range and delimiter

‍

The range to divide is column A and the delimiter is the comma. This time, we limit the range to the first five rows; thus the range is set to A1:A5. 

Step 2: Apply SPLIT(range, delimiter)

‍

Given the range and the delimiter, the formula is now: 

‍

=arrayformula(if(isblank(A1:A5)=false,split(A1:A5,","),""))

Voila! You are done. To keep your sheet neat, however, you can do the next step. 

Step 3: Hide original range (optional)

‍

You can hide the original range to keep your sheet neat:

1. Select the whole range
2. Right-click
3. Select Hide column among the options

‍

If you enjoyed this article, you might also like our article on how to split the first and last name in Google Sheets or our article on how to split text in Google Sheets. 

‍

If you want to learn how to send Google Sheets email notifications, we also suggest checking out our detailed guide.