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.
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.
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.
FORMULA: =split(A2," ") or =split(A2," ",true,true)
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.
Now imagine we have a grocery list in a single cell. Below are the steps to separate this by item:
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.
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.
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!
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.
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.
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:
If you enjoyed this article, you might also like our article on how to split cells in Google Sheets or our article on how to set up Google Sheets split text to columns feature.
If you want to learn how to automatically send an email from Google Sheets, we also suggest checking out our easy-to-follow guide!
