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.
You can easily do this using the below SPLIT formula (and copy for other cells in column B):
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.
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:
- Select the cells that have the address that you want to split
- Click the Data tab
- Click on Split Text to Columns option
- In the Separator dialog box, select comma as the separator
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
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:
- 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