Often, you need to split the content of a cell in your spreadsheet that is separated by a comma or space, such as the first and last names of a contact. Luckily, Google Sheets provides a simple, straightforward approach to dealing with merged datasets.
How to Split Text in Google Sheets
Fire up your browser, open up a Google Sheets document, and select all the cells you want to split up.
Warning: Make sure the cells to the right don’t already have data in them. This feature overwrites those cells without any warning.
Next, click Data > Split Text Into Columns.
Sheets has a few common options to choose from when you split up data in your document, such as by comma, semicolon, full stop, and space. For this example, we’ll use “Space” as a separator, since there is a space between our datasets.
RELATED: How to Automate Google Sheets With Macros
Click the drop-down menu, then choose “Space” from the list of delimiter options.
Immediately, the data is separated and placed into the column beside the original.
If your data separates by something other than the options provided, don’t worry. You can use the “Custom” option to specify any character you want.
When selecting a separator from the drop-down menu, choose “Custom” instead.
Then, in the provided text field, input the character(s)* you want to separate by in your datasets.
*One caveat to this method is the inability to separate data if it contains two different characters. For that, you will need to use Sheet’s built-in SPLIT function, which we’ll cover down below.
Voila! The custom separator removed all instances of “@” in your cells.
How to Split Text Using the Split Function
While the previous feature only lets you specify an exact delimiter to separate, the SPLIT function lets you specify any number of them for a dataset. For example, let’s say your data is an email address and you want to extract the username, sub-domain, and top-level domain into three columns.
Select an empty cell, type =SPLIT(G14, “@.”), where “G14” is the starting cell. Hit Enter.
By default, each character of the delimiter in quotes is individually considered, unlike the previous method, which would look for “@.” in the string and return an unchanged dataset.
RELATED: The Best Google Sheets Add-Ons
Next, double-click the blue square to apply this function to the remaining cells.
The function does its magic and neatly removes the separators from your data.
That’s all there is to it. Now you can utilize the splitting power of Google Sheets to separate your data into different cells in your spreadsheet.
