How to Extract Numbers from a String in Google Sheets

Google Sheets does not really provide a function specifically to extract numbers from a string. There are, however, a multitude of different functions provided by Google Sheets that we can customize to get the work done.

In this tutorial, we will take a look at a few ways in which we can make use of functions in Google Sheets to extract substrings that are numerical from a string.

As we move along the tutorial, we will look at the different types of formulas that can be used in Google Sheets to extract numbers from the strings in each case.

 

 

Using REGEX Functions in Google Sheets to Extract a Number from a String

Google Sheets provides a suite of handy REGEX functions that include functions like REGEXEXRACT and REGEXREPLACE.

These functions allow the use of a search string consisting of a regular expression to quickly find numbers, letters, symbols, as well as patterns of strings in a given text.

So before we look at individual cases and examples, let us understand the syntax of these functions and how they work.

The REGEXEXTRACT Function

The REGEXEXTRACT function is used to extract matching substrings from a string. It takes a string and a regular expression, and returns the part of the string that matches the pattern in the regular expression.

The syntax for the REGEXEXTRACT function is as follows:

REGEXEXTRACT(text, reg_exp)

Here,

• text is the text or string from which you want to extract a substring
• reg_exp is a regular expression. This is the search string that should match the part of the text that you want to extract

When a text string contains more than one substring that matches the regular expression, then REGEXEXTRACT returns the first matching substring.

Click here for more information on REGEXEXTRACT.

 

The REGEXREPLACE Function

The REGEXREPLACE function is used to replace matching substrings in a string. It takes a string, a regular expression, and replacement string and it returns the string with the matching substrings replaced.

The syntax for the REGEXEXTRACT function is as follows:

REGEXEXTRACT(text, reg_exp, replacement)

Here,

• text is the text or string where you want to find the matching substring(s)
• reg_exp is a regular expression. This is the search string that should match the part of the text that you want to replace
• replacement is the text string that will replace all matching instances of the reg_exp in text

Click here for more information on REGEXREPLACE.

What is a Regular Expression?

A regular expression is nothing but a pattern of characters, which include some special symbols, letters and/ or numbers, also known as meta-characters. This pattern of meta-characters come together to form a search string.

To extract numbers from a string, here are some commonly used meta-characters:

Meta-character	What it Represents
^	The start of the string
$	The end of the string
.	A single character
+	One or more occurrences of a character or string
d	A numeric digit
w	A letter
	Escapes a special character
()	A sequence of characters enclosed inside it
[]	Any one of the characters enclosed inside it

As you keep reading this article, it will become clearer how these meta-characters can be combined to perform powerful regular expressions.

 

Extracting Numbers From Anywhere in a String in Google Sheets

Consider the following list of strings:

 

Let us try to extract the numbers from each of these strings.

The “d” expression represents a numeric digit. We use the ‘’ symbol so that Google Sheets understands that we mean the meta-character ‘d’ and not the letter ‘d’.

So if you want to extract a single numeric digit from a string, you use the expression ‘d’. But if you want to extract more than one digits, you can use the expression ‘d+’.

In other words, if you want to extract the first occurrence of a series of numeric digits from a string, you can use the following formula (considering the string to be extracted is in cell A2):

=REGEXEXTRACT(A2,”d+”)

Note: If there are more than one occurrences of a number in the string, the above formula will only return the first number, as you can see in row 4 of the screenshot below:

Extracting Numbers From the Beginning of a String in Google Sheets

There may be cases where you only want to extract the numbers at the beginning of a string. For example, consider the following list of strings:

 

Let us try to extract only the numbers at the beginning of each of these strings.

The “^” symbol is used to specify that we want only characters starting with the following expression. So the expression “^d” represents only the digit at the start of the given string.

If you want to extract more than one numeric digit from the start of the string, you can use the expression ‘^d+’.

Now what happens if the string does not start with a number at all?

In that case, the REGEXEXTRACT function returns an error.

