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
Also read: How to sort Google Sheet data by date?
Jump ahead to:
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.
Conclusion
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.
A brief Introduction About EasyInsights
As a marketer you may need to import data from various ad platforms to create a comprehensive marketing report. Investing countless hours each day to collect and maintain data in a Postgres database is a tedious and time-consuming task that results in unnoticeable errors. Logging in to multiple platforms and exporting, transforming, and uploading data to BI tools consumes more than 40% of marketers’ time.
This is where a tool like EasyInsights can help! EasyInsights’ allows you to fully automate and secure data transfers from any source without having to rewrite the code. It allows you to not only export and load data but also transform and enrich it so that it is ready for analysis. Although you can manually connect the tools, EasyInsights makes it easier to use any BI tool by allowing you to pull data from various data sources and integration tools.
You can connect all your data sources to EasyInsights with a few clicks, and access your transformed business ready data in any BI tool in a flash.
Benefits Of Using EasyInsights
- Completely automated: The EasyInsights platform is simple to set up and requires little maintenance.
- Real-time Data Transfer: EasyInsights offers real-time data migration so that you always have analysis-ready data.
- 100% Complete & Accurate Data Transfer: EasyInsight’s robust infrastructure ensures dependable data transfer with no data loss.
- Scalability: EasyInsights has built-in integrations from all the popular marketing data sources, allowing you to scale your data infrastructure as needed.
- Generate Insightful reports: EasyInsights helps in generating useful reports. Use reports on Google Sheets to make faster data decisions, or consume it from your inbox.
Book a Demo! Talk to our product expert to automate your daily reports and dashboards within a flash.
