HomeGoogle DocsSpreadsheetSPLIT Function: Splitting Text into Rows, Columns, or Both in Google Sheets

SPLIT Function: Splitting Text into Rows, Columns, or Both in Google Sheets

Published on

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, " ")))
Example of using the SPLIT function to split text into columns in Google Sheets

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, " | "))))
Example of using the SPLIT function to split text into rows in Google Sheets

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:

Emily Grace TurnerJan50010

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))
SPLIT function to split text into rows and columns

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)
Separating the first names from a list of names

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
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

7 COMMENTS

  1. Hello. Your page is very helpful, but I have a question.

    I am trying to transpose(split) multiple rows, with multiple comma-delimited items, into a column.

    However, when I try to run the formula on all the rows, I get an error: “Array result was not expanded because it would overwrite data in E5.”

    Essentially, is there a way to bulk transpose(split) in a situation where there are multiple rows, without having to put in empty spaces beforehand?

    • Hi, Marian,

      You may seem to FLATTEN, not TRANSPOSE the split result.

      Further, you may want to use QUERY or FILTER to omit the blank cells.

      Do it all in one go, not using a copy-paste formula in each row.

      To split and flatten the range A2:A5 all in one go, use the following formula in B2 (first you should make column B blank).

      =lambda(data,filter(data,data<>""))(index(trim(flatten(split(A2:A5,",")))))

      • Thank you so very much for this swift and comprehensible result. It is working as desired. Now I will try to work out a way to automate the process for multiple sheets.

  2. Hi,

    Would there be a way to split and transpose a single column of rows into a second single column of (more) rows?

    In example, I want to split A1 = “item 1, item 2” into B1 = “item 1” + B2 = “item 2” splitting by “,”.

  3. After you split the data…how do you remove the old set of data you split…if I manually delete the old data source, it deletes my split data also…I guess what I need is to “move” data…not just split it.

    • Hi, Tim,

      If you face such issues you can consider using Google Sheets Data menu, Split text into column feature.

      If you are particular to use the formula, do as follows.

      Select the split data, right click and copy. Again right click and apply paste special, paste values only.

      This will sort out the issue.

LEAVE A REPLY

Please enter your comment!
Please enter your name here