In this article:

May 8, 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!

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:

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

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**.

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.

**=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

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

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.

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

- Select the whole range
- Right-click
- Select
**Hide column**among the options

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

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.

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

- Select the whole range
- Right-click
- Select
**Hide column**among the options

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

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

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.

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

- Select the whole range
- Right-click
- Select
**Hide column**among the options

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:

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

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:

- The first FALSE to tell Google Sheets that the delimiter has multiple characters; and
- 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.

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

- Select the whole range
- Right-click
- Select
**Hide column**among the options

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.

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.

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.

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

- Select the whole range
- Right-click
- 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.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->