There are two methods of achieving the same goal, the first involves the menu option and the second uses a formula.
Method 1: Menu Option
Before starting, ensure there are a few empty columns to the right hand side of your worksheet. When the text is split it will spread across multiple columns
Step 1. Highlight the cells you would like to split and select the “Data” menu.
Step 2. Click “Split text to columns”.
Step 3. On the separator dropdown select “Space” and hit “Enter”. If you are splitting by a different character you can select period, comma, semicolon or enter a custom value if it is not on the list.
Step 4. Your have learned how to split data in Google Sheets and the text has been split into multiple columns. The Menu Option method displays the first name in the original split cell, this is not the case when using our next method.
Method 2: Split Formula
The second method we will look at for splitting cells utilizes the inbuilt Google Sheets function. The goal is to break up text, numbers and/or characters from one cell into multiple cells.
Step 1. Type =SPLIT( into an empty cell.
Step 2. Add parameters for the cell location of the text you want to split and the delimiter you want to use. In our case, this is “A1” and “ “ so the formula reads =SPLIT(A2, " ").
Step 3. Hit “Enter” and your text will be split. The formula can then be dragged to repeat the process for other cells.
Text is not the only data type we can split by in Google Sheets, numbers, dates and almost anything else can be split too. Spaces are not the only separator we can split by either, we can split by commas, semicolons, periods, or any other delimiter of our choosing. Notice how our existing data was untouched, this may factor into your decision making process when choosing which method to use.
The Google Sheets Split Function
This split function has two required parameters and two optional ones that allow us additional control over how our data is split.
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
text: here is where we input the text to be split. It can either be entered here directly or we can choose to put a cell reference.
delimiter: here is where we choose what character is used to divide the text. It can be a space, comma, letter, number, semicolon, colon, period or symbol.
split_by_each: This is an optional TRUE or FALSE field, which is TRUE by default. Generally, we can leave this parameter out but it’s useful when using multi-character delimiters.
remove_empty_text: This is also an optional TRUE or FALSE field, which is also TRUE by default. When TRUE, empty cells resulting from the split are omitted, when FALSE any empty cells created by the split are shown.
The split_by_each and remove_empty_text parameters are most easily understood through examples which we will move on to next.
Split By Comma
A lot of data is files arrive in .CSV format. When the data is not automatically split by Google Sheets we often need to do it manually. From what we have learned above it will be easy to use the comma delimiter to split the data but unless the data is perfectly uniform errors can arise. It also gives us a good chance to look at the optional third and fourth parameters of the split function.
Say we have the list of names, “James, Michael, Molly Mae, Joe, Elizabeth”, and we want to separate them into separate columns. We may think to use ", " as the delimiter and this would work in this case if one of the first names was not two words long.
However, as shown in the screenshot above, anyone with a two-word first name will be split into separate columns. By default the split_by_each parameter is set to TRUE so it look for either one, or both of the parameters we specified in the delimiter and because there is a space between the names, it was split.
We can get around this error by setting the split_by_each parameter to FALSE to get the result we wanted below.
To demonstrate how the remove_empty_text parameter works we will use a similar example but with the names "Walter, Cindy,, Joe, Linda". Notice the double comma. Now when remove_empty_text is set to TRUE as it is by default empty text is removed.
However, when it is set to FALSE, the empty text is not removed and we are left with an empty cell in our spreadsheet.
Most of the time we can avoid using the 3rd and 4th parameters of the SPLIT function but it is worth knowing how they work. They are often used when handling data in a format we do not expect.
Splitting Dates
Splitting dates works exactly as expected. However, we need to be careful in selecting the delimiter because dates can be written in many different formats. For example 02/12/2025, 02-12-2025, or 2nd December 2025 and each requires a separate delimiter.
=SPLIT("02/12/2025", "/")
=SPLIT("2-12-2025", "-")
=SPLIT("2nd December 2025", " ")
When dates come in formats we don't often use, sorting them can take up a lot of time. To fix this, we can perform a date split and rearrange it to fit our needs.
We hope this guide has helped you quickly split your data and you have learned more about the SPLIT function and how it works. We have covered some of the most common use cases and provided examples for each. However if you have a specific case that has not been covered in this article, reach out in the comments below and we may be able to help. Happy splitting!
