Extract Numbers from Strings in Google Sheets [REGEX Functions]

Hi. In this article, we will learn how to extract numbers from strings in google sheets. We often have some kind of strings in which we have some numbers, and some alphabets, we called this combination a String. But, for many reasons, we need to extract numbers out of a string and get them in a separate column. We can obviously do this manually by writing down the number from a long String value. But, is it convenient? Is it not calling errors? Of course, it is, doing this manually will invite so many errors and another thing is it is very tedious work to do for smart people like you.

So why shouldn’t we learn something that can do these kinds of tasks for us in seconds? And what about if we can achieve 100% accuracy as well? Well, it’s too good to hear. So, in this tutorial, we are going to learn everything about it. We will extract numbers from strings automatically using functions, and we only have to write the function once, and it will work for the entire column even if there are millions of rows.

Importance of Numbers Extraction from Strings in Google Sheets

As we have discussed above what is the purpose of extracting numbers from strings, we know that many of the data types are the combination of numbers and strings such as addresses, emails, contacts, special codes, account numbers, and so on. We many times need to extract the numbers from the overall string to save them and treat them separately. Doing this is very simple we can copy the entire String and can remove everything other than numbers. But, doing this can be a nightmare if you have thousands of rows.

So, today I have two extremely useful functions in google sheets, that help to extract numbers from strings automatically just by providing a simple regular expression, position, and the text to extract. This is very easy. Many people get confused and even afraid of these functions. But there is nothing difficult to learn, and use these functions for your ease. Let’s check them out.

How to Extract Numbers from Strings in Google Sheets

So, we basically have two similar functions today, both are from the REGEX family, the first function we are going to use is REGEXEXTRACT, which is regular expression extraction, and the other is REGEXREPLACE, which is regular expression replacement. The names are self-explanatory, but still, we are going to understand their names and their functionalities using some simple examples with a simple dataset.

Extract Numbers from Strings in Google Sheets – Using REGEXEXTRACT Function

In this section, we will learn how to extract numbers from strings in google sheets using the REGEXEXTRACT function. REGEXEXTRACT function extracts the first matching substrings according to a regular expression. It returns the part of the string that matches the pattern in the regular expression.

Syntax

REGEXEXTRACT(text, regular_expression)

Here,

1. text: The input text from which you want to extract a number or substring
2. regular_expression: The first part of the text that matches this expression will be returned by the function

Note: The function will return only the first match even if there are multiple matches

We also need to understand regular expressions to use these functions properly and accurately.

A regular expression is a pattern of characters that includes some symbols, letters, and numbers. They are also known as meta-characters.

So, for extracting numbers from a string, we need to know some special metacharacters that are used to tell the function what we want to do. We can extract from the beginning, from, end, or from the overall String.

There is a complete reference on how to use RE2 expressions here. You can check out and learn more about it.

We use one of the above characters to tell function about what we want, so let’s now see a practical example of it and break the suspense.

I have an example dataset in which I have some String values, I will extract the numbers from the strings using the REGEXTRACT Function.

Step 1

Sample dataset

Step 2

Here I have some numbers inside the Strings

Step 3

Now I will use the REGEXEXTRACT function to extract the numbers only

Note: For extracting a number (digit), we need to use the metacharacter “d” in double quotes to tell the formula that we want to extract a numeric value.

Step 4

This function will return the first occurrence of any number in the given String

Step 5

The result

But, what if want to extract all the numbers, back to the above meta characters reference, and see what is the functionality of a + sign? Yes, right. We can use a “+” sign with the expression to extract all occurrences of the numeric values from the String.

Step 6

Adding “+” for returning all occurrences

Step 7

Press Enter key, and now the function returns all the occurrences of the numeric values from a string

This is how we can easily return one, or all occurrences of numbers in a separate cell.

Extract Numbers from Strings in Google Sheets – From the Beginning of a String

