You might know that you can merge cells, but did you know that you can split them as well? Occasionally, the data in one cell needs to be broken into separate columns. Knowing how to split cells is a great skill to learn. It can be used to speed up the creation of tables, as well as quickly import and sort important data into Google Sheets.
There are two methods to split cells in Google Sheets. These methods can be used to split text, numbers, or any data.
The first is to split cells using the Menu option.
The second way to split data is to use the Split Function.
I will show you how easy it is to split data in just a few steps using either method.
How to Split a Cell in Google Sheets with Menu Option
The menu option is easy, quick and straight to the point when you need to split data.
Step #1: Select the Cell
First, we will start off by selecting the cell that we want to split text inside. From there, you will need to click "Data" on the top menu.
Step #2: "Split Text to Columns"
In the "Data" drop-down menu, you will see a lot of options.
Near the bottom, you will see the option "Split Text to Columns."
Google Sheets opens a menu beside your text where you select how to split the cell. Once you select the delimiter used in your text, Google Sheets will automatically split it into multiple columns.
Step #3: Select How to Split the Data
To change which character Google Sheets uses to split the data, next to "Separator" click the dropdown menu. To fix how your columns spread out after you split your text, click the menu next to "Separator.
There are several options for how to split text into separate columns. The option you choose is determined by how the contents of the Cell are separated. The available options are:
- Automatic
- Comma
- Semicolon
- Period
- Space
- Custom
I would personally avoid using the "Automatic" option because it doesn't work that well. "Custom" would be a better option because you should be able to quickly determine and manually input the dividing variables.
You can use this technique to split text: dates, time, numbers, or whatever else.
So long as there is a constant form of separation between the variables, you can split it.
Google Sheets Keyboard Shortcuts
To quickly divide data in a cell use Google's keyboard shortcuts. Most shortcuts will work if Compatible keyboard shortcuts are enabled.
To enable the shortcuts, press Ctrl+/ then go to the bottom of the pop-up window to click the toggle button to enable them.
Press Alt+a,e to use the shortcut for "Split Text to Columns."
This shortcut works on one cell or multiple cells.
Using the SPLIT function in Google Sheets
The SPLIT function allows you to manipulate the source data in a cell and only customize the data that is split, whereas the Merge Cells option does the opposite
What the SPLIT function does is divides text around a specified character or string, and puts each fragment into a separate cell in the row. The number of cells will vary with the amount of split fragmentation which occurs after splitting.
Let's say you have a list of names that you want to split cells from one column into two or even multiple columns.
First, put your cursor in the empty column beside the cell you want to split.
Next, type an "=" sign in the formula area followed by "SPLIT." As you are typing, you will see SPLIT function appear.
After =SPLIT, choose the cell that you want to split. The cell designation will appear in the formula area.
Tell Google Sheets which delimiter you are using to indicate the split. We are using the "space" delimiter.
Complete the split formula like so "=SPLIT(cell," ")" with a closing parenthesis and hit Enter.
Now, your list of names is in two columns using the Split function.
Split Text using Zapier
If you need to split text one time, like when you add a name to your email list, you can set up a split text automation using Zapier.
Here's am example: your eCommerce app sends you a new customer's name as First Name and Last Name together in one cell. However your mailist program splits the names into separate columns of first and last names.
You don't want to have to edit that text every time someone makes a purchase do you? What a headache.
No worries, you can create an automation in Zapier that can automatically copy the customer info from your store and then add it to your mail provider. And it will split the text for you, as well.
Here's how to do it:
- Connect the trigger app to Zapier: This is the app that has the text that you want to split. Set the trigger to copy whatever data you want to split.
- Next: Add a Formatter step to the Zap. Select the Text as the action and then select Split Text transform.
- Click the + button beside the Input field and select the field from your first app with the text that needs split. If your text is separated by spaces, you can leave the Separator field blank; otherwise, enter the character(s) that splits your text.
- Finally, in the Segment Index field, choose if you want the First, Second, Last, or Second to Last item from your split text—to get the first name, say, you'd choose the First segment.
- Click Continue and test your Zap, and you'll get the plain split text you need.
As you've read, there are a couple different ways for you to split text and other data in Google Sheets. Choose the way that works best for your situation.