How to Use the SPLIT Function in Google Sheets
When working with spreadsheets, it’s common to encounter situations where you need to divide the text into smaller, more manageable parts. This is where the SPLIT function in Google Sheets comes in handy.
By specifying a delimiter, this powerful function allows you to separate text strings and get desired information effortlessly. In this guide, we will show you how to use the SPLIT function in your spreadsheets
Syntax of the SPLIT Function in Google Sheets
The basic syntax of the SPLIT function is:
Let’s break down each parameter:
The square brackets indicate optional parameters, so you can omit them if you don’t need to specify those options.
Now, let’s consider some examples to better understand how the SPLIT function works:
Splitting Text Into Columns
Let’s say you have a column (A) with a list of full names, and you want to split the full names into separate columns. Enter the formula below in cellB2.
Drag the formula down toautofill all relevant rowsin column B. This will split the full names in column A into two separate columns (B and C) for first names and last names, respectively.
You can also join the separate columns into a single column with the full names using CONCATENATE. Read our article on theCONCATENATE function in Google Sheetsto learn how the function works.
Extracting Specific Text
You can also combine SPLIT with the INDEX function to extract specific values from text strings. Here’s an example:
Let’s say you have a column containing addresses in the formatCity, State, Postal Code, and you want to extract the postal code from each address. Enter the following formula in cellB2:
In this formula, the SPLIT function splits the text in cellA2into separate values based on the comma followed by a space delimiter (", “). The INDEX function then retrieves the third value from the resulting array, which corresponds to the postal code in the address.
you could adjust the formula based on the structure of your data and the position of the desired information. For example, if the postal code is in the second position, change the index value in the formula to2to retrieve it.
Splitting URLs
Suppose you have a column of URLs, and you want to retrieve the domain names. You can use the SPLIT function as follows:
Here, the SUBSTITUTE function removes thehttps://part from the URL in cellA2. The SPLIT function separates the remaining text into the domain name and path, based on the forward slash ("/") delimiter.
In the example shown above, there are no paths after the domain names in the URLs. So, the SPLIT function will return only one element, which is the domain name.
Efficient Text Separation With the SPLIT Function in Google Sheets
The SPLIT function in Google Sheets is a handy tool for dividing text strings into separate cells based on a specified delimiter. By using SPLIT, you could extract valuable information from a larger string of text or manipulate data in a more organized manner.
Whether you need to split names, addresses, or any other type of data, the SPLIT function provides a simple yet effective solution. So, give it a try and explore the possibilities of the SPLIT function in your spreadsheets.
The CHOOSE function in Google Sheets allows you to retrieve a value from a list based on its index or position. Here’s how to use it.
My iPhone does it all, but I still need my dumb phone.
It’s not super flashy, but it can help to keep your computer up and running.
This small feature makes a massive difference.
These films will leave you questioning humanity, but also wanting more.
Freeing up vital memory on Windows only takes a moment, and your computer will feel much faster once you’re done.
