How To Use SPLIT in Google Sheets

Learn how to use the SPLIT formula in Google Sheets in this simple step by step tutorial.

SPLIT Formula in Google Sheets

The SPLIT formula is used to split text (who’d have thought) from one cell into another.

It’s one of those simple formulas that can save you loads of time.

Got to split up a bunch of cell data by  text, comma, space or any other symbol? SPLIT is for you.

What is the SPLIT Formula?

The SPLIT function helps you to quickly and easily separate text into columns based on a delimiter.

The delimiter is a character (or characters) you choose by which you want to cut the text.

The syntax of the SPLIT function is:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

The SPLIT formula is made up of a few different parts:

  • delimiter
  • split_by_each
  • remove_empty_text

Here’s a quick rundown of what each of those mean:

delimiter

delimiter is the character or characters you use to split text.

split_by_each

split_by_each is an optional argument which is TRUE by default.

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 this argument to FALSE.

remove_empty_text

remove_empty_text is another optional argument which is 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 behaviour is to treat them as one (if TRUE). If it’s FALSE, empty cells are added to the results where there are duplicated delimiters.

SPLIT or CONCATENATE?

The function does the opposite of the CONCATENATE function that joins the content of separate cells together.

Suppose you have a list of names in a spreadsheet and you want to separate them by first and last names into two columns. Instead of wasting time with manually copying and pasting, the SPLIT function can do it automatically.

Depending on your data source, you may have commas, periods or just spaces separating the data. In this example of names, you’re most likely to have blank spaces, so this is the character (the delimiter) by which you need to split your text.

When to use SPLIT for marketing stuff?

  • Splitting URLs into different subfolders.
  • Separating comma separated values into their own columns.
  • Separating data into separate columns.

How To Use The SPLIT Formula in Google Sheets

Before you start, make sure there are a few empty columns to the right of your text, so there is sufficient room to split out your text.

Why? Because, otherwise you will get an error because of overwriting your data in these cells.

To separate the full names into first and last names, you need to supply two arguments to the SPLIT function:

  1. the cell where the source text is located
  2. the delimiter which is a space character in this case

=SPLIT(A3, “ “)

It’s important to surround the delimiter with double quotation marks.

SPLIT Formula Example

Now let’s see another example, where there are words together in one cell, separated by commas. For example, keywords of a specific topic that you would like to put each in a separate cell.

In this case, you just need to change the delimiter in the function to the character(s) that separate your words.

The function looks like this:

=SPLIT(A2, “, “)

The delimiter consists of two characters here, a comma and space.

The function automatically fills as many columns as it needs to put every word in a separate cell. It depends on the number of delimiters found in the text.

Apart from using the SPLIT function, Google Sheets has a built-in tool to split text with the most common delimiters.

Select the cells that have the text to be split and click on the Data > Split Text to Columns option in the menu.

Then this feature automatically detects your delimiter, or you can choose it from a list that contains the most common delimiter characters such as comma, semicolon, period and so on.

Summary

Go check out the Google Sheets Templates in the diretory.

More ways to clean data in Google Sheets: