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.

‍

Analyze your live financial data in a snap

Are you learning this function to visualize financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you automate manual workflows and keep numbers updated in real-time. You can access more than fifty financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

‍

What are the other tips or formulas on text in Google Sheets?

Check the following articles if you are interested in formatting text in cells.
How to Make Text Bold, Italic, and Strikethrough in Google Sheets
How to Align Text in Google Sheets
How to Wrap Text in Cell in Google Sheets
How to Rotate Text in Google Sheets
How to Change Default Font in Google Sheets
How to Indent Texts in Google Sheets: Explained
‍‍How to Capitalize the First Letter in Google Sheets
‍How to Capitalize All Letters in Google Sheets
How to Make All Letters Lowercase in Google Sheets
TEXT Function in Google Sheets: Explained

Do you want to learn how to combine or split text in Google Sheets?
How to Combine Texts in Google Sheets
CONCAT Function in Google Sheets: Explained
How to Use the CONCATENATE function in Google Sheets
How to Split Texts into Columns in Google Sheets
CHAR Function in Google Sheets: Explained

Do you want to know where a specify character or word is in a text string?
LEFT Function in Google Sheets: Explained
How to Use RIGHT Function in Google Sheets
SEARCH Function in Google Sheets: Explained
FIND Function in Google Sheets: Explained

Do you need to replace a part of text with another characters or words?
How to Use REPLACE Function in Google Sheets
SUBSTITUTE Function in Google Sheets: Explained

Do you like to show a formulae in a cell?
How to Use FORMULATEXT Function in Google Sheets

This function is useful when you need to count the number of characters in a text string.
LEN Function in Google Sheets: Explained

Why don’t you double-check the spelling of your texts or cleaning up extra spaces before printing or closing Google Sheets?
How to Spell-check in Google Sheets
How to Trim Whitespaces in Google Sheets

You can repeat the same text string with this formula.
REPT Function in Google Sheets: Explained

You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides