Which of the following functions Divides text around a specified character or string and puts each fragment of text into a separate cell in the row?

The SPLIT function in Google Sheets is useful to quickly and easily separate text into columns based on a delimiter.

The 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:

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.
• SPLIT is our function. We will have to add at least the required arguments text and delimiter into it for it to work.
• text is the text we want to divide.
• delimiter is 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_each to FALSE.
• split_by_each is 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_text is 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

1. When you write your SPLIT function 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!
2. The delimiter can 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 delimiter is the ultimate way to separate words from each other.
3. The results themselves don’t include the delimiter character or characters to split the string around.
4. 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 SPLIT function 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:

• The text we 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:

• The text is in cell A3.
• The delimiter is in cell B3. The trick is that the delimiter is 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 split_by_each.

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 split_by_each FALSE.

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”.

Let’s see how to write your own SPLIT function in Google Sheets step-by-step.

1. To start, make sure that you have enough empty cells to the right where you want to spread your results.

1. Now click on the cell where you want to write your function. For the purposes of this guide, I will be selecting C2.

1. Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘split‘ (or ‘SPLIT‘).

1. 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.

1. 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 “,”.

1. Afterwards, set the optional split_by_each argument 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_each is TRUE if you don’t write anything.

1. 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_cells FALSE, and because I’m using the fourth argument, I have to add the third one as well, even if it’s TRUE.

1. 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. 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Which of the following SQL functions can data analysts use to clean string variables select all that apply 1 point?

How can spreadsheet functions help you clean data more efficiently and effectively?

Which of the following tasks can data analysts do using both spreadsheets and SQL Select all that apply?

What spreadsheet function enables you to put each of the colors in Column G into a new separate cell?