What Is Split in Google Sheets?
Splitting text for different reasons is something we come across often when using Google Sheets. Hence, SPLIT in Google Sheets can be used to divide a specified string with a delimiter and put each fragment into a separate cell. In other words, the SPLIT function in Google Sheets is used to divide a text string containing a given delimiter and place the output in separate cells, row-wise. Let us look at a simple example using SPLIT, where we separate a list of names in a cell with commas as delimiters. The SPLIT formula will separate these names. Use the following formula in A3.
=SPLIT(A1,”,”)
The result is split into four cells, each containing a name. If you have a space in between, it will be interpreted as a name as well, and we get a blank cell.
Key Takeaways
- SPLIT in Google Sheets is used to divide the string in a cell into multiple cells based on the delimiter specified.
- The syntax of the Split function is =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- If the argument split_by_each is TRUE, each character in the delimiter is used to split the text.
- You can use a single character or set of characters as a delimiter. Some commonly used delimiters include spaces, commas, pipes, etc.
Syntax
The SPLIT in Google Sheets function has the following syntax.
SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- text – The string to divide.
- delimiter – The character to use to split text.
- By default, each character in the delimiter is considered individually. Set split_by_each to FALSE to turn off this behavior.
- split_by_each – (optional) – TRUE by default. It specifies if we should divide text around each character present in the delimiter.
- remove_empty_text – (optional) – TRUE by default. If TRUE, empty text will be removed. If FALSE (default), empty text is kept.
How to use Split Function in Google Sheets?
Enter the function manually
As seen earlier, we use SPLIT in Google Sheets to split a string with delimiters. It can be used in many real-life scenarios. Let us look at how we can use this function with a simple example. We have a few names which need to be split into first name, middle name, and last name.
Step 1: Click on cell B2, where you want to type the SPLIT function.
Step 2: Start typing the function directly into the cell B2. You want to split each name into three. Start by typing =SPLIT(.
Step 3: Enter the parameters as shown.
=SPLIT(A2, “ “). Close the parentheses. Here, the delimiter is a blank space. Press Enter.
Step 4: You can observe how the string has been split. Now, drag the formula to cell B5 and observe the result.
Thus, we can use the function easily to split strings.
From the Google Sheets Menu
Let us look at how to insert the function from the Google Sheets menu.
Step 1: First, go to the “Insert” option. Go to “Functions” and choose “Text.” Here, choose the option “SPLIT.”
Enter the arguments as shown above and Press Enter.
Examples
Using the SPLIT in Google Sheets to split text is as easy as it gets! Here are some interesting examples that help you split text easily. Let us look at some interesting examples of this topic below.
Example #1 – Find Unique Items In A Grouped List
We have sold some fruits in a supermarket. The sales details are shown. We need the list of unique items sold. Let us find out how to get them.
Step 1: First, we use Google Sheets’ innermost function, SPLIT in Google Sheets, to separate the items and costs.
=SPLIT(A1:A10,” “).
Step 2: We add the ARRAYFORMULA function to apply it to the entire range. Enter the formula in B1 and press Enter.
=ArrayFormula(SPLIT(A1:A10,” “))
You can see how the data has been split into two columns.
Step 3: Finally, we add the UNIQUE function to get only the unique values in column B. For this, let us apply the following function in cell D1. Note that we have changed the range from B1:B10 as we apply the formula to this column.
=ArrayFormula(UNIQUE(SPLIT(B1:B10,” “)))
Step 4: Apply this formula in cell D1.
You can see the unique list of items available in the shop.
Example #2 – Extract The N-th Word in A Sentence
Let’s look at an example of extracting the fifth word from a sentence. We use a combination of SPLIT in Google Sheets and INDEX functions for this.
Step 1: GIven below is a sentence in Google Sheets. Let us extract the fifth word.
Step 2: To make things easier, we can split the sentence into units using the space as a delimiter. Now, we need the fifth word. The syntax of the INDEX function is as follows.
INDEX(reference, [row], [column]). Here, we apply it to the SPLIT function as follows in cell B1:
=INDEX(SPLIT(A1,” “),5).
It will retrieve the word in the fifth row.
Step 3: Press Enter. You can observe that it has retrieved the fifth word, “of.”
Example #3 – Alphabetize Comma-Separated Strings
In this example, we sort and rearrange the list of items in cell A1 in alphabetical order. We must use a combination of functions like SPLIT, SORT, and TEXTJOIN.
Look at the list below.
Step 1: First, we use the SPLIT function to separate the vegetables into separate cells. The formula to be used is the following.
=SPLIT(A1, “,”).
Next, use the TRANSPOSE function to turn rows into columns and the SORT function to sort the names in alphabetical order. Apply the following to cell B2.
=SORT(TRANSPOSE(SPLIT(A1, “,”)))
Step 2: Press Enter. You get the vegetables sorted in ascending order column wise. The vegetables are sorted in ascending order column-wise. To join the text again, separated by commas, we use TEXTJOIN.
Apply the following formula in B2.
=TEXTJOIN(“,”,TRUE,SORT(TRANSPOSE(SPLIT(A1, “,”))))
Example #4 – Splitting and Concatenating Strings
The SPLIT is useful for splitting and concatenating strings based on some conditions. Let us look at a list of names in a cell.
Step 1: We should first split the string. Use the following formula.
SPLIT(A1,”, “)
Step 2: Now, let us use the JOIN function to add the surname “Devon” to the names. We can use ARRAYFORMULA to work on the entire array of names simultaneously. Enter the following formula in cell B1.
Step 3: Press Enter. We are now able to observe both the splitting and concatenation of strings.
Important Things To Note
- Delimiters in SPLIT in Google Sheets are case-sensitive. So, if you use “a,” for example, it splits on only the lower-case a’s in the text.
- If there is data present in any of the cells where the split string is to be displayed, it does NOT overwrite it but shows a #REF! error message.
- Use the keyword Arrayformula to input a range as the first argument to the SPLIT function, but it requires an Array Formula wrapper to work.
- If you want to split text into multiple rows rather than columns, consider using TEXTSPLIT in Google Sheets.
- The SPLIT function requires enough cells for its output. For example, if it splits a text string into five, it requires five cells.
- The output from the SPLIT function is an array of values which we can use in other formulas.
Frequently Asked Questions (FAQs)
● We use SPLIT to parse the data from CSV files. The data is split and stored in individual columns.
One also uses it to split the first and last names and for separating and concatenating strings.
For handling addresses separated by commas or line breaks, the SPLIT function separates them into distinct components.
It can be used to extract specific parts from text.
In Google Sheets, there are different ways of splitting cells into columns.
● First, highlight the cells to split.
● Navigate to the “Data” menu and click on it. Select the option “Split text to columns,” which splits the cells.
● You get a separator detector option where you can choose your separator type or choose “Detect Automatically.”
● It splits the string into separate columns.
You may also use the SPLIT() function to split cells in your spreadsheet. This function is simple and you must specify which cell to split and the type of separator. For example, =SPLIT(A2,” “) splits the content in A2 with space as a separator.
To split text with a line break, we can use CHAR(10) as the delimiter. For example: =SPLIT(A1, CHAR(10)) uses line break as a delimiter.
To use the SPLIT function with multiple delimiters in Google Sheets, combine the delimiters using the pipe – | character in the split text in Google Sheets formula.
Google Sheets SPLIT multiple delimiters: For example, to split the text in B2 by commas and dashes, you write =SPLIT(B2, “,|-“).
Download Template
This article must be helpful to understand the Split in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Split in Google Sheets. We learn how to use function Split in Google Sheets along with its formula and examples with a working template. You can learn more from the following articles.
Choose Function in Google Sheets
Leave a Reply