Have you ever struggled with separating a text string into different parts in Google Sheets? Well, worry no more! The SPLIT function is here to save the day. With this powerful function, you can divide a text string or a value using a specific delimiter and display the separated pieces in their own cells.
Examples of the SPLIT function
Let’s start with a simple example to understand how to use the SPLIT function to separate a list of names in cell A1.
The following SPLIT formula will separate these names using a comma as the delimiter:
=SPLIT(A1, ",")The result is 5 cells, each containing a name. Note that one cell appears empty because the text string in cell A1 has two adjacent commas with a space between them. The space is interpreted the same way as the names and is included in the result.
Now, let’s see what happens if we include a space in the delimiter, i.e., “, “.
The function splits both on the comma “,” and on the space ” “, so the name “Mary Jo” is separated into two:
This is probably not the desired behavior.
The third argument is optional and can take a value of TRUE or FALSE to determine if SPLIT considers each individual character of the delimiter (TRUE) or only the complete combination as the separator to use (FALSE).
In our example, by adding FALSE, SPLIT considers only the comma/space combination as the delimiter:
And the result looks like this:
There is also a fourth argument, optional, which takes a value of TRUE or FALSE. It determines whether empty cells should be removed from the result or not.
To illustrate this, consider this arrangement of data separated by semicolons. Note the presence of two adjacent semicolons without data between them:
The fourth argument determines whether to display or hide the empty cell caused by the two adjacent semicolons.
To retain the empty cells, add FALSE as the fourth argument:
Syntax of the SPLIT function in Google Sheets
The SPLIT function 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 containing a value or even the output of a nested formula, as long as this output is a text string or a value and not an array. -
delimiter: This is the character or characters used to divide the text. Note that by default, all characters are used in the division. So, a delimiter of “the” will divide a text string on “the”, “he”, “t”, “h”, “e”, etc. -
split_by_each: This argument is optional and can only take the values TRUE or FALSE. If omitted, it is assumed to be TRUE.The TRUE behavior splits individual characters of the delimiter and all possible combinations of them. The FALSE behavior does not consider characters separately and only divides on the entire delimiter.
-
remove_empty_text: The fourth and final argument is optional and can only take the values TRUE or FALSE. If omitted, it is assumed to be TRUE.It specifies what to do with empty results in the SPLIT output. For example, assume you are splitting a text string with a comma “,” and your string looks like this: “Ben,Bob,,Jenny,Anna”
Between the names Bob and Jenny, there are two commas without any value between them.
By setting this final argument of the SPLIT function to FALSE, an empty cell appears in the output. If this argument is omitted or set to TRUE, then the empty cell is removed and “Bob” and “Jenny” appear in adjacent cells.
Notes on the SPLIT function
-
Delimiters in SPLIT are case-sensitive. So, “t” will only divide on lowercase t’s in the text.
-
The SPLIT function requires enough space for its result. If a text string is split into 4 elements, it takes up 4 cells (including the one where the formula is), on that row to expand. If there is already data in any of those cells, it does NOT overwrite them but instead displays a #REF! error message.
-
You can enter a range as the first argument of the SPLIT function, but it requires wrapping in a matrix formula to work.
-
The output of the SPLIT function is an array of values that can be used as input in another formula, which may require the use of the matrix formula.
An alternative method of text splitting
There is an alternate way of splitting values in a Google Sheets.
Under the Data menu, there is a feature called “Split text into columns” that allows you to divide single columns into multiple columns, based on the delimiter you specify.
It’s a quick and easy way to split text.
Note that this will overwrite existing data in your sheet if the split columns overlap with existing data.
SPLIT function templates
Click here to open a read-only copy.
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it may be due to your Google Workspace settings. If you click on the link and open it in a private browsing window, you should be able to see it.
You can also find more help in the Google documentation.
Advanced examples of the SPLIT formula in Google Sheets
Extracting the nth word from a sentence
You can wrap the output of the SPLIT function with the INDEX function to extract the word at a given position in a sentence. For example, to extract the 4th word, use this formula:
If you combine this with a dropdown menu using data validation, you can create a word extractor:
Sorting comma-separated strings alphabetically 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 this list in alphabetical order:
=JOIN(", ",SORT(SPLIT(A1,", ")))It splits the string of words, applies the TRANSPOSE function to convert it into a column so that it can be sorted using the SORT function, and then recombines it with the JOIN function.
Learn more in Formula Challenge #3: Sorting Comma-Separated Strings Alphabetically.
Splitting and concatenating strings
The SPLIT function is useful in more advanced formulas as a way to split an array into distinct elements, perform operations on those elements (e.g., sorting them) before recombining them with another function, such as the JOIN function.
For example, this array formula will add last names to a list of first names in a cell:
={A1:A3&" "&TRIM(SPLIT(B1,", "))}
Using the layering analysis technique to break down this formula, starting with the innermost function and working outward, it splits the text string, joins it with the last name “Smith,” trims off the excess space with the TRIM function, and finally produces an array using the matrix formula.
Finding unique items in a grouped list
Suppose you want to find unique values in 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:
=UNIQUE(SPLIT(A1,", "))Learn more about this technique in this article: Getting a Unique List of Items from a Column with Grouped Words.
The spill technique
The SPLIT function in Google Sheets is used in several of the complex IMPORT formulas to fetch social media stats into your Google Sheets.
The SPLIT function has been combined with the FLATTEN function in this wacky spill formula in Google Sheets:
=FLATTEN(SPLIT(IMPORTDATA("https://example.com/data.csv"),","))Overall, the SPLIT function is a handy tool!
Remember, if you want to dive deeper into the world of SEO and online marketing, visit our website Crawlan.com. Stay tuned for more juicy secrets and helpful tips!
