In this article:

How to Use the SPLIT Function in Google Sheets (with examples!)

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 

List of full names sample

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

Example: Separating Names

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: 

Grocery list items

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) 

Output with third argument set to 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. 

Output of Grocery List Separation with Third Argument in Default

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 

Sample with fourth argument set to false

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. 

Common Errors with the Split Function

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:

Use our renewal management software to easily manage renewals from your spreadsheet in just a few clicks. 

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 detailed guide.

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!