How to Split Columns in Google Sheets (Best Method 2023)
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:
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.
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.
Step 3: Hide original range (optional)
You can hide the original range to keep your sheet neat:
Select the whole range
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: