How to Quickly Split Text in Google Sheets

In this article, we’ll learn how to quickly split text in Google Sheets. We’ll see more than one way of splitting text data.

Text data often requires some preprocessing before we can use it. That’s why Google Sheets offers a wide variety of text-based functions as well as built-in functionalities to manipulate text data. Occasionally, we might need to separate parts of our text data. In other words, we might need to split our text based on some delimiter, as we will see in this article. 

To clarify this, let’s say we have a Names Data spreadsheet. The Name column has some names and we need to split these names into first and last names. As of now, the First Name and Last Name columns are empty.

This is exactly a scenario where we need to split text data, one most frequently used technique is to use delimiters. Now, what is a ‘delimiter’? It is a character that separates parts within a text. Can you guess what the delimiter is in this case? At this moment, If you cannot sort out the delimiter in the Name column then stay tuned as we will eventually use it. 🙂

So how do we go about this problem?

We’ll see the SPLIT function and its use here. Moreover, we will learn Google Sheets’ Split Text to Column Feature, which is yet another useful built-in functionality to split text data. By the end of this article, you will be familiar with both these techniques for splitting text data in Google Sheets. 

First, we’ll learn the use of the SPLIT function. Then, we’ll move on to Google Sheets’ Split Text to Column Feature. So, let’s get started.

How to Quickly Split Text in Google Sheets Using SPLIT Function

We have already seen the SPLIT function in one of our articles titled, “How to Use SPLIT Function in Google Sheets”. We suggest that you should go through that guide first before reading this tutorial. It’ll introduce the syntax and usage of this function.

We’ll be using the SPLIT function in our Names Data spreadsheet to split the names into first and last names. Let’s see how.

1. Activate the first cell B2 in the First Name column.

2. Next, type the equal sign “=” to start. Type “SPLIT”. You can type it either in the cell itself or the formula bar above.

3. Click on the autocomplete option to get the formula as “=SPLIT(”.

4. Now, we have to pass the arguments. The first argument will be the reference to cell A2. The second argument is the delimiter. We’ve hinted at delimiters in the intro. We will see its use here. In this case, blank space “ “ is a delimiter. Complete the formula with a closing bracket “(”.

5. Click Enter and you’ll see it splits up the name into first and last names.

6. Now drag the blue box all the way down to cell A14. Google Sheets will automatically do the splitting for the rest of the names in the Name column.

7. Voila! Simple process but quite useful to split strings based on a delimiter.

Next, we’ll see how to use Google Sheets’ Split Text to Column feature to split names.

How to Quickly Split Text in Google Sheets Using Split Text to Column Feature

Split Text to Column is Google Sheets’ built-in feature for splitting text based on a delimiter. It doesn’t involve the use of any formula and we can use it for our Names Data spreadsheet. However, before we start the tutorial, we’ll reformat our spreadsheet a little bit.

We will rename the Name column as ‘First Name’. The First Name column becomes ‘Last Name’. By the end of the tutorial, you’ll understand why we had to make these changes.

We’ll proceed with this modified version of our Names Data.

1. Select the First Name column, ranging from A2:A14.

2. Navigate to the Data in the top menu.

3. In the Data drop-down, navigate to Split text to column.

4. After that, you’ll see a pop-up titled Separator appears.

5. The Separator actually asks for the delimiter which is the Space in our case. So we would select it.

6. We’re done!. Google Sheets separates the names into first and last names. Therefore, we need only two columns here and that’s the reason why we had formatted the Demo spreadsheet 🙂

We hope you’ve learned both the ways of quickly splitting text in Google Sheets.

That should be all you need. You have learned how to quickly split text in Google Sheets by using the built-in function as well as the SPLIT function. Check out our other numerous Google Sheets formulas to create even more complex and useful functions in Google Sheets.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe