Spreadsheets have become the first stop for most of us to handle data. And the nature of the data supported by these applications is not limited to numbers. They seem to provide us with all the requisite tools to tackle even the text-based data. In this article, we’ll learn the functioning of SPLIT formula in Google Sheets. It helps us to separate a string based on a delimiter.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- text – is the text or string that we want to split.
- delimiter – is a single character or a group of characters that the formula should consider for splitting the text. By default, each character within the delimiter is considered individually. For instance, if the delimiter is “one”, then the text is divided around the characters “o”, “n”, and “e”. If we do not this behavior, we can do so by setting the split_by_each to FALSE.
- split_by_each – [ OPTIONAL – TRUE by default ] – if we went with the default option, then the formula considers each character within the delimiter string to split the text. If we set this to FALSE, then the formula considers the delimiter as a whole.
- remove_empty_text – [ OPTIONAL – TRUE by default ] – this parameter indicates whether or not the formula should remove empty text messages from the split results. The default behavior is to treat consecutive delimiters as one (if TRUE). If FALSE, empty cells values are added between consecutive delimiters.
Usage: SPLIT formula in Google Sheets
The syntax looks more complex than it actually is. So, to get comfortable with the formula, let us try our hands practically without further ado. See the screenshot below.
We have entered all the formulas in column D. That is because the SPLIT formula in Google Sheets spreads its output across multiple cells towards the right, as is the case with all the examples. So, it is very important that we keep the cells, where we expect the data to flow, clear of any values. Otherwise, the formula returns #REF! error.
The first four examples are probably a little straightforward. We passed the text and the delimiter, and the outputs are as expected. It gets interesting hereafter. Compare the inputs and outputs on the fifth and sixth examples. We provided the same text and delimiter values. Yet the outputs are a lot different from each other. In the fifth example, the formula considers each individual characters – “o”, “n”, and “e” as separate delimiters. That is because the split_by_each parameter is TRUE by default. However, in the sixth example, we set it to FALSE. Therefore the delimiter is a composite “one” instead of separate characters. So, the formula did not split the text, because there is no occurrence of “one” within the text “Countries”. Had there been an occurrence, like in the seventh example, the formula would have split the text accordingly. In the final example, we passed FALSE for the remove_empty_text parameter. Therefore after the making a split, the formula didn’t remove the empty text from cell E9.
For more advice on how to better structure and manage your data in Google Sheets, check out the following blog post: “Spreadsheet Modeling – Best Practices and Tips“.