How to Split Cells in Google Sheets (In 3 Easy Steps)

Sometimes when working on a spreadsheet, you may need to split text from a single cell into multiple columns.

A simple example would be when you have full names in a single column and you want to split these into first name and last name columns.

Or you have the customer’s complete address in one cell and you want to separate out the individual parts such as city, state, or Pin code to analyze them separately.

Fortunately, Google Sheets makes it quite easy to split the content of a cell into multiple columns.

In this tutorial, you’ll learn how to split cells in Google Sheets using the following techniques:

• Using the Text to column feature
• Using SPLIT function

01. Split Cells in Google Using Text to Column Feature

One of the easiest ways to split cells in Google Sheets is to use the text to column feature.

So, let’s say you have a dataset as shown below where you have the full names in a column and you want to split these names into first and last names.

Here are the exact steps you need to follow to split the full name into two separate cells:

1. Select the cell or cell range having the data to be split. In our case, I’ve selected A2:A8

Warning: Make sure the cells to the right do not already have data in them. The text to column feature will overwrite those cells without any warning.

2. Click on the Data menu.

3. Select Split Text to Columns option from the drop-down menu items.

4. In the Separator dialog box that appears at the bottom right of the data, choose a separator that suits your data.

Google Sheets has a few common separator options to choose from when you split up data, such as comma, semicolon, full stop, and space.

In our example what separates the first name and last name is “space“, so I am going to select space.

That’s it… This will instantly split the full name into two different columns.

What if your data is separated with something other than a comma, semicolon, full stop, or space?

No problem… Just select the custom separator option and enter your desired character or symbol.

Note: When you use the split text to columns feature, your result will replace the original cell content too.

In the above example, I had the full names in column A, but the final result replaced Column A with the first name.

So in case you want to keep the original data, either make a copy of your document or use the next method (SPLIT function) I am going to show you next.

02. Split Cells in Google Using SPLIT Function

If your data is likely to change later, you can save yourself from the hassle of going through the above process repeatedly by using the SPLIT formula.

SPLIT formula in Google Sheets helps you easily separate text into columns based on a delimiter.

A delimiter is a character or symbol you choose by which you want to cut the text. It can be any textual, numeric, or punctuation symbol.

The syntax of the SPLIT function is:

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

Here’s a quick rundown of what each of those means:

• = the equal sign is how we start writing any function or formula in Google Sheets.
• SPLIT is our function.
• Text is the text string or value in the cell that you want to split.
• Delimiter is the character or symbol we will use to split text.
• split_by_each is an optional argument which is set to TRUE by default. The TRUE behavior splits the data around each character in the delimiter. For example, if the delimiter is “123”, the data will be divided around “1”, “2”, and “3″. If you need to divide the data around the delimiter as a whole, set [split_by_each] to FALSE.
• remove_empty_text is another optional argument which removes empty text cells from the split fragments. If you want to turn it off, set [remove_empty_text] to FALSE.

Once you understand how the SPLIT function works, it’s time to see them in action…

Suppose you have the following dataset as shown below where you have a complete Address in Column A and you want to separate each part of the address into a different column.

Here’s how this can be done using the SPLIT function:

1. Click on the cell where you want to write your function. For the purpose of this example, I will be selecting B2

NOTE – Make sure you have enough empty space to the right where you want to spread your results.

2. Next, type the equal (=) sign to begin the function and then followed by the name of the function which is SPLIT

3. After the opening bracket ( you have to add the variables. The very first variable is text, you can either directly enter the text between quotation marks or reference a cell. For this example, I will reference the cell A2 (which contains the first address of our list)

4. Next, add the delimiter. This should be symbol or character by which you want to separate your text. For this example, I will use comma as delimiter (because each part of the address is separated by a comma)

5. Finally, add the closing bracket ) to complete the function and press the Enter key.

That’s it… This will split the full address into different cells.

03. Final Thought:

It is very common to have multiple pieces of data in a single cell, especially when you are combining spreadsheets or exporting data from a third-party source.

Instead of manually organizing these data into different cells, you can split cells in Google Sheets within a few clicks.

In this tutorial, I have covered two different ways to split cells in Google Sheets into multiple columns (using Text to Columns and SPLIT formulas)

Which one do you prefer to use?

Let me know in the comment section below…