How to use the SPLIT function in Google Sheets

Spreadsheets have become the go-to solution for storing and handling data. And spreadsheets are not limited to numbers — they provide all the tools you need to manage text-based data as well. In this article, you’ll learn about the SPLIT function in Google Sheets. It helps you separate a text string based on a delimiter.

This means you can divide text (that’s currently in one cell) into separate columns. Imagine that you have a list of contacts in your spreadsheet. Each cell contains the contact’s first name and surname and you want to divide this into two separate columns: first name and last name. The SPLIT function is the solution.

Here’s how it works:

Syntax

• text – the text or string that you want to split.
• delimiter – a single character or a group of characters that the function should consider for splitting the text. By default, each character within the delimiter is considered individually. For instance, if the delimiter is “one”, then the text is divided around the characters “o”, “n”, and “e”. If you do not want this behavior, you can set the split_by_each to FALSE. Note that the delimiter will not be included in the output of the function.
• split_by_each – [ OPTIONAL – TRUE by default ] – if you go with the default option, then the function considers each character within the delimiter string to split the text. If you set this to FALSE, then the function considers the delimiter as a whole. You can select this parameter by writing TRUE or FALSE, or by indicating 1 or 0, respectively.
• remove_empty_text – [ OPTIONAL – TRUE by default ] – this parameter indicates whether or not the function should remove empty text fragments from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cell values are added between consecutive delimiters. You can select this parameter by writing TRUE or FALSE, or by indicating 1 or 0, respectively.

How to use the SPLIT function in Google Sheets

The syntax looks a lot more complex than it actually is.

So, to get you comfortable with the function, I’ll show you some examples. Take a look at the screenshot below.

You can see that the results that I get are the input text, broken into pieces at the points where the delimiters are in the text. For the first example, the delimiter is “e”, so the result is one cell with all the text before the e’s in “Sheetgo”, and one cell with all the text after the e’s. I achieve that here by typing =Split(A2,B2).

The same is true with the other examples shown. In each, I specify a delimiter, and the function breaks the text into chunks that come before, in between, and after the delimiters. This can be extremely convenient for separating text data such as first and last names separated by a comma, as seen in the third example above.

Tip: Leave empty columns to the right

I have entered all the functions in column D. That’s because the SPLIT function in Google Sheets spreads its output across multiple cells towards the right, as is the case with all the examples. So, it is very important that you keep the cells, where you expect the data to flow, clear of any values. Otherwise, the function returns a #REF! error.

These first few examples show the SPLIT function in its most basic form. I didn’t specify the optional parameters, so they remained at their default value. Now I’ll demonstrate what happens when you add in the optional parameters split_by_each and remove_empty_text.

SPLIT function with split_by_each parameter

The split_by_each parameter is used when the delimiter is more than one character long. As you can see in the image below, changing this parameter can have a significant impact on the output of the function.

In this example, I am splitting the word “Countries” using the delimiter “one”.

The default value of the split_by_each parameter is 1 (TRUE), which tells the function to consider each character in the delimiter. So when you use this option, the function uses the “o”, “n”, and “e” as individual delimiters. You can see the result above: the text “Countries” is split into four parts.

The next example uses a split_by_each parameter value of 0 (FALSE), which tells the function to consider the delimiter text as one single delimiter of multiple characters. So in this case, the function will only split the text based on the delimiter of the entire word “one”. Of course, “one” does not appear in “Countries”, so it does not split the text at all. This goes to show the large difference in results that occurs when you change the split_by_each parameter when using a multi-character delimiter.

SPLIT function with remove_empty_text parameter

Now I’ll show an example using the remove_empty_text parameter. This parameter is used when you have consecutive delimiters in your text.

If the parameter is TRUE (default value) it will treat consecutive delimiters as a single delimiter; if FALSE, it will treat them as individual delimiters and add blank cells to the results.

You can see the difference here. I have a text string that contains consecutive delimiters — in this case, two commas beside each other.

In the default case, where the remove_empty_text parameter is 1 (TRUE), the function treats the consecutive commas as one and outputs the text split up as expected.

In the second case, however, I’ve set the remove_empty_text parameter to 0 (FALSE). Now, the function treats each comma as a separate delimiter, so it adds a blank cell to the output to reflect the “space” (even though there is no space character) in between the two commas.

SPLIT function: Things to remember

There are a few things to keep in mind when using the SPLIT function. First, always make sure you have enough blank cells to the right since the SPLIT function can output across multiple cells.

Second, remember that the delimiter will always be removed from the text. For example, if you want to separate text strings by commas, the comma will not appear in any of the output cells.

Lastly, you can also split text by selecting Data > Split text to columns. This method is less versatile, however, so you may be better off using the SPLIT function – play around with both options to see for yourself!