Last Updated on October 31, 2023 by Jake Sheridan
How To Split Cells In Google Sheets
Sometimes when working with data in Google Sheets you’ll need to split text from a single cell into multiple columns.
This often happens when you have an address that needs to be split into separate columns for street #, city, etc, or a full name that needs to be split into first and last name
Split Cells with Menu Option
One way to split cells into columns in Google Sheets is to use the menu option.
Use this option if you don’t want to deal with formulas and don’t expect your data to change in the future.
Here’s how to split cells using the menu option:
Select the cell you want to split, then go to the Data menu and choose the Split Text To Columns option
Your data will be automatically split into columns
Note that Google Sheets will look at the data and try to determine what character to use as a separator to split the text.
If it chooses the wrong separator, simply click on the dropdown menu that appears and choose the option that’s right for your data
Split Cells with Formula
If your data is likely to change later, you can save yourself the hassle of going through the above process repeatedly by using a formula instead.
This way you can update data in a single cell and keep your columns calculated properly.
Here’s how to use this method:
Select the cell in which you want the first column of the split data to appear and type the equals sign (=) to start typing a formula
Enter the SPLIT() formula.
This formula has two required parameters.
The first is a reference to the cell you want to split, and the second is the separator or delimiter, to split the cell on.
If the cell we want to split is C4, and we want to split the cell using the comma as the separator, this is what the formula would be: “=SPLIT(A4, “,”)”
Notice in the above screenshot how the formula is in column B, but the split data extends to column F.
This formula is an array formula that can display data in more than one cell.
The number of cells it spans will depend on how many pieces of data are in the original cell. Specifically, how many of the separator character are in the cell.
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to group rows in Google Sheets. Want more? Check out all the Google Sheets Tutorials.
More ways to clean data in Google Sheets:
- Clear Cells
- Clear Contents
- Clear Formatting
- Clear Filter
- Stop Google Sheets From Rounding
- Remove Dollar Sign
- Stop Google Sheets From Deleting Zeros
- Separate First And Last Name
- Format Phone Numbers
- Change Currency
- Add Dollar Sign
- Split Text To Columns
- Split Text to Rows
- Split Text
- Find And Replace
- Find And Replace Wildcard
- Swap Cells
- Check if a Cell is Not Empty