La fonction SPLIT in Google Sheets allows you to split your data from a single cell and distribute it into separate columns. Imagine having a massive database of company locations in a single column, with both the city and country in each cell, and wanting to optimize your spreadsheets by having a column for city and another for country. The SPLIT function in Google Sheets could easily become your best friend for this task. Let’s explore 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, here are its main parts:
-
TEXT – this is your content (which can be names, addresses, dates, etc.!). You can refer to it by its cell number.
-
DELIMITER – this is a character or combination of characters such as a period, space, comma, or words that you’ll use to divide your TEXT. Remember to enclose it in quotation marks (“”), whether using it or referring to actual characters or words in this segment (e.g., “.”, “/”, “word”) and not cell numbers.
-
[SPLIT_BY_EACH] – This is an optional argument in the function that treats each individual character of your delimiter as a separator by default. If you want to use your chosen characters as a whole (for character combinations), set it to FALSE.
-
[REMOVE_EMPTY_TEXT] – This is another optional argument that tells Google Sheets to remove empty content between the two predefined delimiters in the final output. By default, this is set to TRUE. If you want your final output to include empty spaces, set the fourth argument of your function to FALSE.
Examples of the SPLIT Function
Now, let’s use the SPLIT function for practical scenarios. We’ll have an example on basic uses and the main parts of the syntax of our function.
How to Use the SPLIT Function to Split into Columns
- Identify what separates your text (a single space).
- Go to the cell where you want your output to appear (B2).
- Use the following formula in the identified cell:
=split(A2, " ")or=split(A2, " ", true, true). - Copy the formula downwards or auto-fill the other cells in column B.
Useful Note: Make sure there is enough space for your outputs to spill to the right of the cell where your function resides. Not having enough cells to the right of the SPLIT function will result in a #REF! error.
Splitting with a Combination of Delimiters
Now, imagine you have a grocery list in a single cell. Here are the steps to separate it by item:
- Identify the recurring delimiter between your items.
- Notice that there is one specified delimiter character (space) that appears in other instances without the comma. Because of this, we’ll set the third argument to FALSE.
- In a new cell (B2), enter the following formula:
=split(A1, ", ", FALSE).
Useful Note: Not setting the third argument to FALSE will cause Google Sheets to split at every instance where either a space or a comma appears. Setting it to FALSE will only split instances where BOTH are present together.
Splitting Cells with or without Empty Spaces
Now, imagine you have a list of addresses with street, city, and state. However, some addresses don’t have details about the city, and when you use the SPLIT function, Google Sheets continues to remove spaces. Here are the steps to split your cells with empty spaces:
- Identify your delimiter.
- In B2, write your function with the third and fourth arguments set to FALSE:
=split(A2, ", ", FALSE, FALSE). - Drag or copy it to the other cells in column B.
Useful Notes: When the fourth argument is set to default/true, the output will look like lines 2-5 below. Setting it to true will be useful in cases where you want to remove empty spaces between your columns. Also, make sure your delimiters are always a combination of your specified characters. Otherwise, the above steps won’t work, and your output will look like lines 7-10.
Frequently Asked Questions about 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 split based on the exact delimiter you specified in the split function. For example, if your delimiter is the word “one,” any instances where “One” is visible will not be split. Additionally, because it’s case-sensitive, always remember to enclose your delimiter in quotes (“) when not referencing direct content and other cells!
How many cells do I need in my Google spreadsheets?
It depends on your content and how you want to split it. If you have long-form content, it’s best to keep all cells to the right of your formula empty for formula overflow. This formula takes up available space to its right.
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 make sure you always include your selected range and correctly follow the syntax. Also, ensure that you have enough space for your ARRAYFORMULA to propagate its outputs.
Is there an easier way to do this in Google Sheets?
Absolutely! You can split text into columns using a built-in tool in Google Sheets. Click the article below to learn about using the Text to Columns tool:
Use our renewal management software to easily manage renewals from your spreadsheet with just a few clicks.
If you liked this article, you might also enjoy our article on How to Split Cells in Google Sheets or our article on How to Use the “Text to Columns” Function in Google Sheets. And if you want to learn how to automatically send an email from Google Sheets, we also recommend checking out our detailed guide.
