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:
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:
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:
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:
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:
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:
Use our legal deadline tracking software to easily track deadlines from your spreadsheet in just a few clicks.
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.
Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.
.png)
