How to Split Cells in Google Sheets

Some cells can contain information that must be split into columns. Learn 2 easy methods, via the Split Text tool and SPLIT() function, on how to do this task!

Table of Contents
  1. A Brief Talk on Separators
  2. Using Split Text to Columns Option
  3. Using the SPLIT() Function
  4. Splitting to Three or More Columns
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. A Brief Talk on Separators
  2. Using Split Text to Columns Option
  3. Using the SPLIT() Function
  4. Splitting to Three or More Columns

Some cells can contain information that must be split into columns. The most common type of such information are the names. Before we consider two methods of splitting such cells, let us have a short talk on the separators used to distinguish different columns of data.

A Brief Talk on Separators

Separators, or delimiters, are symbols used to separate cells of data in two-dimensional arrays, or tables. The common characters used are comma, colons, semicolons, and brackets. They are used widely not just for data storage but also for programming (to separate lines of code). 

One of the most popular spreadsheet file formats is the CSV format, where CSV means comma-separated values. You can also easily edit the file using a notepad program of your choice, as each line corresponds to a single row and the commas mark the separation between columns. To store commas inside cells, one can simply enclose the entire content of a cell with a pair of double quote marks.

Using Split Text to Columns Option

We will consider the Split Text to Columns option in Google Sheets. 

Step 1: After selecting the cells to split, click Data on the top menu, and then scroll down the drop-down box to select Split text to columns

Data option from menu, split text to columns highlighted


Step 2: A small box will appear near the end of the column of cells you want to split, with the label Separator. Click on the box with the label Detect automatically, and select Space from the options. 

Separator options. Space highlighted.


The column is now split into two:

Column split to two. The first column replacing the original column.
You might have noticed, however, that the original column was replaced by the first column of the split text. We have another solution to solve it: by using the SPLIT() function.

Using the SPLIT() Function

The SPLIT() function requires you to specify the cell to split and the separator used inside the cell. For our case, our separator is the space. So we can write the formula as follows:


=SPLIT(A2," ")


The equation will automatically use the next columns if there are extra delimiters detected. You can simply copy the cell with the formula down the column:

Column split to two using SPLIT() function. The original column is not replaced.

The disadvantage of this method is that the values are not static and depend on the value of the original column. You can simply follow the steps here.

Splitting to Three or More Columns

You can separate the text to more than two columns, depending on how the string and separators are configured! Here is an example, where the separator used is the single space. This can be done with either of the methods above.

a column split to five columns


Regardless of the method you choose, what matters the most is that you correctly choose the separator to split the columns!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

September 15, 2020

How to Split Cells in Google Sheets

Google Sheet spreadsheet

Some cells can contain information that must be split into columns. The most common type of such information are the names. Before we consider two methods of splitting such cells, let us have a short talk on the separators used to distinguish different columns of data.

A Brief Talk on Separators

Separators, or delimiters, are symbols used to separate cells of data in two-dimensional arrays, or tables. The common characters used are comma, colons, semicolons, and brackets. They are used widely not just for data storage but also for programming (to separate lines of code). 

One of the most popular spreadsheet file formats is the CSV format, where CSV means comma-separated values. You can also easily edit the file using a notepad program of your choice, as each line corresponds to a single row and the commas mark the separation between columns. To store commas inside cells, one can simply enclose the entire content of a cell with a pair of double quote marks.

Using Split Text to Columns Option

We will consider the Split Text to Columns option in Google Sheets. 

Step 1: After selecting the cells to split, click Data on the top menu, and then scroll down the drop-down box to select Split text to columns

Data option from menu, split text to columns highlighted


Step 2: A small box will appear near the end of the column of cells you want to split, with the label Separator. Click on the box with the label Detect automatically, and select Space from the options. 

Separator options. Space highlighted.


The column is now split into two:

Column split to two. The first column replacing the original column.
You might have noticed, however, that the original column was replaced by the first column of the split text. We have another solution to solve it: by using the SPLIT() function.

Using the SPLIT() Function

The SPLIT() function requires you to specify the cell to split and the separator used inside the cell. For our case, our separator is the space. So we can write the formula as follows:


=SPLIT(A2," ")


The equation will automatically use the next columns if there are extra delimiters detected. You can simply copy the cell with the formula down the column:

Column split to two using SPLIT() function. The original column is not replaced.

The disadvantage of this method is that the values are not static and depend on the value of the original column. You can simply follow the steps here.

Splitting to Three or More Columns

You can separate the text to more than two columns, depending on how the string and separators are configured! Here is an example, where the separator used is the single space. This can be done with either of the methods above.

a column split to five columns


Regardless of the method you choose, what matters the most is that you correctly choose the separator to split the columns!

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.