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.
The general syntax is as follows:
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.
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.
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.
Check the following articles if you are interested in formatting text in cells.
Do you want to learn how to combine or split text in Google Sheets?
Why don’t you double-check the spelling of your texts before printing or closing Google Sheets?
Learn how to do this step-by-step in the video below 👇