Learn several techniques for deleting blank rows in your data. Depending on your situation, find the one that works best for your spreadsheet.
This post is meant to accompany the instructions 🎥this video.
In your Google Sheets workflow, it may sometimes be necessary to extract numbers from cells that contain a combination of both text and numbers. Perhaps you want to get the numbers to do further analysis, such as creating Pivot Tables. We can use various formulas for various unique situations. Let’s look at these formulas and a workaround that can save us a lot of time and effort.
The standard syntax for the SPLIT function is
=SPLIT(text, delimiter). The delimiter tells the formula for how to separate the string. For instance, if we had a list of first names and second names separated by a comma, we can use the following formula to separate the names into separate cells:
=SPLIT(text, ","). The comma is the delimiter in this case.
We can apply a similar concept to separate numbers that appear together with text in the same cell. How? By setting the delimiter to all characters that are not numbers. If we swap out the comma used in the previous example with the string, “qwertyuiopasdfghjklzxcvbnm, we are telling the formula to separate anything that isn’t in the domain of the mentioned string. Let’s see this in practice.
This only excluded letters of the alphabet from the output, and this is because the delimiter we specified only covers that scope. If we wanted brackets and colons excluded as well, we could add them to the delimiter. This separates the digits appearing before and after the character in question into different cells.
Another way we can extract numbers from a cell containing an assortment of numbers and text is using the REGEXREPLACE function. This function extracts all digits from a string and places them in one cell. The exact syntax used is
One thing to note here is that REGEXREPLACE ignores any non-digit characters that appear between the numbers and merges the numbers in one cell. This returns the value of “0” if the text does not contain any numbers. If the string contains a pure number, the formula will return a VALUE error because it hasn’t found any non-digits to replace.
Instead of trying to extract all the numbers that occur within a string, we may be interested in just the first instance of digits that appear next to each other. For example, we could have a list of international telephone numbers that generally appear in the following format (+country dialing code)-(rest of the number. To extract the country prefix, we could use REGEXEXTRACT. The syntax would be
These three ways to extract numbers from text are helpful in some ways, but they more or less are lacking in some capacity. In addition to that, if you’re not an avid spreadsheet user, things can get confusing and complicated for you at times. We can make things easier by using a Google Sheets add-on known as Power Tools.
Make sure to install Power tools before getting started. After installation, launch it via Add-ons > Power Tools.
On the sidebar that appears on the right, click on “Text” and then “Extract” and “Extract numbers”. Clicking on these options opens up an array of options for extracting numbers from our text.
What do the various options offer when extracting the numbers? Let’s explore them:
While you may spend a lot of time combining data in Google Sheets, you may also need to split the data into different sheets. In Google Sheets, it’s possible to split rows of data into various sheets based on specified criteria. For instance, we could separate the following list of companies based on their headquarters. As seen in 📺this video, there is more than one way to achieve our intended end result.
Let’s start by creating a list of companies headquartered in Australia.
We successfully split rows of data in Google Sheets! The cells containing the word Australia are now in a separate sheet, but what if we were listing all companies worldwide? Would we have to manually create over 200 sheets and tweak the FILTER function accordingly? No we wouldn’t because there’s a powerful tool (excuse the pun 🙂 ) known as Power Tools that can do the heavy-lifting for us.
Once Power Tools is installed for the first time, a sidebar should appear on the right side of your sheet. If the sidebar doesn’t appear automatically, you can launch it via Add-ons > Power Tools > Start. After that, make sure you’re on Sheet1 and click on SPLIT in the Power Tools sidebar.
Upon clicking, a set of options should appear, giving us various choices of how we want to split the data. In our case, we want to split an entire sheet so that’s what we’re going to select.
Now the only thing that’s left is to specify the criteria by which we wish to split our data and the destination of the split sheets.
We get this as the output once we click on “Split”:
The entries have been separated into 3 tabs automatically. If we had 20 or 50 tabs, that’s the number of tabs that would appear.
There are no built-in options in Google Sheets that function on cells based on their formatting. There are a few crude methods to use functions by color, but these are not super-intuitive. Enter Power Tools.
Power Tools is an add-on that gives our Google Sheets extra abilities, one of them being the ability to sum, average, et cetera, based on color, as explained in 📺this video.
All units below 50 have a green font in the following dataset, while those above 50 have a red font (column D). How would we sum all the green values?
As mentioned, we need to use Power Tools.
After installing Power Tools, you can launch it via Add-ons > Power Tools > Start
After starting Power Tools, let’s find the total of all units that have a green font. First, we should highlight the range to which we want to apply a function. In our case, that’s D2:D10. Next, head over to the Power Tools sidebar, click on the red underlined summation icon, and select “Function by color”.
After clicking “Function by color”, we need to adjust the settings to match the formatting of the cells we want to work with. After changing the settings, we should set the background color to white and the font color to green (The actual name for the color is lime but, the most important thing is to make sure they are a visual match). There are quite a few functions we can apply to the selected range, but in our case, we’re interested in the SUM function.
Lastly, we click on “Insert function”. As a result of clicking on “Insert function”, a custom formula will appear on cell D11, and it computes the total of all values that have a lime font.
Things to note:
Let’s say you had several spreadsheets saved in Google Drive, and we wanted to combine them into one spreadsheet; how would we do this?
The first thing to note is that the data must have matching headers. They don’t have to be in the same order across all spreadsheets, but they need to match. We can combine the data in a static way or make sure that it’s dynamic by auto-inserting the formulas.
In our case, we are going to combine three spreadsheets that contain lists of companies. Here’s a look at one of the sheets to better understand what we’re working with.
There are two other similar sheets – Australian companies & USA companies. Now let’s combine them.
These are the steps to follow when combining the data is a one-time operation that doesn’t need to update when any of the sheets changes:
After a few seconds, a new sheet within the spreadsheet will appear with the combined data.
If we need the combined data to be dynamic, Power Tools can automatically insert the formulas we need. To do this, follow the steps we used in the previous example but, this time, make sure that the box labeled “Use a formula to combine sheets” is checked.
Upon combining, the sheet containing combined data will appear with errors since no user has granted permissions yet. (Remember we need to give access when using IMPORTRANGE). An extra sheet will appear, and we can then grant permissions there. That should correct the errors.
Since we’re using formulas, any changes to the source files will reflect on the combined sheet.