Manipulating data is one of the key functions of spreadsheet use, and you might already be aware of the various options for separating data in Excel but the methods for doing this in Google Sheets are not quite the same.
Google Sheets is a popular alternative to Excel and is increasingly being used, particularly where there’s a need for a shared spreadsheet or database that team members can share and access remotely. However if, like me, you’re a seasoned pro at using Excel, finding your way away Google Sheets can have its challenges!
So let’s imagine you have a cell containing data that needs to be separated out into different columns. A classic example would be where you need to split a cell containing a full name into first name and last name. Or in a more complex example, you might have a CSV export from your ecommerce store or CRM system that has grouped add data into one column that you want to split out into separate columns.
There are a couple of ways of achieving this, using both functions and formulas in Google Sheets
(and if you are looking for a similar tutorial in Excel, view this post)
- The first step when splitting data in a spreadsheet is to make sure there is plenty of empty columns to the right of your source data to accommodate the newly split data.
- Next you need to ensure that your source data has some sort of separator between each piece of information (also know as a delimiter in the formula). This could be a comma, a space, a dash etc. Now you can got to “Data” and select “Split text into columns…”. Note that this will overwrite your source data.
- To fix how your columns have spread out after you’ve split your source data, click the menu next to “Separator” then click “Detect automatically”.
- This leaves you with your source data successfully split into separate columns that are correctly spread out.
- If you want to split the source data in your columns without losing your source information, you can use the split formula. This will retain your source information, however if you want to manipulate the data in the newly split columns you will need to convert the data to values only first, otherwise when you try to edit the data in the columns, it will result in an error.
- Start by entering the formula in the column next to the combined data. The formula should be “=split)”, which will bring up the formula helper.
- The first part of your formula is the cell number that the combined number is in. In this case it’s A3. The second part of the formula is the delimiter. In our example we’ve used a comma, but if in your data it’s a space or a dash you need to add this between the inverted commas instead. So the formula will read “=split(A3,”,”)”. Now press enter.
- Now your source data will have split into the columns to the right of the original combined data. If you look at the data in column B, you will notice that it will be a formula. Also if you delete column A it will delete the information used in the formulas. This means we have one more step to go.
- Highlight the cell that has the formula when you click on it, copy the information and then use “Paste Special” > “Paste Value Only”. This makes sure the formula is replaced with the values, and you can now delete column A if you need to.