How to Split Cells in Google Sheets (Into 2 or More 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 is quite easy to split data in 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 learn how to split cells in Google Sheets.

So let’s get started!

In Short: How to Split Cells in Google Sheets

Here’s how to split a cell horizontally in Google Sheets:

1. Select the data
2. Go to Data
3. Choose Split text to columns
4. Select the delimiter

Up Your Skills First

The G Suite is Google’s powerful collection of apps and programs that can exponentially increase your productivity. But only if you know how to use them efficiently. Browsing our site will help with this, but for those that like a little more structure to their learning, check out the excellent G Suite guides on Udemy.

How to Split Cells in Google Sheets Using the SPLIT function

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

Split Function Syntax

The split function is used in the Google Sheet split text to columns fucntion by dividing texts around a specified character or string.

SPLIT( Text, Delimiter, [split_by_each], [remove_empty_text])

Text: The text that is to be split.

Delimiter: The characters that will be used to split the text. The delimiter i s considered as each individual character meaning if the word was “and” then the text will be split around each character “a”, “n” and “d”

Split_by_each: This argument is used to indicate whether the delimiter should be used as a whole word or individual characters using TRUE or FALSE.

Remove_empty_text: This argument is used to decide whether or not to remove empty text messages from the split text.

How to Use the Split Function

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.

.

Here’s how to split a cell in half in Google Sheets using the below SPLIT formula (and copy for other cells in column B):

=SPLIT(A2," ")

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 split 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.

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 option
   

   

4. In the Separator dialog box, select 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.

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 cells 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.

How to Split Date and Time in Google Sheets

You can split date and time in Google Sheets using the SPLIT formula. This method will work the same way as the other Google spreadsheet split text to columns methods. All you need to do is decide on the delimiter to use. Let’s look at our example spreadsheet below:

To split the date and time to separate columns, we would use the formula =SPLIT(A2,” “) in the cell for the date column.

This will split the date and time using the space character as the delimiter like below

You can then copy the formula to the other cell by clicking and dragging the square at the bottom right corner of the cell.

You can also use the inbuilt split option in the data menu the same way. However, this method will split the names in the original column and the next column, unlike the SPLIT function.

Tips When Splitting Cells

So far, we have shown you how to split cells in Google Sheets. Here are some extra tips for Google spreadsheet split cell:

• If you want the data split to be dynamic then use the SPLIT function instead of the split menu option.
• If you want to split the data into new columns using the split menu option, you can copy the column before splitting to get two new columns and retain the original column.
• You can always use the keyboard shortcut CTRL+Z on windows and Command+Z on Mac if you want to combine the cells again.
• You should filter your data first before you split the cells.

Frequently Asked Questions

Can You Split Cells in Google Sheets?

You can split cells in Google Sheets using the SPLIT function or using the inbuilt split menu. These methods makes Google Sheets split cells horizontally so that you end up with two columns of data.

How Do I Split Cells Into Rows?

In order to have Google Sheets split cells into rows, you will need to combine the SPLIT function with the TRANSPOSE function.

For example, in our demonstration sheet, we will split the words in cell A2 into separate rows using the formula 

=TRANSPOSE(SPLIT(A2," "))

Wrapping Up

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

I hope you found this tutorial useful and you now know how to split a cell in Google Sheets like a pro! If you found this article useful you might also be interested in learning how to merge and unmerge cells in Google Sheets.

If you found this guide on how to split cells in Google Sheets useful, check out awesome of our related content below.

Related:

• • • How to Get the Word Count in Google Sheets
    • How to Change Text Case in Google Sheets (Upper, Lower, or Proper)
    • How to Wrap Text in Google Sheets (with a single click)
    • Remove the First Character from a String in Google Sheets
    • How To Remove Duplicates In Google Sheets
    • How to Sort By Multiple Columns in Google Sheets
    • How to Highlight Duplicates in Google Sheets