Are you looking for a variety of ways to extract numbers from a string in Google Sheets? Well, today is your lucky day, as this tutorial has everything you’re looking for and more.
But before we get into the nitty gritty of this tutorial, keep in mind that Google Sheets doesn’t provide a specific function for extracting numbers from a string. To this end, we will explore different functions we can use to extract numbers from a string in Google Sheets.
In today’s guide, we’ll look at a few ways to use functions in Google Sheets to pull numerical substrings out of a string. Not just that, we will also look at the different formulas we can use to extract numbers from the strings.
REGEX Functions: Using these functions to extract a number from a string in Google Sheets
If there is one thing we love about Google Sheets, it is the fact that it provides users with a plethora of useful REGEX functions, including REGEXEXTRACT and REGEXREPLACE, that can be used to extract numbers from a string.
What is exciting about these functions is that they allow users to quickly use a search string featuring regular expressions to find numbers, symbols, patterns, and even letters from strings.
Before we consider a few individual cases and examples, it’s important we break down the syntax of these functions along with how they work.
The anatomy of the REGEXEXTRACT function
This function is typically deployed to extract matching substrings from a string. What this function does is take a string from a regular expression and return the part of the string that conforms or matches the pattern in the regular expression.
Here is what the syntax for the REGEXEXTRACT function looks like:
Here is an explanation that gives a better picture of what this function means:
- Text in the syntax above typically represents text or string from which you’d like to extract a substring
- Reg_exp is just a regular expression. This search string is expected to match the part of the text you intend to extract.
When dealing with a text string with more than one substring aligning with the regular expression, the REGEXEXTRACT function returns the first matching substring.
The anatomy of REGEXREPLACE function
The REGEXREPLACE function in Google Sheets is typically used to replace matching substrings in a string. Unlike the REGEXEXTRACT function, this function takes a string, a regular expression, as well as a replacement string and returns the string with the matching substring replaced.
Here is what the syntax for REGEXREPLACE function looks like:
REGEXREPLACE(text, reg_exp, replacement)
Here is a brief overview explaining this syntax:
- text in the formula above refers to the text or string from which you intend to find the matching number.
- reg_exp is a regular expression. It typically means the search string should match the part of the text you intend to replace.
- replacement is the text string you want to replace all matching instances of the reg_exp in text.
We know that the explanations above may sound a bit confusing, but not to worry, you’ll have a better understanding of what we mean when we delve into the practical aspect of this guide. So stay put.
Understanding a regular expression
A regular expression typically refers to a pattern of characters. It can include things like letters, special symbols, and numbers. And yes, it is also known as meta-characters. Typically, this meta-character pattern is fused together to form a search string.
Here is a table highlighting commonly used meta-characters you can use to extract numbers from a string.
|Meta-characters||What they represent|
|^||This meta-character represents the start of the string|
|$||Represents the end of the string|
|.||Represents a single character|
|d||This meta-character represents a numeric digit|
|+||It represents one, or multiple occurrences of a character or string|
|w||This meta-character represents a letter|
|It meant to escape a special character|
|()||A sequence of characters enclosed in it|
|||Any single one of the characters enclosed within it|
As we go deeper into this guide, you’ll soon see how you can use these meta-characters together to make powerful regular expressions.
How to extract numbers from anywhere in a string in Google Sheets
Now that we’ve talked in depth about how to get numbers out of a string in Google Sheets, it’s time to talk about how to do it. For this tutorial, we will consider the following list of strings.
Our goal with this guide is to show you how to extract numbers from each of these strings.
From the meta-characters we highlighted earlier, we mentioned that the “d” expression represents a numeric digit. So since we intend to extract the numbers from our sample strings, we will use the ‘’ symbol along with d, so Google Sheets understands that what we mean is the meta character ‘d’ and not the letter ‘d.’
So, assuming we want to extract a single numeric digit from a string, we can use the expression ‘d.’ On the flip side, if we want to extract more than one numeric digit, we would have to use the expression ‘d+’.
To put things in perspective, let’s say you want to extract the first occurrence from a series of numeric digits from a string; here is the formula you need to use (assuming the string you’d like to extract is in cell A2):
Note: Keep in mind that in a situation where there are multiple occurrences of a number in the string, the above formula only considers the first.
Here is how to extract numbers from a string in Google Sheets:
- Select the cell where you’d like to display the extracted numbers. For this guide, we will use cell B2.
- After selecting the cell, type in the following formula in the formula bar:
- After entering the formula, press the Enter key on your keyboard. You should notice the extracted number appears in your chosen cell.
Here is what it should look like:
Having extracted the number for cell A2, you can do the same for other cells using the autofill option. This would save you the hassles of repeating the same process for each cell.
Here is a short video illustration showing you how it’s done.
How to extract numbers from the beginning of a string in Google Sheets
There are certain instances when we only want to extract numbers at the beginning of a string. As we did in the above example, we will use the same sample string.
Before we show you how to extract only numbers at the beginning of each of these strings, here is a brief explanation you need to have at the back of your mind.
The meta character “^” is used to specify that we are only interested in extracting characters starting with the following expression. In essence, the expression “^d” means we only want to extract the digit at the start of a string.
If you intend to extract more than one numeric digit, you’ll need to use the expression “^d+”
In a situation where your string doesn’t start with a number, applying the REGEXEXCTRACT function will typically return an error.
Here is how to extract numbers from the beginning of a string in Google Sheets.
- As we did in our earlier example, choose the cell where you’d like the extracted number to show. For this tutorial, we will be using cell B2.
- After choosing the cell where we would like to display the extracted numbers, the next thing you need to do is type the following formula in the formula bar:
- Finally, hit the Enter key to generate the extracted number in your chosen cells.
If you followed the steps exactly as we outlined above, your spreadsheet should look like this:
From the screenshot above, you’ll notice that applying the =REGEXEXTRACT(A2,”^\d+”) has returned an error for some cells. We can prevent this by including an IFERROR function in the REGEXEXTRACT function. By including an IFERROR function instead of the REGEXEXTRACT to return an error, it will leave the cell blank.
In simple terms, if our string doesn’t start with a number, the result will be a blank cell.
To extract the numbers from a string without the error popping up in specific cells, you can use the following formula:
Going back to our sample string, here is how to go about it
- Select the cell you want the extracted numbers to be displayed.
- Head to the formula bar and type the following formula:
- Hit the Enter key on your keyboard. You should see the result generated in your chosen cell.
Here is what your spreadsheet should look like if you did everything correctly.
How to extract numbers from the end of a string in Google Sheets
Sometimes, you might only be interested in extracting numbers from the end of a string in Google Sheets. And should that be the case, you only need to tweak the REGEXEXTRACT formula to get the job done.
For this example, we will use the same sample string we used for other examples.
To extract only numbers from the end of our sample strings, we need to understand the basics.
The meta-character “$” is specifically used when we want to extract only characters ending with the preceding expression. So the expression “d$” typically represents the numbers at the end of a string.
If you intend to extract multiple numeric digits from a given string, you can use the expression ‘d+$.’
And just like our earlier example, if your string doesn’t end with a number, you’d have to include the IFERROR function to resolve any error that will pop up.
To extract only numbers from the end of a string, you can use the following formula:
Here is how to apply this formula to our sample string:
- Select the cell where you’d like the extracted number to be displayed. For this tutorial, we will use cell B2.
- Now, type in the following formula in the formula bar:
- Finally, hit the Enter button on your keyboard, and Google will automatically generate the result in your chosen cell.
If you followed the steps highlighted above, your spreadsheet should look like this:
How to extract all numbers in a string by removing the text part
Do you know it’s possible to extract all numbers in a string by removing the text part? Well, let’s look at some special cases. Let’s say you have numbers in different parts of your text, and you only want to extract and combine all the numbers into a single number. Here is how to go about it.
For this tutorial, we will use the following sample strings:
If you look at each string in our spreadsheet, you’ll notice that the numbers are spread out along with text characters in between. Our objective with this tutorial is to extract the numbers in the order they appear and combine them into a large number.
To achieve this, all we need to do is remove all the text characters in the string using the REGEXREPLACE function as follows:
When applied, the above formula simply removes all alphabets in the string, leaving only numbers behind.
Here is a step-by-step guide showing you how to extract all the numbers in a string:
- Select the cell where you want to show the extracted numbers.
- Type the following formula in the formula tab:
- Hit the Enter button on your keyboard. You should see the extracted number in the chosen cell.
Here is a screenshot of what your spreadsheet should look like:
In today’s guide, we explored how you can extract numbers from a string in Google Sheets. We focused on different types of situations and presented several use cases. Our objective was to present you with different ways to approach it, and we are excited we covered that extensively.
We also explained each formula with a practical guide. This way, you know exactly how to execute such a task.
We hope our examples have been practical and detailed enough to teach you how to extract numbers from a string in Google Sheets.
Disclosure: This page may contain a few affiliate links, which means if you buy something through them, we may get a commission (without any extra cost to you).