The SPLIT Formula in Google Sheets allows you to literally split your data from a single cell and partition them into columns.
Imagine you have a huge database of company locations in a single column with both the city and country in each cell and you want to optimize your sheets to have a single column for the city and another one for the country, SPLIT in Google Sheets might just be your best friend for the task. Let’s go through the formula below.
Syntax
The SPLIT Function in Google Sheets has the following syntax:
=SPLIT(text,delimiter, [split_by_each], [remove_empty_text])
Breaking down the syntax of this function, these are its main parts:
-TEXT - this is your content (which could be names, addresses, dates and more!). You can reference this by its cell number.
-DELIMITER - this is a character/combination of characters like a period, space, comma, or words that you’d be using to divide your TEXT. Remember to enclose this with quotation marks (“”) when using or referencing actual characters or words in this segment (eg: “.”, “/”, “word”) and not cell numbers.
-[split_by_each] - is an optional argument in the function that considers each individual character of your delimiter as a separator by default. If you wish to use your chosen characters as a whole (for combinations of characters), set this to FALSE.
-[remove_empty_text] - is another optional argument that tells google sheets to remove empty content within two predetermined delimiters from the final output. In default, this is set to TRUE. If you want your final output to include the empty spaces, set your function’s fourth argument to FALSE.
SPLIT Function Examples
Now, let’s use the SPLIT Function for actual scenarios. We’ll have an example on the basic uses and the main parts of the syntax of our function.
How to use SPLIT Function to divide into columns
Step 1: Identify what separates your text (a singular space)
Step 2: Go to the cell where you want your output to land (B2).
Step 3: Use the following syntax on the identified cell:
FORMULA: =split(A2," ") or =split(A2," ",true,true)
Step 4: Copy the formula down or autofill to the other cells in Column B
Helpful Note: Make sure there is enough space for your outputs to propagate to the right side of the cell where your function is. Not having enough cells at the right of the SPLIT Function will cause a #REF! error.
Separating with a Combination of Delimiters
Now imagine we have a grocery list in a single cell. Below are the steps to separate this by item:
Step 1: Identify the repeating delimiter between your items
Step 2: Observe that there is a character from your specified delimiter (the space) that appears on other instances without the comma. Because of this, we will set the third argument to FALSE.
Step 3: In a new cell (B2) input the following formula
FORMULA =split(A1,", ",FALSE)
Helpful note: Failure to set the third argument to false will cause Google Sheets to put a separation at every instance where there is either a space OR a comma (see output of this scenario below). Setting this to false will only divide at instances where BOTH are present together.
Splitting Cells With Or Without Blanks
Now imagine you have a list of addresses with the street, city and state. However, some addresses are missing city details and when you use the split function, Google Sheets keeps removing the spaces. Here are the steps to split your cells with blanks:
Step 1: Identify your delimiter
Step 2: In B2, Write your function with the third and fourth arguments set to FALSE =split(A2,", ",FALSE,FALSE)
Step 3: Drag or copy to other cells in column B
Helpful notes: When the fourth argument is set to default/true, the output will look like rows 2-5 below. Setting this to true will be useful in instances where you want to eliminate empty spaces between your columns. Also, make sure that your delimiters are consistently a combination of your specified characters. If this is not the case, the steps above will not work and your output will look like rows 7-10.
Frequently Asked Questions on the SPLIT Function
Is the SPLIT Function in Google Sheets Case sensitive?
Yes, the Split Function in Google Sheets is very case sensitive and will separate on the exact delimiter you have placed in the split function. For example your delimiter is the word “one”, all instances where One is seen will not be separated. Also, since it is case sensitive, remember to always enclose your separator with quotation marks (“”) when not referencing direct content and other cells!
How many cells do I need in my Google Sheets?
This depends on your content and how you want it separated. If you have long form content, it would be best to keep all cells at the right of your formula empty for the formula overflow. This formula occupies the space available at the right side of it.
Does Google Sheets allow using SPLIT with ARRAYFORMULA?
Yes, you can use the SPLIT function inside your ARRAYFORMULA or use your ARRAYFORMULA inside the SPLIT function. Just be careful to always include your selected range and follow syntax correctly. Also remember to have enough spaces for your ARRAYFORMULA to propagate its outputs.
Is there an easier way to do this on Google Sheets?
Absolutely! You can separate text into columns using a built-in tool in Google Sheets. Click on the article below to learn how to use the text to columns tool:
Recommended articles
More resources
