Slicing up data is something that most people who need to compile results from various sources probably have to do on a fairly regular basis when it comes to their job. A good example of how one has to correct data formatting can be seen when working with text.
Perhaps you have run into a situation where you want the first and last names in separate columns. For example, if someone uses your spreadsheet as an address book but wants to ensure each surname gets its line.
What if you want to separate a complete address in cells into columns? For instance, let us say you want to separate house number, street name, city, and state from your customer address?
Thankfully, it is straightforward to split up a cell’s content into multiple columns with the Google Sheet functions. There are a couple of ways to split cells in the Google Sheet. Here is how you can do it:
1. Split Cells With Menu Option
2. Split Cells Using Formula
Split Cells With Menu Option
You can use the menu option to split cells in sheets if you do not want to deal with formulas or if your data will not change in the future.
Here is how you can do it:
Step 1: Click on the cell you want to split, click on the Data menu, and choose the split text to columns option.
Step 2: Your data will automatically split into different columns
Note: Google Sheets will look at the data and determine what character to use as a separator to split the text. If you want it to use a different character, simply click on the dropdown menu that appears and choose the correct option for your data.
Split Cells Using Formula
Suppose your data is likely to change later. In that case, you can save yourself the hassle of having to edit the formulas whenever there is a chance.
In this case, rather than using a formula for each column, it might be easier if you go ahead and key in all of the values into one area. Every time you want to change a certain value, you can easily do it as there is a formula that helps change every other adjacent value across the row.
Here is how you can do it:
Step 1: Select the column where you want the output to appear, type (=), and you can start typing the formula.
Step 2: Enter SPLIT () formula.
When you want to split a cell, the first thing you need to know is the cell reference that you want to split. In order to do this, you must highlight the cell or cells it will be cut into and insert a comma in front of your separator or delimiter, which needs to be something other than spaces or commas.
If we want to separate cell A1 using a comma as a delimiter, the formula is “=SPLIT (A1,”,”)”.
Note: This formula is a way to display data in multiple cells. The result spans to the number of cells depending upon the required data split. Specifically, how many of the separator characters are used in the formula.
In some cases, the delimiter which you use may not be a comma but rather a mix of say comma along with spaces. In such an instance, it is possible to establish Split Text to Columns more than once, first splitting the cell based on said comma and then using either spaces or tabs for the second step.
Now that we have covered how to split cells in Google Sheets, you will see that the formula method of splitting cells is more dynamic than the menu option method. You will need to remember not to have a blank space after your comma in the formula. Also, make sure whoever inputs the data does not add commas into the cell description. Split cells how you would like, depending on your preferences and methods.