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.
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.
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.
- 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.
- To extract the article number from the text, we can use the formula shown in the prior section.
- Hit the Enter key to return the final result of the formula. In this case, we extracted 15 from the original string.
- We can drag down the formula to fill out the rest of the column.
- 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.
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!
