Extract numbers from Text in Google Sheets

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.

Extracting numbers from text using the SPLIT function

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.

Extracting numbers from text into separate columns using the SPLIT function

 

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.

 

The results of the SPLIT function showing numbers being extracted from text

 

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.

 

Extracting numbers from text using the REGEXREPLACE function

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 =VALUE(REGEXREPLACE(text,"[^[:digit:]]",""))

 

Numbers extracted using the REGEXREPLACE formula

 

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.

Using the REGEXEXTRACT function

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 =VALUE(REGEXEXTRACT(text,"\d+")).

 

 

Numbers extracted using the REGEXEXTRACT function

 

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.

Extracting numbers from text using Power Tools add-on

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.
 

Menu of functions available from Power Tools

 
What do the various options offer when extracting the numbers? Let’s explore them:

  • If you specify that your numbers have decimal/thousand separators, the add-on will display all numbers, including those that appear after a comma and decimal point.
  • If you check “Extract all occurrences”, this extracts all numbers regardless of their positions in the string. For example, the formula extracts 79 and 90 if a string is “79ogfgfh90”.
  • There is an option to place occurrences in one cell or separate cells.
  • You can choose to display results in a new column. By default, this displays the results to the right of the selected cells. This overwrites any data that exists in that column. However, if we select “Insert new column with results to the right”, it inserts new columns/columns with the extracted data.
  • Finally, we can remove the extracted data from the source by selecting “Clear the extracted text from the source data”. This option could come in handy if we’re interested in the remaining text rather than the numbers extracted.

 

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

Splitting rows of data in Google Sheets

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.

Table of data that is not split

 

Splitting rows in Google Sheets with the FILTER function

Let’s start by creating a list of companies headquartered in Australia.

  1. Create a new sheet and name it “Australia”.
  2. Copy the header row from Sheet 1 and paste it to the new sheet.
  3. Next, we’re going to use the FILTER function to give us a list of only Australian-based companies. The syntax we should follow is =FILTER(range, criteria). For us, we want to return all the values in the range Sheet1!A2:C18, provided this criterion is met: column C is equal to Australia. Therefore our formula would be: =FILTER(Sheet1!A2:C18,Sheet1!C2:C18=“Australia”)
  4. If we plug this formula into cell A2 of the sheet, Australia, we get the following output:

Table of data after being split based on row value

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.

Splitting Rows using Power Tools by Ablebits

To install Power Tools, click on this link.

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.

Power Tools Split Function in the 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.

Power Tools Split Sheet option

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.

Settings for Power Tools Split Sheets

We get this as the output once we click on “Split”:

The resultant split rows

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.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

Combining Spreadsheets in Google Sheets

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?

As explained in 📺this video, we can use formulas, but this gets tedious when dealing with a large number of sheets. Instead, we can use a Google Sheet add-on known as Power Tools.

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.

On of the three spreadsheets that are will be combined

There are two other similar sheets – Australian companies & USA companies. Now let’s combine them.

Combining Static Spreadsheets

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:

  1. Launch Power Tools via Add-ons > Power Tools. (If you haven’t installed Power Tools yet, you can follow this link to install.
  2. In the sidebar on the right, select “Merge & Combine.”
  3. Select “Combine sheets” and click on “Add files from Drive” in the pop-up that appears.
  4. Add all the sheets from which you would like to fetch data.
  5. Make sure the boxes are next to the files and click “Next.”

Using a menu in Power Tools to select all of the spreadsheets to be combined

  1. In the following pop-up that appears next, make sure that “Use a formula to combine sheets” is not checked. (This is because we are working with static data). Now click combine.

Power Tools menu with the option to use a formula to combine the sheets unselected

After a few seconds, a new sheet within the spreadsheet will appear with the combined data.

Combined spreadsheet with a success message

Combine Dynamic Spreadsheets

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.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

AutoSum Function in Google Sheets

You may be looking for AutoSum in Google Sheets, but you won’t find it in the built-in menus.

AutoSum in Excel

Traditionally in Microsoft Excel, you would sum, multiply or divide values in a range by keying in the respective function and then specifying the range. You would add the total number of units In the following dataset by applying the formula, “=SUM(D2:D10)“.

column being SUMMED in Excel

However, as demonstrated in 📺this video, Excel provides a built-in intelligent function that automatically detects the range we wish to sum, known as AutoSum. If we place the cursor on cell D11 and click on AutoSum, Excel will figure out on its own that we intend to sum the range, D2:D10.

column of numbers being highlighted with autosum

 

AutoSum in Google Sheets

Could we do the same in Google Sheets? Well, it’s not as impressive as in Excel. Instead of auto-detecting the range, Google Sheets merely inserts the specified function without the range.

the SUM function appearing in Google Sheets

We could solve this problem using a third-party add-on known as Power Tools.

 

AutoSum in Google Sheets using Power Tools

To install Power Tools, click on this link.

After you install Power Tools, you can launch it via Add-ons > Power Tools > Start.

 

function menu in Power Tools

 

Now that we have the plugin installed, we can repeat the AutoSum operation we did in Excel. To achieve this, click on the cell that needs to add up the total. In our case, we want to get the Units total, so the cell is D11. Now that you have selected the units, head over to the sidebar and click on the AutoSum icon, Σ (not to be confused with the red-underlined Σ). Next, click on SUM in the drop-down that appears. After clicking, the total automatically appears in the cell we selected.

 

the result of autosum in Google Sheets using Power Tools

 

Things to note:

  • You can execute operations besides addition using the Power Tools add-on. The drop-down next to the icon provides a wide selection of functions to apply.
  • There’s an AutoSum by color function in  Power Tools, which sums values based on background color and the text color. Find more on that here.

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.