How To Split Text In Google Sheets

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:

Step 1

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

Step 2

Select the cells containing the text to split and open the Data menu, then choose Split Text to Columns

Step 3

Google Sheets will automatically detect the character to split on based on the data and your text will be split to columns

Step 4

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

Step 5

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.