How to Quickly Split Text in Google Sheets

How to Quickly Split Text in Google Sheets

A lot of times, you may want to split the text strings into two or more columns in Google Sheets.

For example, if you have names, you may want to split the first name and the second name.

Similarly, if you have an address, you may want to split the address elements (such as house numbers, street, city, state).

Split Text in Google Sheets

In this tutorial, you'll learn how to split text in Google Sheets:

  • Using the SPLIT function.
  • Using the Text to Column feature.

Split Text in Google Sheets Using the SPLIT Function

Here I have a list of some of my favorite fictional characters.

Split Text in Google Sheets - Names

To separate the first name and last name, use the following formula in cell B1:

=SPLIT(A1,” “)

This would instantly split the name and give the first name in cell B1 and last name in cell C1. Now copy paste it for all the cells or drag it down to apply the formula.

Split Text in Google Sheets - SPLIT function

Here is how it works:

SPLIT function takes two arguments:

  • The first argument is the text which you want to split.
  • The second argument is the delimiter that is used to separate the text strings.

In the above example, the delimiter is a space character.

NOTE: The benefit of using this method is that it is dynamic. If you change the name in any cell, the result would dynamically update.

Also, you won't be able to delete a part of the result. For example, you can not delete only the last name.

Split Text in Google Sheets Using Text to Column Feature

Another quick way to achieve the same result is using the Text to Column feature in Google Sheets.

Again let's take the same example of fictional characters.

Split Text in Google Sheets - Names

Here are the steps to split the first name and the last name:

  • Select all the cells in which you want to split the text.Split Text in Google Sheets - select cells
  • Go to Data –> Split text to columns.Split Text in Google Sheets - Text to Column
  • In the Separator drop-down. select the delimiter (which is space in this example).Split Text in Google Sheets - separator

This would instantly split the names into first name and last name.

Split Text in Google Sheets - text to column split text result

NOTE: This technique alters the original data set. If you want to keep the original dataset intact, make a copy. Also, the results are not dynamic, which means that if you make changes, you'll have to repeat this process again.

You May Also Like the Following Google Sheets Tutorials:

>