Knowing how to create split cells in Google Sheets can save you a lot of time when compiling data. This can be for names, addresses, personal information, and so on. You can even use this for storing items in your shop, as long as you have a proper format.
Now, there are two ways to split your cells.
2 Ways to Create Split Cells in Google Sheets
- Using the SPLIT function
- Using the Split text to columns feature
Both these methods have varying effects, but with the same outcome. We’ll explain in detail what we mean by that later on. Throughout this article, we’ll go over examples, the pros and cons of both methods, and when you should split cells in Google Sheets. If you aren’t familiar with Google Sheets at all, you have nothing to worry about!
The steps below are made with beginners in mind, so you should be able to just follow through the tutorial. Without further ado, here’s how you can create split cells in Google Sheets.
Let’s dive right in!
Why Create Split Cells in Google Sheets?
Splitting your cells helps you arrange data, spreadsheet entries, and other types of information. It’s a neat and easy way to separate, for example, a first name from a full name. This makes it much easier to retrieve those kinds of information through individual cells. Contrast that to accessing the full name and manually copying the first name from the entire line.
This feature is best utilized for registration forms, product names, and any spreadsheet that stores data. The appeal here is that you can copy-paste raw data into a cell block, and the split cell feature will separate the data into multiple columns.
Having your cells split this way makes it easier to read, access, and arrange in your spreadsheet. It’s especially useful for long text, that you would otherwise have to separate yourself and put into individual cells. Save yourself some time and learn how to split cells in Google Sheets!
Important Note When Splitting Cells
Before we go over the process of splitting your cell into columns or rows, we’ll have to go over an important consideration. Splitting your cells will occupy a long line, whether it be horizontal or vertical. This is because your data is stored in consecutive cell blocks every time it is split.
For example, let’s go over the picture below. As you can see, the cell with a full name only occupies one block. However, the split cells take up to 3 blocks, because there’s a first name, middle initial, and surname.
With that said, make sure that your spreadsheet already has enough space for you to split your cells into. This function will override and overwrite any data to its right once it starts to split the text into different cells.
Method 1: Using the SPLIT Function
First off, we’ll start a little technical here and begin by using the SPLIT function. Google Sheets is no stranger to useful functions, and SPLIT is one of them. In this method, we’ll show you how you can use the SPLIT function and explain what each parameter means!
Here’s how you can split cells with the SPLIT function.
Step 1: Open up your Google spreadsheet file.
To begin, we’ll need to open up a spreadsheet file so you can follow the instructions on your own. If you already have an existing document with cells you want to split, then you can use that as well. Alternatively, you can also create an exact copy of the document that we’ll be using today.
Step 2: Highlight the cell next to the address.
Before we can start typing out the function, go ahead and select the cell where you want your data to split into. For the ones who are following this tutorial with the spreadsheet copy provided above, select B2. Now, the ones with their personal spreadsheets, select anywhere but make sure that there is space to the right for the cells to split.
Step 3: Using the SPLIT function.
Now, we can get to the good stuff and finally use the split function. Start by typing out “=split(A2, “, ”)“ and pressing Enter.
=split() – function that splits the text in the cells
A2 – The cell with the raw data that you want to split
“, “ – This is the separator. The split function will look for the separator, in this case the comma and the spacebar (, ) and split the data whenever it finds the separator.
Your spreadsheet should now look like the picture below:
Step 4: Copy-paste the function to all the respective cells.
With that done, all that’s left is to copy-paste the function to all the respective cells. While B2 or the cell with the function is still highlighted, simply press both Ctrl + C keys. Then, highlight the entire column where you want the split cells to start, and press Ctrl + V.
Congratulations! You’ve successfully split the cells of your Google Sheets spreadsheet! With that being said, let’s go ahead and proceed to the next method, which shows a cleaner way to do this.
Method 2: Using the Split Text to Columns Feature
The next method you can do is to use the ‘Split text to columns’ feature that comes built-in with Google Sheets. You can use this if you already have data in your spreadsheet that are separated with the use of commas. Alternatively, separators like semicolons, periods, spaces, or any of your custom separators work just as well.
Let’s go over the steps on how you can do this.
Step 1: Open up your Google spreadsheet file.
Before we begin, you’ll need to open up a spreadsheet on Google Sheets. You can either use your own spreadsheet or create a completely new one for this method. We recommend creating a copy of the document that we’ll be using for this method. This is because splitting your cells will override and overwrite data. Knowing that, you might want to test it out first before working on your personal documents.
Step 2: Highlight the cells you want to format.
With your Google spreadsheet open, select the cells that you would like to split. We will use the same example as was used in Method 1. For the purposes of this guide, we will select A2:A7 as these cells contain the data that we would like to split. Simply do this by left-clicking and dragging your selection over the cells you want to split.
Step 3: Click on the ‘Split text to columns’ feature.
To find the ‘Split text to columns’ feature in Google Sheets, click on the Data tab from the top menu. From the drop-down list, you will find the Split text to columns option. Go ahead and click on that so you can split your cells.
After you’ve clicked that feature, your cells should automatically look like the image below:
Don’t click away! Before anything, go ahead and proceed to the last step below to see what you can do before the separator option vanishes entirely.
Step 4: Choosing the separator.
Before you click away, you can still change the format of how the cells are split. If you can notice the Separator: box, like the first method, this is the separator used by Google Sheets to split your cell. Although the default option is that Google will detect this automatically, you can click on the box and change it.
You can even set it to a custom separator, which helps you personalize how your data is split.
With that being said, the unfortunate part about this method is that Google by default will automatically split your cell. This can be problematic, especially if it splits your cell into more cells than you wanted, thereby overriding important data to the right. You definitely have much more control over splitting your cells when you use the function in Method 1.
How to Create Split Cells with Custom Delimiters in Google Sheets?
Alternatively, you may have a spreadsheet with custom delimiters that use spaces, commas and periods at the same time. If so, then there are two ways to go about creating split cells.
The first method is to simply choose the Custom option when presented to manually select the separator type (Step 4). The second method is to run the ‘Split text to columns’ feature twice or more depending on the number of delimiters you have.
The latter method is beneficial if you have a spreadsheet with two separators. For example, refer to the picture below where a comma and a semicolon separates the data. All you have to do is click the ‘Split text to columns’ option the first time to split the values according to the comma (,) separator. Then repeat the action again to separate the values that are still bound by the semicolon (;) separator.
The first two cells are separated by a semicolon (;) which separates the latitude and longitude. However, we’re left with 322, Beverly Hills, CA, 90210. To further separate this, we use the split method with a comma (,) separator.
Now that you’ve reached the end of this article, hopefully, we’ve helped you figure out how to create split cells in Google Sheets. Here’s a brief summary of all the steps we walked through.
Creating split cells in Google Sheets lets you consistently format your data. This is useful for long text that is inputted into the spreadsheet. Doing so keeps data concise and clean, allowing for easy reviewing and accessing. You can do this by either using the SPLIT function or the ‘Split text to columns’ feature that comes built-in with Google Sheets.
We hope you found this article helpful!