Jawadul Islam Chowdhury
While using Google Sheets sometimes we need to separate the content from a cell and split the strings into different columns or rows so that we can get data more quickly. Like from an official mail of a person we can get data like the person’s name, position, company the person works for, etc. In this article, you will learn how to use SPLIT function in Google Sheets.
The above image is the overview of the article. This article will show you different ways to use the SPLIT function to separate strings from cell content.
What Is SPLIT Function in Google Sheets?
The SPLIT function is mainly used to split a cell into columns by delimiter. This function has four arguments.
Syntax
The syntax for the function is-
Argument
| ARGUMENT | Requirement | Function |
|---|---|---|
| text | required | Indicates the text from the cell or range you want to split |
| delimiter | required | denotes the separator using which the function will split the string |
| split-by-each | optional | decides whether or not to divide the text around each character in the delimiter |
| remove_empty_text | optional | decides whether the consecutive delimiters will be treated as a single delimiter or individual delimiters according to the input |
Output
Formula SPLIT(“a,b,c”, “,”, TRUE, TRUE) will split the texts by the delimiter comma (,) and will return the strings a, b, and c in three different columns.
5 Ideal Examples of Using SPLIT Function in Google Sheets
Specifying the delimiter you can easily split a cell content using the SPLIT function. Moreover, you can use the function along with other functions like ARRAYFORMULA, INDEX, TRANSPOSE, etc.
Suppose you have a dataset of employee IDs and the mail IDs of different companies. Now you want to extract data from the dataset so you need to separate the cell contents into different columns. Follow the below examples on how to use the SPLIT function in Google Sheets.
1. Separating Text by a Specific Delimiter
The delimiter is used between the plain text to specify the boundary of separation in texts. We can use this mark or symbol to separate text elements of a cell. Blank space ( ), full stop (.), comma (,), semicolon (;) etc are used as delimiters in Google Sheets. Follow the below steps to use a specific delimiter in the SPLIT function to separate the cell content.
📌 Steps:
- In the beginning, select cell D5 and enter the following formula. This formula uses the SPLIT function to divide the text string of the cell B5 based on the delimiter (;) and returns arrays of substrings into columns.
=SPLIT(B5,";")
- Then, hit Enter. Thus, you will see the separated cell elements as similar to the following image.
- Finally, use the fill-handle tool to copy the formula to the following rows.
Read More: How to Split Cell by Comma in Google Sheets (2 Easy Methods)
2. Separating Text with Multiple Delimiters
We can also separate text using the multiple delimiters that are situated around the text elements. Follow the below steps.
📌 Steps:
- First, select cell D5 and enter the following formula. This formula uses the SPLIT function to divide the text string of the cell B5 based on the delimiters (;.@) and returns arrays of substrings into columns.
=SPLIT(B5,";.@")
- Next, click Enter. The output will look like the following image.
- Lastly, drag the fill-handle tool to copy the formula to the following cells. The final output will look like the following image.
Read More: How to Split String into Array in Google Sheets (3 Easy Methods)
3. Merging SPLIT Function with Other Functions
You can use the SPLIT function with other functions like the ARRAYFORMULA, TRANSPOSE, and INDEX functions, etc. In this section of the article, we will show you how to use the SPLIT function with other functions.
3.1 Merging SPLIT and ARRAYFORMULA Functions
When working with a dataset with multiple data points, we first apply the formula to a specific cell. Then utilizing the fill-handle tool, we copy the formula from the cell to the following. It is a very tiresome task for a sizable dataset. In that case, we can easily split the cells by applying the formula just once by combining the ARRAYFORMULA and the SPLIT function. To do that, follow the steps below.
📌 Steps:
- First, select the cell D5 and input the following formula. In this formula, the SPLIT function is used to divide the text in the cells B5:B9 into columns of substrings, based on the delimiter “.;@“. The ARRAYFORMULA function is then applied to the SPLIT function so that it is applied to the entire range of cells B5:B9 and not just one cell.
=ARRAYFORMULA(SPLIT(B5:B9,".;@"))
- Shortly after that, the final output of the formula will be seen in the following image.
3.2 Combining SPLIT, ARRAYFORMULA, and TRANSPOSE Functions
You can separate the text strings into different rows using the SPLIT, ARRAYFORMULA, and TRANSPOSE functions in combination. Follow the step below to split a cell into rows.
📌 Steps:
- Initially, select cell E5 and type the following formula. Here, in this formula, the SPLIT function splits the text in the range of cells B5:B9 using the delimiter “@.;” and returns an array of substrings. The ARRAYFORMULA function is then applied to the SPLIT function so that it is applied to the entire range of cells B5:B9, and the TRANSPOSE function is used to rotate the resulting array, meaning columns will become rows.
=TRANSPOSE(ARRAYFORMULA(SPLIT(B5:B9,"@.;")))
- The following image represents the final result. Here, a cell was divided into rows using the SPLIT and TRANSPOSE additionally, the ARRAYFORMULA is used to simultaneously assign the formula to each cell.
Read More: How to Split Cell into Rows in Google Sheets (2 Useful Methods)
3.3 Incorporating SPLIT and INDEX Functions
There are times when we don’t need all the information in the cell. Utilizing the SPLIT and INDEX functions together allows us to extract specific data and organize it into columns. Let’s say you need to separate the employee Name, Position, and Department information from the dataset. To separate specific information from the cell, follow the steps below.
📌 Steps:
- Firstly, select cell D5 and to split the employee name from the dataset, enter the following formula. This formula uses the INDEX and SPLIT functions to retrieve a specific element of an array after splitting a text string. The SPLIT function splits the text in cell B5 using the delimiter “;.@” and returns an array of substrings. The INDEX function is then used to return the value of the 2nd element of that array.
=INDEX(SPLIT(B5,";.@"),2)
- Then, use the fill-handle tool to copy the formula to the following. The output will look like the following image.
- Secondly, to split the employee’s position from the dataset, select cell E5 and type the following formula. Here, in this formula, the SPLIT function splits the text in cell B5 using the delimiter “;.@” and returns an array of substrings. The INDEX function is then used to return the value of the 4th element of that array.
=INDEX(SPLIT(B5,";.@"),4)
- Now the output will look like the following image after using the fill-handle tool.
- Thirdly, to split the data of the department of the employees, select cell F5 and input the following formula. In this formula, the SPLIT function splits the text in cell B5 using the delimiter “;.@” and returns an array of substrings. The INDEX function is then used to return the value of the 5th element of that array.
=INDEX(SPLIT(B5,";.@"),5)
- Lastly, the final output will look like the following image.
Read More: How to Use QUERY with SPLIT Function in Google Sheets
4. Using Split by Each Parameter Argument
split_by_each is a SPLIT function’s optional parameter that is used when the delimiter contains more than one character. The inputs are TRUE or FALSE. If you use TRUE, the function will count all of the characters in the delimiters as single delimiters, and the text around all delimiters will split. When you use FALSE, the function will count the characters as a single parameter and the text around the entire delimiter will split. If no parameters are used, it will be considered TRUE.
Let’s say you have to input the sentence I have more information and you will use more as a delimiter. Now, Here are the steps to use the parameter split_by_each.
📌 Steps:
- First of all, select cell F5 and enter the following formula.
=SPLIT(B5,"more",TRUE)
- Then, the output will look like the following image. Here, words ‘m’, ’o’, ’r’, and ‘e’ are defined as delimiters as the TRUE parameter is used. So, texts around these words are split.
- Now, select cell F6 and type the following formula.
=SPLIT(B6,"more",FALSE)
- Afterward, the output will look like the following image. Here, the whole word ‘more’ is defined as the delimiter. Thus the text around the word more is split.
Read More: [Solved!] Split Text to Columns Is Not Working in Google Sheets
5. Splitting by Remove Empty Text Parameter
When there are consecutive delimiters in the text you can use this parameter. This parameter also has two inputs; TRUE (as default) and FALSE. If you use TRUE, the function will treat the consecutive delimiters as a single delimiter. If FALSE is used then they will be treated as individual delimiters and a blank cell will be added to the result. Follow the steps below to learn the use of the parameter remove_empty_text.
📌 Steps:
- Firstly, select cell D5 and enter the following formula.
=SPLIT(B5,".",TRUE,TRUE)
- After that, the output will look like the following image. Here, the function will treat the consecutive delimiters ‘..’ as a single delimiter. Because we have used TRUE in the parameter.
- Similarly, select cell D6 and enter the following formula.
=SPLIT(B6,".",TRUE,FALSE)
- Finally, the output will look like the following image. Here, we have used the parameter FALSE. That’s why the function treats the consecutive parameters as single delimiters and adds a blank cell to the result to reflect an empty string.
Read More: How to Split Text to Columns Using Formula in Google Sheets
Things to Remember
The SPLIT function will remove the delimiters from the text.
Conclusion
Hopefully going through the article you can learn the use of SPLIT functions easily. Please comment below with your doubts and suggestions regarding this article. Visit Officewheel for more Google Sheets-related helpful articles.