To handle this error, you can wrap an IFERROR function around the REGEXEXTRACT function. This function can then ensure that if the REGEXEXTRACT function returns an error, a blank cell is displayed.

In other words, if the string does not start with a number, then the result will be a blank cell.

So if you want to extract the numbers from the beginning of a string, you can use the following formula (considering the string to be extracted is in cell A2):

=IFERROR(REGEXEXTRACT(A2,”^d+”),””)

 

Extracting Numbers From the End of a String in Google Sheets

Similarly, if you only want to extract the numbers at the end of a string, you just need to make a small tweak to the same formula.

Consider the following list of strings:

 

Let us try to extract only the numbers at the end of each of these strings.

The “$” symbol is used to specify that we want only characters ending with the preceding expression. So the expression “d$” represents only the digit at the end of the given string.

If you want to extract more than one numeric digit from the end of the string, you can use the expression ‘d+$’.

As before, if your string does not end with a number at all, you might want to handle it with an IFERROR function.

So if you want to extract the numbers from the end of a string, you can use the following formula (considering the string to be extracted is in cell A2):

=IFERROR(REGEXEXTRACT(A2,”d+$”),””)

 

Extracting All Numbers in a String by Removing the Text Part

Now let us take a look at a few special cases. What if you have numbers in different parts of the text and you want to extract and combine all of them into a single number?

For example, consider the following list of strings:

 

In each string, the numbers are spread out with text characters in between them. We want to extract the numbers in the same order and combine them into a single large number.

In such cases, the best move would be to simply remove all the text characters in the string! For this we can easily apply the REGEXREPLACE function as follows:

=REGEXREPLACE(A2,”[a-zA-Z]”,””)

The above formula will remove all alphabets from the string in cell A2, leaving behind only the numbers.

 

Extracting Multiple Numbers From Different Parts of a String into Separate Columns

Alternatively, instead of combining the different occurrences of numbers into one column, you might want to split the number occurring in different parts of the text into separate columns. The SPLIT function would work great for you in such cases.

The SPLIT function is used to divide a text string (or value) around a given delimiter. The output is displayed in separate pieces into their own consecutive cells in a row.

Let us consider the same list of strings:

 

To split the numbers occurring in different parts of the text into separate columns, we can use the SPLIT function as follows (considering the string to be extracted is in cell A2):

=SPLIT(LOWER(A2);”abcdefghijklmnopqsrtuvwxyz”)

This formula uses any letter of the alphabet as a delimiter, thereby splitting any numbers around a letter or set of letters into separate cells (or columns).

Here’s what happens when you apply the formula to our given list of strings:

 

 

Splitting Parts of a Number into Separate Columns Based on a Pattern

The great thing about REGEX functions is that they can detect patterns of numbers in a string, so we can easily take advantage of the patterns to separate the numbers into constituent parts and display each part in separate columns.

For example, let us say you have a list of strings containing telephone numbers and you want to separate these numbers into separate columns for area code, exchange code, and subscriber number:

 

Now notice the pattern in the telephone numbers.

• The first three digits (of the area code) are enclosed in parentheses.
• The next three digits (of the exchange code) come after the closing parenthesis and before the hyphen ‘-‘.
• The last 4 digits (of the subscriber number) come right after the hyphen.

We can use this pattern to specify the regular expression, where each sequence of numbers (that we want in one column) can be represented in rounded brackets.

To specify how many characters should be there in a sequence, we can use curly brackets. This means if we want to specify a sequence of 3 numbers, we can use the expression “(.{3})” or “(…)”.

Let us put everything together. To extract the phone number from the string in cell A2 and separate it into three columns, we can use the formula:

=REGEXEXTRACT(A2,"((.{3}))(.{3})-(.{4})")

Here’s what happens when you apply the formula to our given list of strings:

 

Conclusion

In this tutorial, we showed you how to extract numbers from a string in Google Sheets, using different types of situations.

We tried to present you with different use-cases so that you can decide which formula works best for you.

We also explained each of the formulas so that you can customize them as needed and apply them to your own data.

We hope our examples have been helpful.