This guide explains how to use the SPLIT function in Google Sheets and other related functions to make it work better. ‘Enhancing the function’ just means making it better or doing more things.

For example, the SPLIT function needs a delimiter (a special character) to split text. If there’s no delimiter, you can use REGEXREPLACE to add delimiters at certain spots and help with the splitting.

Also, to make the results look better, you can use functions like INDEX or CHOOSEROWS to pick specific parts of the split text. And, TRANSPOSE, TOCOL, WRAPROWS, and WRAPCOLS can help organize the final output nicely.

Importance of Splitting Text in Spreadsheets

There are numerous reasons behind its necessity. Here are a few scenarios:

Scenario 1: Imagine you have an employee list, where the first, middle, and last names are entered together in a combined format within a single column for each row. If you wish to separate them into three distinct columns, you can utilize the SPLIT function in Google Sheets.

Scenario 2: Consider a situation where you’ve imported content from other software tools like Tally, typically in a comma-delimited format. To transform the imported text into a tabular form, you can employ the SPLIT function.

Scenario 3: When dealing with a comma-delimited SIF file or a SIF file created using Google Sheets for WPS, the SPLIT function becomes invaluable for organizing such data into a tabular form within Google Sheets.

Scenario 4: In any other analogous situations where you need to split text strings into columns, the SPLIT function in Google Sheets can be a helpful tool.

SPLIT Function in Google Sheets: Syntax and Arguments

Syntax:

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

Arguments:

• text: The text to split.
• delimiter: The character or characters in the text to be used for splitting.
• split_by_each: Optional and TRUE by default. It determines whether or not to split text around each character contained in the delimiter (details explained in the examples).
• remove_empty_text: Optional and TRUE by default (details explained in the examples).

Here are some examples of using the SPLIT function in Google Sheets. Please try to use the same sample texts for the test. Once you’ve learned, feel free to use your own sample texts.

How to Split Text in a Cell into Columns in Google Sheets

In the following example, there are 5 random names with first names, middle names, and last names in the range B2:B6.

To separate the first name, middle name, and last name of the value in cell B2 into C2, D2, and E2, you can use the following formula:

=SPLIT(B2, " ")

In this formula:

• text is the name “Emily Grace Turner” in cell B2.
• delimiter is " " (represents space).
• split_by_each and remove_empty_text are left unused, so their TRUE default values are applied.

To separate the first name, middle name, and last names of all the text in the range B2:B6, you can use the SPLIT function in an array formula as follows:

=ArrayFormula(IFERROR(SPLIT(B2:B6, " ")))

The IFERROR function is used to handle errors that might occur when you delete any name in the range, preventing the formula from returning #ERROR. It helps to remove unforeseen errors.

How to Split Text in a Cell into Rows in Google Sheets

To split text in a cell into rows, you just need to wrap the earlier formula with the TRANSPOSE function.

For example, if the value in cell B2 is “Emily | Jan | 500”, the following formula in cell C2 will return Emily in cell C2, Jan in cell C3, and 500 in cell C4.

=TRANSPOSE(SPLIT(B2, " | "))

You can apply this with an array formula as mentioned earlier, and here is an example:

=ArrayFormula(TRANSPOSE(TRANSPOSE(SPLIT(B2:B6, " | "))))

This is useful when you want to arrange related information in the same column.

Before delving into how to use the SPLIT function to split text into columns and rows, let’s first understand the two optional arguments in this function.

Optional Arguments: Split by Each and Remove Empty Text

The optional arguments play a crucial role in the SPLIT function, so it’s essential to understand them before proceeding.

Assuming the text in cell B2 is “Emily Grace Turner | Jan | 500,” the following formula will separate Emily, Grace, Turner, Jan, and 500 into separate cells:

=SPLIT(B2, " | ")

This is because the delimiter is " | ", not "|". The SPLIT function divides text around each character contained in the delimiter. You might wonder why not use "|"? The reason is that our delimiter is " | ", not a single character, but multiple characters.

The following formula solves this:

=SPLIT(B2, " | ", FALSE)

It would return “Emily Grace Turner” in one cell, “Jan” in another cell, and 500 in a third cell. This is all about the split_by_each argument.

The next optional argument is remove_empty_text. Its purpose is as follows:

Assume you have two consecutive delimiters in a text. Do you want to treat it as one or two separate delimiters? If you select it as a separate delimiter, the function will leave an empty cell in the result.

To treat the consecutive delimiters as separate, specify FALSE in remove_empty_text.

Assume the value in cell B2 is “Emily Grace Turner, Jan, 500, , 10”. The formula is:

=SPLIT(B2, ", ", FALSE, FALSE)

It would return the following result:

Utilizing the SPLIT Function to Split Text into Rows and Columns

Unlike Excel 365, as of the latest update to this post, there is no specific argument in the SPLIT function to split text into rows and columns in Google Sheets. We can’t specify separate row and column delimiters.

However, that doesn’t mean we can’t use the SPLIT function to achieve this in Google Sheets. You should follow the SPLIT > TRANSPOSE > SPLIT approach.

For example, if the value in cell B2 is “Emily | Jan | 500, Benjamin | Jan | 400, Sophia | | 350”, you first need to split it by the comma delimiter and then by the pipe.

=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(B2, ", ", FALSE))," | ", FALSE, FALSE))

Additional Tips

In the beginning, I mentioned enhancing the results using some other functions. You can refer to my function guide to learn more about those functions.

Here are a few real-life examples.

If you split first, last, and middle names, and want just the first names, you can use the following formula:

=CHOOSECOLS(ArrayFormula(SPLIT(B2:B6, " ")), 1)

The CHOOSECOLS function is employed to extract the first column from the SPLIT function result.

Then, if you want, you can convert this into a row using TOROW as follows:

=TOROW(CHOOSECOLS(ArrayFormula(SPLIT(B2:B6, " ")), 1))

Want to make these into a two-person group? Then use WRAPCOLS.

=WRAPCOLS(CHOOSECOLS(ArrayFormula(SPLIT(B2:B6, " ")), 1), 2, "")

Resources

We have explored the use of the SPLIT function to split texts into columns, rows, and both rows and columns in Google Sheets.

Here are some advanced tutorials related to SPLIT:

1. Split Number to Digits in Google Sheets.
2. How to Use Trim Function With Split in Google Sheets.
3. Split to Column and Categorize – Google Sheets Formula.
4. Split a Column into Multiple N Columns in Google Sheets.
5. How to Split Number from Text When No Delimiter Present in Google Sheets.
6. Split Comma-Separated Values in a Multi-Column Table in Google Sheets
7. Extract Unique Values from a Comma Separated List in Google Sheets
8. Insert Delimiter into a Text After N or Every N Character in Google Sheets