Last Updated on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to split text in google sheets.
Google Sheets is a great tool for performing calculations and collaborating with other users, but sometimes getting the data into a spreadsheet can be frustrating. If you’re copying data from another service, you may find that data comes into Google Sheets incorrectly, often with multiple columns of data combined into a single cell where values are separated by commas. Manually separating the data into the appropriate columns would be time consuming and error-prone, but fortunately Google Sheets has a feature that makes splitting text into columns quick and easy.
Identifying Splittable Data
Before proceeding, it’s important to recognize that data must be formatted correctly in order to be splittable. What this means is each value in a given line must be separated by a specific character, such as a comma or semicolon. For example, the following text could easily be split into three values based on the commas: “Fred Love, Atlanta, 55”.
If the values aren’t separated by a particular character, Google Sheets will be unable to figure out where one value ends and the next begins.
Splitting Text To Columns
Once you’ve determined that your data is formatted such that it can be automatically split, here’s how to do so:
Ensure there are blank columns after the column in which the text you want to split is located so that the text can be split without overwriting other data
Select the cells containing the text to split and open the Data menu, then choose Split Text to Columns
Google Sheets will automatically detect the character to split on based on the data and your text will be split to columns
Immediately after splitting the text to columns, a small popup will appear over the split text with a dropdown where you can choose a different separator character
Most of the time, Google Sheets will be able to detect the appropriate separator character without trouble, but if you need to make a correction, simply click on the Separator dropdown and choose an option from the list. In addition to the frequently used comma, semicolon, period and space characters, you can also choose a custom character to split the text on
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to split text 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 Cells
- Find And Replace
- Find And Replace Wildcard
- Swap Cells
- Check if a Cell is Not Empty