Sometimes you have data with long strings of text that you want to split into separate columns. Like when you need to separate full names into salutations, first, and last names in Google Sheets.
Similar to extracting a substring, it may not be as straightforward but once you understand how to do it, it’s quite simple!
In this tutorial, I’ll show you some ways how to split text to columns in Google Sheets.
How to Split Text to Columns in Google Sheets
To split text to columns in Google Sheets:
- Select the cell with the text that you want to split
- Go to ‘Data’ and select ‘Split text to columns’
- Select your ‘Separator’
- Alternatively, use the SPLIT function “=SPLIT(‘text’,’delimiter’)”
How to Split Text To Columns Video Tutorial
2 Easy Methods to Split Text to Columns in Google Sheets
To ‘Split Text’ means that you’re going to be using Google Sheets functionalities to fragment a text around a certain character or string of your choosing.
It will then put each separate piece into its own cell in the same row as the source text.
The ‘delimiter’ can be anything from a dot to a phrase and it will be used as the dividing point of the text to determine the parts to be distributed along the next few columns.
If the delimiter cannot be found inside the source text, the result will just be the same as the text with no changes.
Consequently, if there is no limiter an error will occur as seen on the screenshot above.
Split Text to Columns in Google Sheets By using the ‘Split text to columns’ menu option
The first step in this method (menu option) is to have at least a single non-empty cell.
Once you have selected the cell containing the string that you to split, go to ‘Data’ and select ‘Split text to columns’.
A small window with a drop-down menu will appear. Click on it.
Once you have clicked on it, the drop-down list should show up. You need to select the separator that you’ll be using.
This will serve as the “divider” that Google Sheets will use to split the text into columns.
Right after selecting your separator, the text will be split into the next few columns (depending on how long your original text is the number of fragments may exceed your total number of columns and may require you to add more columns to the sheet).
Using the menu option to split text to columns in Google Sheets also works for multiple selections of strings.
This is particularly easier and simpler to use than the SPLIT function but only for situations where your whole selection will only require the same separator.
Otherwise, I highly recommend that you use the SPLIT function to split text to columns in Google Sheets.
Split Text to Columns in Google Sheets By using the SPLIT function
The SPLIT function follows this syntax:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Parallel to the first method, the ‘Text’ parameter indicates the text that you wish to split and the ‘Delimiter’ parameter is the ‘separator’. The other two parameters are optional.
The example below shows alternating rows of personnel information containing their names, email addresses, and birthdays.
Using the former method to split text to columns in Google Sheets will not be so easy as you’ll have to select the cells separately. Even more so in databases with thousands of rows.
Before using the SPLIT function, we need to prepare the text to be split and in addition, we also need to list down their respective ‘delimiters’ or ‘separators’.
Afterward, we just have to use the fill handle by pointing the mouse over to the lower-right corner of the first cell with the formula at the top.
In doing so in this example, we were able to split 12 strings in one full sweep.
We can use the new data that we have to gain more insights into our personnel’s information. To see the data better, we can start by sorting them.
Afterward, we can now see the data grouped according to their type.
We can assemble the group data and attach them with labels for the best visual output.
This way, we can get more relevant data depending on the dataset that is available and what we actually need. Depending on the strings, you may modify your delimiters to further reflect more accurate results.
One difference between using the SPLIT function over the ‘Data’ menu ‘Split text to columns’ option is that the source text disappears in the latter option.
Frequently Asked Questions about How to Split Text to Columns in Google Sheets
Is it possible for me to split a text into rows in Google Sheets?
You can split a text into rows by combining the SPLIT and the TRANSPOSE functions. You can use the formula “=TRANSPOSE(SPLIT(text, delimiter))”. Make sure that you have enough space below your source text.
What could be the problem when I’m getting an error when I try to split text to columns in Google Sheets using the SPLIT function?
You may have a “#REF!” error if there is not enough space in the spots where the split text should show up. You may also get a “#VALUE!” error if either the text or the delimiter parameter is missing.
Conclusion On How To Split Text To Columns In Google Sheets
To split text to columns in Google Sheets, select the cell with the text that you want to split then go to ‘Data’ and select ‘Split text to columns’. Afterward, select your ‘Separator’. Alternatively, you can use the SPLIT function “=SPLIT(‘text’,’delimiter’)”.
