How to Split Cell in Google Sheets (into separate columns)

Slicing and dicing data is quite common for people working with text data in Google Sheets.

And one of the common tasks a lot of people have to do often is to split a cell in Google Sheet into separate columns (two or more than two).

A very simple example of would be when you have the first and the last name and you want to split the first and last names into separate columns.

Or when you have an address as a whole in cells and you want to separate out the individual parts such as house number, street, city, state, etc, in separate columns.

Thankfully, it quite easy to split the content of a cell in Google Sheets. And there is more than one way you can split cells and separate these into multiple columns (or rows in case your data is arranged in a row instead of a column)

In this tutorial, I will show you how you can use a simple formula and the text to columns feature to split cells in Google Sheets.

So let’s get started!

Split Cells into Columns Using the SPLIT function

Google Sheets has a SPLIT function that’s well suited for… you guessed it… split the contents of the cell.

Suppose you have the dataset as shown below where you have the names and you want to split these names into first and last names.

Dataset to split cells in Google Sheets

You can easily do this using the below SPLIT formula (and copy for other cells in column B):

=SPLIT(A2," ")

Formula to split cells in Google Sheets

The above SPLIT function takes the cell reference as the first argument and the delimiter as the second argument.

In this example, since we want to split the cell content before and after the space character, I have specified a space character (in double-quotes) as the second argument.

Similarly, if you have a column that has an address (where each address element is separated by a comma), you can use the comma as the delimiter to split the address into different columns.

Note that this is an array formula. So you can not remove or edit a part of the formula result. You will have to delete the entire formula result. In case you want to edit part of the result, you can first convert the result into static value and then edit it.

One benefit of using the SPLIT formula is that it gives you a result that is dynamic. This means that if I go and change any of the names, the result would automatically update.

Also, if you add more records to the data, all you need to do is copy and extend the formula for these additional records and it will split these as well.

But in some cases, you may not want dynamic results and need to quickly split the cell content into columns. In such cases, you can rely on another useful feature in Google Sheets – Text to Columns

Using Split Text to Columns

Suppose you have the below dataset again, and you want to split the address into individual elements in separate columns.

Address data to split in google sheets

The intent here is to split the address in each cell into separate columns so that we get the house number in one column, street in the second column, the city in the third column, and so on.

Also, note that each of these address elements is separated by a comma.

Below are the steps to split the cell into multiple columns using Split Text to Columns feature:

  1. Select the cells that have the address that you want to split
  2. Click the Data tab
  3. Click on Split Text to Columns optionClick on Data and then click on Split Text to Columns
  4. In the Separator dialog box, select comma as the separatorSelect comma as the separator

That’s it!

You will notice that the content of the cells has been split into columns based on the delimiter.

Address that has been split

Also, you may get the desired result after the third step only as Google Sheets sometimes correctly guesses the delimiter and show you the split cell content right there. If that’s what you wanted, you can skip the fourth step (where you manually specify the delimiter)

You can also watch the video below to learn more about how to split cells using text to columns

When you use Split Text to Columns, it will replace the current text with the result. In the above example, you can see that column A had the complete address and in the results, the full address is not there. So in case you want to keep the original data, either create a copy of the Google Sheets document or copy and paste the address in another column and then split it.
Caution: In case there is any text in any of the cell where the result of text to columns would appear, it would be overwritten without a warning.

In some cases, you may have a dataset, where the delimiter is not a comma, but a mix of say comma and space. In such a case, you can consider using Split Text to Columns twice, once to split the cell based on the comma and then a second time using space.

So these are two really simple and fast ways to split cells into columns in Google Sheets.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may find useful:

Leave a Comment