How to Extract Numbers from a String in Google Sheets

We can use a combination of Google Sheets functions to extract numbers from a string in Google Sheets.

The function REGEXREPLACE can be used to remove non-digit characters from a string, leaving behind a number. We can also use the SPLIT function to get numbers separated by non-digit characters.

Let’s take a look at a quick example where we can use these functions to extract numbers from a string.

We’re given a dataset of published works. We want to identify the volume number of each of these articles, but our data is not that straightforward. The Volume column has values such as “Volume 3”, “Volume: 1”, and “Vol. 2”. Is it possible to return just the number found in the Volume string?

The REGEXREPLACE function makes it easy for you to remove any non-digit characters from your string.

We can also use the SPLIT function if we need to extract multiple numerical values from the same string.

How is this different? For example, with the string “11abcd22”, our REGEXREPLACE function will return a value of 1122. Meanwhile, the SPLIT function will return an array containing both 11 and 22.

These are just one of many scenarios where we need to extract numbers from a string in Google Sheets. We can use these functions, for example, to clean up user-inputted mobile numbers or get numbers from an address line.

Now that we know when we can use the REGEXREPLACE and SPLIT functions, let’s explore how we can use it.

 

 

A Real Example of Extracting Numbers from a String in Google Sheets

Let’s look at a real example of Google Sheets functions being used to retrieve numbers from a string.

In the example below, we have 8 different strings, which all reference a specific article number. We could ignore all non-digit text and return the numbers shown in Column B. 

extract a single number from google sheets

 

To get the values in Column B, we just need to use the following formula:

=VALUE(REGEXREPLACE(A2,"\D+", ""))

REGEXREPLACE works by providing three arguments: the target text, a regular expression, and the replacement text. In the formula above, we want to replace certain characters with a “” or empty string. This erases them from the final result. The “\D+” regular expression matches one or more characters that are not a digit.

We wrap the result in a VALUE function to convert the resulting string into a numerical value.

You may also write the formula in the following form:

=VALUE(REGEXREPLACE(A3,"[^[:digit:]]", ""))

In the formula above, the “[:digit:]” portion of the expression refers to any digit from 0 to 9.

Another example of a situation where we must extract numerical data can be seen below. The original text consists of a pair of numbers labeled x and y. With the SPLIT function, we can easily remove the non-digit characters and return each number present in the text.

extract multiple numbers from string google sheets

 

To get the numbers in columns E and F, we can use the following formula:

=SPLIT(lower(D3),"qwertyuiopasdfghjklzxcvbnm`-=[]\;' ,./!:@#$%^&*()")

The formula works by taking advantage of how the SPLIT function works. The delimiters chosen to split the string around will not appear in the result themselves. Not only do we remove non-digit characters, but we can also return multiple numbers as well.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to start extracting numbers from a string in Google Sheets, head over to the next section to learn how to do it yourself.

 

 

How to Extract Numbers from a String in Google Sheets

This section will guide you through every step we need to start extracting numbers from a string in Google Sheets. By the end of this guide, you’ll learn how we can use the REGEXREPLACE function to remove non-digit characters from text.

  1. In this example, we have two columns. Column A contains our original text, while column B contains the extracted number. Despite the differences in format, every entry has an associated numerical value.
    multiple text values with inconsistent formatting

  2. To extract the article number from the text, we can use the formula shown in the prior section.
    Using REGEXREPLACE and VALUE to extract numbers from string google sheets
  3. Hit the Enter key to return the final result of the formula. In this case, we extracted 15 from the original string.
    extracting 15 from the string "Article 15"

  4. We can drag down the formula to fill out the rest of the column.
    Fill entire column to get every number

  5. For cases where you will need to extract multiple numbers, we can use the formula with SPLIT. In the example below, we could ignore the non-digit characters from the original text and retrieve a pair of 3-digit numbers.
    Using SPLIT to extract numbers from string in google sheets

 

 

Our step-by-step guide shows how easy it is to use Google Sheets formulas to extract a number from a given text string.

Extracting numbers is just one example of what you can do with text values in a Google Sheet. With so many other Google Sheets functions out there, there’s bound to be a function out there that suits your use case. 

Are you interested in learning more about what Google Sheets can do? Stay notified of new Google Sheets guides like this by subscribing to our newsletter!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like