How to Separate String in Google Sheets

This guide will explain how to separate string values in Google Sheets.

Google Sheets provides several methods to separate strings into distinct parts. This may be necessary if you’re given a value such as an address and you want to separate the value into specific parts such as the street name, city, or country.

Separating strings for this purpose requires your data to have delimiter characters. These characters define how the string should be separated. Common delimiters include spaces, tabs, and commas. 

In this guide, we will provide a step-by-step tutorial on how to separate strings in Google Sheets. We will cover how to use the SPLIT function to create a formula that splits a string by a delimiter. 

We’ll also cover how to separate a delimiter-separated string into multiple columns using the Text to Columns tool.

The Anatomy of the SPLIT Function

The syntax of the SPLIT function is as follows:

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

Let’s look at each argument to understand how to use the SPLIT function.

• SPLIT() refers to our SPLIT function. This function accepts text and a corresponding delimiter string and returns an array of cells consisting of the original text split around the specified delimiter string.
• text refers to the text value you wish to divide.
• The delimiter parameter should be the character or characters to use when splitting the text.
• split_by_each is an optional argument that sets whether or not to divide the given text around each character contained in the delimiter.
• remove_empty_text determines whether or not SPLIT will remove empty text strings from the split results. By default, this is set to TRUE.

A Real Example of  Separating Strings in Google Sheets

Let’s explore a simple example where we’ll need to separate strings in Google Sheets.

Using the SPLIT Function

We can use the built-in SPLIT function to separate strings by a particular delimiter character. For example, given the string “apple,banana,cherry”, we can use the SPLIT function to return an array with the values “apple”, “banana”, and “cherry”.

In the table above, we have a list of locations that we want to separate into city and country values. We can use the following Google Sheets formula to accomplish this:

=SPLIT(A2, ",")

The formula above uses the text in cell A2 as input and divides the text by the comma delimiter.

After evaluating the formula, the SPLIT function returns a horizontal array of cells with the split values.

Using the Text to Columns Tool

If you want to split an entire column or range of delimited data at once, we recommend using the Text to Columns feature in Google Sheets.

Click on Data > Split text to columns, and the tool will automatically overwrite the selected range with new values split by a delimiter.

The separator or delimiter character will be detected automatically. The user may also choose the separator to use manually.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to separate strings in Google Sheets.

How to Separate Strings in Google Sheets

1. Select the cell where you want to place the SPLIT function. Ensure that there is enough empty space to the right of this cell to accommodate the output.
   
2. Next, type the SPLIT function. Provide the text you want to split as the first argument.
   In this example, we’ll start by separating the city name and country name from the string “Paris, France”.
   
3. Next, provide the delimiter you want to split the string by.
   In our example, the city name and country name are separated by a comma. We’ll input the character “,” as our delimiter argument.
   
4. Hit the Enter key to evaluate the SPLIT function.
   
5. You can use the AutoFill tool to apply the SPLIT function to the remaining text values in column A.
   
6. To separate a range of strings into two or more columns, we can also use the Text to Columns tool. We’ll start by selecting the range containing the text you want to split.
   
7. Next, click on the Data menu and select the Split text to columns option.
   
8. Google Sheets will now overwrite the selected range with an array containing the data separated into multiple columns.
   

These are all the steps you need to know to start separating strings in Google Sheets.

FAQs

1. Can the SPLIT function handle multiple delimiters in a single string?
   Yes, the SPLIT function can split a given text value using multiple delimiters. Set the split_on_each function to TRUE to enable the function to split a text value by any character found in the delimiter argument.
   
   
   
2. What happens if the SPLIT function doesn’t find the delimiter in the string?
   If the delimiter is not found, the function will return the original string without any splitting. 

To learn more about manipulating text in Google Sheets, you can read our post on how to use the TRIM and SPLIT function together in Google Sheets.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!