There are cases when you only need to have a segment of numbers from the beginning of a String, which means you don’t want all the numbers to occur in a string, but you also don’t want to have the first one. Instead, you want to return only the numbers which are grouped at the start of a String.

Let’s understand with the below example

Step 1

Same data set

Step 2

Write the REGEXEXTRACT function

Step 3

Pass the “^” meta-character at the beginning of your regular expression which is used in the previous example.

Note: The “^” meta-character represents the start of a String, so we can use this with the same regular expression to tell the function that we want to extract the numbers only from the beginning of the string.

Step 4

Press Enter key and you will get the starting numbers from a String

Step 5

You will get an error where the numbers are not present in the beginning

Now you can see here that the Strings that don’t have numbers, in the beginning, return an #N/A error, which means the function could not find the numbers in the starting because they dont exist there. This is valid, and it can’t be said as an error. So, to control it we have multiple solutions, we can use the IRERROR function, or IFNA function to return meaningful texts instead of this dirty error.

Using IFERROR Function for Error Handling with REGEXEXTRACT function

To handle errors using the IFERROR function, simply enclose the formula within the IFERROR function. The overall formula will look like below

=IFERROR(REGEXEXTRACT(A2,”^\d+”),”Number not found in the beginning”)

Using IFNA Function for Error Handling with REGEXEXTRACT function

To handle errors using the IFNA function we can similarly enclose the formula within the IFNA function. The overall formula will be like below

=IFNA(REGEXEXTRACT(A2,”^\d+”),”Number not found in the beginning”)

Extract Numbers from Strings in Google Sheets – From the End of a String

As we have done it from the beginning, I am sure you have got the idea for the end as well. In this section, we will learn how to extract numbers from strings in google sheets from the end. This is very similar to what we did in the last section.

Step 1

I have the same dataset

Step 2

Write down the function

Step 3

Pass the other meta-character “$” for the end of a string

Note: When we used “^” for the beginning, so we write this meta character at the beginning of the regular expression, now we are using “$” for the end, so we will write this character at the end of the regular expression. I hope this is clear to everyone.

Step 4

Press the enter key, and you have got the numbers at the end only

Again, if there is no number at the end of a string, you will see an #N/A error, you can handle this error using IRERROR or IFNA as I have described above.

This is how to extract numbers from strings in google sheets from the beginning and the end.

We have another function that does not extract but replace. Let’s see how we can use that function to perform similar tasks.

Extract Numbers from Strings in Google Sheets – Using REGEXREPLACE Function

In this section, we will learn how to extract numbers from strings in google sheets using the REGEXREPLACE function. The function simply replaces the defined characters from new given characters in a given string and returns a new strong. Now the replacement from the removed characters is mostly whitespace. This means we don’t replace a substring what we do is simply remove it. But you can specify new characters for the replacement.

For this example, I have the same data set, in which we are going to apply the REGEXREPLACE function.

Step 1

Sample data

Step 2

Write down the function

Step 3

The first argument will be a string cell reference

Step 4

Firstly, we want to replace all the” [a-zA-Z]”, and replace with an empty string

Step 5

Press Enter key, and you’re done

Note: The function will return everything other than small and capital alphabets, which means any characters like hyphens, dashes, underscores, etc will also be returned.

Download/Copy Google Sheets Workbook

Important Notes

Frequently Asked Questions

Can I Use the Text to Number Conversion Method to Extract Numbers from Strings in Google Sheets?

Conclusion

Wrapping up how to extract numbers from strings in google sheets. We have learned how to use regular expressions for performing simple and easy functions. We saw several examples of REGEXEXTRACT, and REGEXREPLACE functions to extract numbers from the beginning, from the end, and from anywhere of a String. That’s all from how to extract numbers from strings in google sheets. I hope you learned and find this tutorial and examples helpful. I will see you soon with another helpful tutorial. Take care. Keep learning with Office Demy.