Last Updated on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to split text to columns in Google Sheets.
How To Split Text To Columns in Google Sheets
When dealing with large datasets, you may find yourself needing to split text into multiple columns. For example, you may have a data set that contains a column that holds full names, and you want to split the names into different columns for first name, middle name, and last name.
In Google Sheets, there are two ways you can split text to columns. You can use the SPLIT function, which is a powerful formula that allows you to split text based on a specific character or delimiter.
You can also use the split to text feature, which is a quick and easy way to split text without the need for a formula.
In this guide, we will show you how to split text to columns in Google Sheets using both the SPLIT and split to text feature.
How To Use the SPLIT Function and ‘Split text to Columns’ Feature in Google Sheets
Here’s how to use the SPLIT function and ‘Split text to columns’ tool on text in Google Sheets.
First, we’ll explain how to use the SPLIT function to split text into different columns.
In this example, we’ll split a phrase or sentence in column A into individual words in columns C to H.
Select an empty cell and type “=SPLIT(“ to start the SPLIT function.
The first argument of the SPLIT function should be the text you want to split or a cell reference to another cell with the desired text.
Next, specify the delimiter in the second argument. The delimiter will be the basis for splitting the chosen text.
In this example, we’ve chosen a space character as the delimiter. This allows us to split our target text into individual words.
Use the AutoFill tool to return the rest of the split output.
If you prefer not to use a formula, Google Sheets also includes a built-in option to split text.
To use the Split text to columns tool, you must first select the cells you want to split.
In this example, we will split cells containing several comma-separated values.
In the Data tab, click on the Split text to columns option.
Google Sheets should automatically detect that the selected cells contain comma-separated values.
Google Sheets will now overwrite the selected range with a new range containing the split values.
When the delimiter is not immediately clear to Google Sheets, a prompt will appear asking the user what separator to use.
Users can choose from a variety of common separators and can even indicate a custom option.
In the example above, we’ll use the custom separator “/” to extract the domain name from a list of URLs.
This guide should be everything you need to split text into columns in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.
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 Rows
- Split Cells
- Split Text
- Find And Replace
- Find And Replace Wildcard
- Swap Cells
- Check if a Cell is Not Empty