Category: Spreadsheets

The SPLIT function in Google Sheets is used to divide a text string (or value) around a given delimiter, and output the separate pieces into their own cells.

SPLIT Function Examples

Let’s see a simple example using SPLIT to separate a list of names in cell A1:

This simple SPLIT formula will separate these names, using the comma as the separator:

The result is 5 cells, each containing a name. Note that one cell looks blank because the text string in cell A1 has two adjacent commas with a space between them. The “space” is interpreted in the same way as the names and contained in the output:

Now watch what happens if we include a space in the delimiter, i.e. ", "

The function splits on the comma "," and on the space " ", so the name “Mary Jo” split in two:

This is probably not the desired behavior.

The third argument is an optional TRUE or FALSE that determines whether SPLIT considers each individual character of the delimiter (TRUE) or only the full combination as the separator to use (FALSE).

In our example, adding FALSE ensures that it only considers the combined comma/space string as the delimiter:

And the output looks like this:

There is a fourth argument too, which is optional and takes a TRUE/FALS value. It determines whether to remove blank cells or not in the output.

To illustrate this, consider this arrangement of data separated by semi-colons. Note the presence of two adjacent semi-colons with no data between them:

The fourth argument determines whether to show or hide the blank cell caused by the two adjacent semi-colons.

To keep the blank cells, add FALSE as the fourth argument:

SPLIT Function in Google Sheets: Syntax

It takes 4 arguments:

text

This is the text string or value in the cell that you want to split. It can also be a reference to a cell with a value in, or even the output of a nested formula, provided that output is a string or value and not an array.

delimiter

The character or characters used to split the text. Note that by default, all characters are used in the division. So a delimiter of “the” will split a text string on “the”, “he”,”t”,”h”,”e” etc.

This behavior can be controlled by the next argument:

split_by_each

This argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

The TRUE behavior splits by individual characters in the delimiter and any combination of them. The FALSE behavior does not consider the characters separately, and only divides on the entire delimiter.

remove_empty_text

The fourth and final argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

It specifies what to do with empty results in the SPLIT output. For example, suppose you’re splitting a text string with a "," and your string looks like this: “Ben,Bob,,Jenny,Anna”

Between the names Bob and Jenny are two commas with no value between them.

Setting this final argument of the SPLIT function to FALSE results in a blank cell in the output. If this fourth argument is omitted or set to TRUE, then the blank cell is removed and “Bob” and “Jenny” appear in adjacent cells.

SPLIT Function Notes

• Delimiters in SPLIT are case sensitive. So “t” only splits on lower-case t’s in the text
• The SPLIT function requires enough “space” for its output. If it splits a text string into 4 elements then it requires 4 cells (including the one the formula is in) on that row to expand into. If there is already data in any of these cells, it does NOT overwrite it but instead shows a #REF! error message
• You can input a range as the first argument to the SPLIT function, but it requires an Array Formula wrapper to work
• The output from the SPLIT function is an array of values that can be passed as the input into another formula, which may require the use of the Array Formula

Alternative Split Method

There’s an alternative way to split values in a Google Sheet.

Under the Data menu, there’s a feature called “Split text to columns” which will separate single columns into multiple columns, based on the delimiter you specify.

It’s a quick and easy way to split text.

Note that it overwrites existing data in your Sheet if the split columns overlap with any existing data.

SPLIT Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open it in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

---

Advanced Examples of the SPLIT Formula in Google Sheets

Extract The N-th Word In A Sentence

You can wrap the SPLIT function output with an INDEX function to extract the word at a given position in a sentence. E.g. to extract the 4th word, use this formula:

If you combine this with a drop down menu using data validation, you can create a word extractor:

Alphabetize Comma-Separated Strings With The SPLIT Function in Google Sheets

Suppose you have a list of words in a single cell that you want to sort alphabetically:

This formula will rearrange that list alphabetically:

It splits the string of words, applies the TRANSPOSE function to convert into a column so it can be sorted using the SORT function, and then recombines it with the JOIN function.

Read more in Formula Challenge #3: Alphabetize Comma-Separated Strings.

Splitting and Concatenating Strings

The SPLIT is useful in more advanced formulas as a way to divide an array into separate elements, do some work on those elements (e.g. sort them) before recombining them with another function, like the JOIN function.

For example, this array formula will add surnames to a list of first names in a cell:

which looks like this in your Google Sheet:

Using the onion framework to analyze this formula, starting from the innermost function and working out, it splits the text string, joins on the surname “Smith”, trims the excess trailing space with the TRIM function, and finally outputs an array by using the Array Formula.

Find Unique Items In A Grouped List

Suppose you want to find unique values from data that looks like this:

You want to extract a unique list of items from the column containing grouped words, which are separated by commas.

Use this formula to extract the unique values:

Read more about this technique in this post: Get A Unique List Of Items From A Column With Grouped Words

Unpivot Technique

The SPLIT function in Google Sheets is used in a number of the complex IMPORT formulas for retrieving social media statistics into your Google Sheet.

The SPLIT function was combined with the FLATTEN function in this exceedingly wacky unpivot formula in Google Sheets:

All in all, SPLIT is a useful function!

