The SPLIT function in Google Sheets is useful to quickly and easily separate text into columns based on a delimiter.
Table of Contents
SPLIT function does this simply by splitting the content of the cell at the delimiter.
The delimiter is a character (or characters) you choose by which you want to cut the text.
Let’s take an example.
Say you have a list of names in a spreadsheet, and you want to separate them by first and last names into two columns.
So how do we do that?
Simple. We can use the
SPLIT function to separate the contents of the cells. We just have to select the character by which we split the text into first and last names.
Let’s go straight into real examples where we will deal with actual values and see how we can write our own SPLIT function in Google Sheets.
The Anatomy of the SPLIT Function
The syntax of the function specifies how we should work with it. The syntax of the
SPLIT function is as follows:
=SPLIT(text, delimiter, split_by_each, remove_empty_text)
Let’s understand what the
SPLIT function and its attributes mean:
=the equal sign is how we start just about any function in Google Sheets.
SPLITis our function. We will have to add at least the required arguments
delimiterinto it for it to work.
textis the text we want to divide.
delimiteris the character or characters we use to split text. By default, each character in the delimiter is considered individually. For example, if the delimiter is “the”, then the text is divided around the characters “t”, “h”, and “e”. If you don’t want this behaviour, set
split_by_eachis an optional argument which is TRUE by default. As I mentioned, we can define whether we want to divide the text around each character of the delimiter or consider the delimiter as a whole.
remove_empty_textis another optional argument, TRUE by default. It indicates whether or not to remove empty text fragments from the split results. So when there are two or more delimiters next to each other, the default behavior is to them as one (if TRUE). If FALSE, empty cells are added between the consecutive delimiters.
⚠️ A Few Notes to Make Your SPLIT Function Work Perfectly
- When you write your
SPLITfunction in a cell, it puts each fragment into a separate cell in the row. So it returns the output across the cells on the right side. Make sure to keep plenty of empty columns to the right of your function!
delimitercan be one or more characters, even a word or a whole sentence. It can also be a white space character. Using white space as the
delimiteris the ultimate way to separate words from each other.
- The results themselves don’t include the delimiter character or characters to split the string around.
- Another way to split the content of a cell is possible by clicking on Data > Split text to columns. However, this solution has some limits. You can only split by a few common delimiters or detect the delimiter automatically. Moreover, it differs from the
SPLITfunction because this solution will overwrite your source data.
A Real Example of Using SPLIT Function
The below examples show how the
SPLIT function works.
As you can see, we have the original text values in column A, the delimiters in column B and we wrote each corresponding
SPLIT function in column C.
Let’s go through the examples to see the different ways to use the SPLIT function in Google Sheets.
Split Words Separated By Comma
Look at the first example in row 2. The original text has many words next to each other, separated by commas. We use the
SPLIT function to separate them into columns.
The function of cell C2 is as follows:
Here’s what this function does:
textwe want to separate is in cell A2.
- We set a
delimiter. This is obvious here, right? The commas separate the words from each other. So we use a comma as the delimiter in cell B2.
We used cell references here, but of course, you can also put the direct arguments in the function. So instead of referencing B2, you can put the comma in the function, but make sure to put it between quotation marks!
Split First Name and Last Name
The second example is the example I mentioned above. How do you separate the first name and the last name into two columns?
The function of cell C3 is as follows:
We used two references here:
textis in cell A3.
delimiteris in cell B3. The trick is that the
delimiteris a white space character, so you can’t see it immediately, but it’s there. The white space character is what separates the first name and the last name.
Super easy, right?
Using the link I have attached below, you can make a copy and try it for yourself:
Split By Each Character Or The Whole Delimiter
If you look into the syntax of the
SPLIT function, you can see that the third argument is
This is an optional argument which is TRUE by default.
Let’s have a look at how it works.
The third and fourth examples can give you an idea.
In both of the cases, we used the same text and delimiter. The text is the sentence “I have more information.” and the delimiter is “more”.
The function of cell C4 is as follows:
We didn’t specify
split_by_each here, so it is TRUE by default.
Meanwhile, the function of cell C5 is:
So we set
When it’s TRUE, the function considers every character in the delimiter as a separate delimiter. So it splits the text by every “m”, “o”, “r” and “e” characters, at every place where any of these letters are presents.
On the other hand, when split_by_each is FALSE, the delimiter is considered as one whole delimiter being “more”. That’s why the sentence is only separated where it has the word “more”.
How to Use SPLIT Function in Google Sheets
Let’s see how to write your own SPLIT function in Google Sheets step-by-step.
- To start, make sure that you have enough empty cells to the right where you want to spread your results.
- Now click on the cell where you want to write your function. For the purposes of this guide, I will be selecting C2.
- Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘
split‘ (or ‘
- After the opening bracket ‘(‘, you have to add the variables. The first variable is the text. You can reference a cell or type your text directly between quotation marks. I’m using the cell reference A2.
- Then, add the
delimiter. This should be the character or characters by which you separate your text. Now I’m using a comma character “,”. Again, you can write your delimiter in a cell and then reference that cell. Or if you prefer, put the delimiter directly in the function between quotation marks. I’m doing the latter one now and write “,”.
- Afterwards, set the optional
split_by_eachargument FALSE. Do this if your delimiter has more than one characters, and you want to consider it as one whole delimiter. Otherwise, the default value of
split_by_eachis TRUE if you don’t write anything.
- Then, you might want to set
remove_empty_cells. Write FALSE if you want to get empty cells in case the function finds two or more delimiters next to each other. If you don’t mind the function skipping the empty cells, leave it unchanged. I’m setting
remove_empty_cellsFALSE, and because I’m using the fourth argument, I have to add the third one as well, even if it’s TRUE.
- Finally, press the Enter key to add the closing brackets ‘)‘ and to complete the function. You can see the fragments spread into the columns to the right.
That’s it, good job! You can now use the
SPLIT function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