How to Use SPLIT Function in Google Sheets

In this article, you will learn how to utilize the SPLIT formula in Google Sheets. The SPLIT function is beneficial when splitting existing text with some conditions.

‍

How to use the SPLIT formula in Google Sheets

1. Type “=SPLIT(” or navigate to the “Insert” tab (or “Functions” icon) → “Function” → “TEXT” → “SPLIT”.
2. Select the text you want to separate.
3. Input value(s) (e.g., a specific letter or sign) as “Delimiter” and define how it works, functioning individually or aggregately, by inputting “TRUE” or “FALSE”.
4. Determine if you remove the empty cells from the split results.
5. Press the “Enter” key.

‍

The general syntax is as follows:

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

Text: This is a text to be split under the condition defined in other arguments.

Delimiter: This value (e.g., letter(s) and/or sign(s) functions as separator.

Split_by_each (Optional): You can input “TRUE” or “FALSE” in this argument, though the default setting is “TRUE”. If the parameter is “TRUE”, the formula adds empty cell values between each letter or sign and treats each text as an independent delimiter. If the argument is “FALSE”, the formula considers the input “Delimiter” as one.

Remove_empty_text (Optional): You can input “TRUE” or “FALSE” in this argument, though the default setting is “TRUE”. This argument determines whether empty cell values are shown in split texts. If it is “TRUE”, the empty cell values are removed, and if “FALSE”, they appear among split texts.

‍

Note that you need to secure enough space for the formula to spread the split texts and that the character(s) to separate the text do not appear in split texts themselves.

‍

See the examples below to understand how the optional arguments work in the formula and its practical usage. 

‍

Examples #1 and #2: They show the same split results because the optional arguments are considered “TRUE” if there is no input. You can see the “delimiter” of “BC”, is not contained in the results, and each of “B” and “C” works as a delimiter independently and separately.

‍

Example #3: As the second optional argument is “FALSE”, the empty cell value is included in the result.

‍

Example #4: As the first optional argument is “FALSE”, the delimiter of “BC” is considered one. Thus the original text is split into two parts before and after the “BC”.

‍

Example #5: The shown result is the same as Example #4

‍

Examples #6 to #8: You can use the SPLIT function when you need to split a full name into a first name and last name, or a full address or a consecutive number into one than one part for the detailed data investigations.

‍

What is the alternative way to split existing texts?

You can use the “Split text to columns” function as an alternative way, although it is suitable for simple splits because of its limited options for separations.

‍

How do I split texts into rows in Google Sheets?

Unfortunately, the Google Sheets function introduced in the sections above doesn’t allow you to split text vertically when you paste a data set. So, you need to use the TRANSPOSE function or Special Paste after you separate texts horizontally.