The REPT function in Google Sheets is used to repeat an expression a set number of times.

The REPT formula to repeat “Go! ” three times is:

Notice the additional space added after the exclamation point, so that there is a space between the repeated values in the output.

Continue reading REPT Function in Google Sheets

The INDIRECT function in Google Sheets is used to convert text strings into valid cell or range references.

For example, the INDIRECT function will convert the text string “A1” into the cell reference A1. The formula is:

which is equivalent to this formula:

It gives the answer 10 in the following example because that’s the value in cell A1:

Continue reading INDIRECT Function in Google Sheets

The IF function in Google Sheets is used to make decisions with your data. It’s from the Logical family of functions in Google Sheets.

In this tutorial, you’ll see how to use IF formulas in Google Sheets to make decisions with your data.

What does the IF Function do?

At its heart, the IF function is a test that evaluates to a true or false value with a defined behavior if the outcome is true and a different behavior when the outcome is false.

Let’s see an example.

Suppose you have two columns of numbers in A and B and you want to compare each row to see which column has the larger number.

You would use the IF function in Google Sheets to do this!

Inside the IF formula, the first expression A2 > B2 checks whether the value in cell A2 is greater than the value in cell B2.

The outcome of this test is either a TRUE or a FALSE value.

The IF function requires a TRUE or FALSE value for this first argument.

Next up, you specify what you want to happen when the result is true. In this example the output of the function in the cell is “Column 1 is larger”

The final argument is the value you want to show if the result is false. In this example, that means the number in column A was smaller (or equal to) the value in column B. In this case, the output of the function is “Column 1 is less than or equal to column 2”.

IF Function in Google Sheets: Syntax

You might hear it referred to as an IF function, an IF formula or even an IF statement, but they all mean the same thing.

It takes 3 arguments:

logical_expression

An expression that gives a TRUE or FALSE answer, or a cell that contains a TRUE or FALSE value.

value_if_true

The value displayed by the IF function if the logical expression has the TRUE value.

value_if_false

The value displayed by the IF function if the logical expression has the FALSE value.

IF function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

The IF function is also covered in the Day 2 lesson of my free Advanced Formulas 30 Day Challenge course.

You can also read about it in the Google documentation.

Calculations with the IF Function in Google Sheets

IF functions can be combined with other functions to perform calculations on values above a certain threshold for example.

When the logical expression is true, do this calculation, otherwise leave the value as it is.

Perhaps your business has a performance bonus structure that pays out a 20% bonus above a certain threshold of client revenue. Use an IF statement to determine if the threshold has been met and then put the calculation in the true field:

Using The IF Formula for Classification

Another example use case for the IF function is to classify items.

For example, you might want to segment your customers into long-term customers and new customers, based on whether they’ve been a client for 12 months or longer.

Suppose column A was a column containing the number of months a client has been with you, the following IF formula would classify them into long-term or new clients:

This kind of segmentation is useful in lots of different ways.

It lets you compare the retention and churn metrics of the two groups. You can run different marketing campaigns to different segments of your data. Or, maybe you just want to send a “Thank you, you’re awesome!” card to your long-term clients.

Nested IF Formula in Google Sheets

Sometimes one IF function alone isn’t enough.

Inside the TRUE or the FALSE arguments you can nest another IF function.

It looks complex but if you apply the onion method for working with formulas and work in layers, you’ll see it’s not difficult.

Working from the outside in, it checks if the value is greater than 50. If that is true, then the formula is done and it displays “Greater than 50”.

But if that’s false, it must mean the value is either less than 50 or equal to 50, so we use a second, nested IF to check for that:

If your nested IF formulas get too complicated, you might want to try the SWITCH function.

IF Formulas and Checkboxes

Combining checkboxes with IF formulas is a powerful and versatile technique to use in your Sheets.

Checkboxes are actually just TRUE or FALSE values in disguise!

With a checkbox in cell A2, it can only ever have a TRUE or FALSE value. So it can be plugged into the IF function’s first argument directly!

This sort of logic is useful in many settings, for example a dashboard where you want to show/hide additional context for specific numbers or charts.

Direct TRUE or FALSE Input

In the example above, you had a formula already in column A that contained a TRUE or FALSE value.

Since the first argument of the IF statement is looking for a TRUE of FALSE, you only need to reference that cell directly without testing for TRUE / FALSE.

Your IF formula would look like this:

There is NO need to test for TRUE or FALSE again. So you should not see an IF function like this:

In fact, you should never see an “= TRUE” or “= FALSE” in an IF statement because they’re redundant.

Replace numerical IF formulas with MIN or MAX functions

Suppose you have a column of values that you want to cap at a certain level, so that everything above a threshold value, e.g. 200, gets set to that value.

Most of us would approach this by writing an IF formula that checks whether the value is above 200 and then set it to 200 if TRUE, or the actual value if FALSE, like so:

However, you can replace the whole IF function with a much more succinct MIN function, which chooses the value 200 if the actual value is larger, since 200 is the minimum:

Similarly, we can use the MAX function to replace IF statements when we’re looking at a threshold on the low side.

It’s good practice to write efficient formulas because it’s quicker and you’re less likely to make mistakes.