Extract Numbers from Strings in Google Sheets [REGEX Functions]

how to Extract Numbers from Strings in Google Sheets 3

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.

See also  What does IDLE Mean on Google Sheets?

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.

 

Meta Character Functionality
^ Starting of the String
& Ending of the String
. Any single character
+ One or more occurrences of a single character or a string
d A numeric value or digit
w A single letter
() The content enclosed in parentheses ()
[] The content enclosed in square brackets

 

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

how to Extract Numbers from Strings in Google Sheets 1

Step 2

Here I have some numbers inside the Strings

how to Extract Numbers from Strings in Google Sheets 2

Step 3

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

how to Extract Numbers from Strings in Google Sheets 3

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

how to Extract Numbers from Strings in Google Sheets 4

Step 5

The result

how to Extract Numbers from Strings in Google Sheets 5

how to Extract Numbers from Strings in Google Sheets 6

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

how to Extract Numbers from Strings in Google Sheets 7

Step 7

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

how to Extract Numbers from Strings in Google Sheets 8

how to Extract Numbers from Strings in Google Sheets 9

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

See also  How to Highlight Row and Column in Google Sheets

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

how to Extract Numbers from Strings in Google Sheets 10

Step 2

Write the REGEXEXTRACT function

how to Extract Numbers from Strings in Google Sheets 11

Step 3

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

how to Extract Numbers from Strings in Google Sheets 12

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

how to Extract Numbers from Strings in Google Sheets 13

Step 5

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

how to Extract Numbers from Strings in Google Sheets 14

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”)

how to Extract Numbers from Strings in Google Sheets 15

how to Extract Numbers from Strings in Google Sheets 16

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”)

how to Extract Numbers from Strings in Google Sheets 17

how to Extract Numbers from Strings in Google Sheets 18

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

how to Extract Numbers from Strings in Google Sheets 19

Step 2

Write down the function

how to Extract Numbers from Strings in Google Sheets 20

Step 3

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

how to Extract Numbers from Strings in Google Sheets 21

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

how to Extract Numbers from Strings in Google Sheets 22

how to Extract Numbers from Strings in Google Sheets 23

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.

See also  How To Switch Axis in Google Sheets [Guide 2023]

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

how to Extract Numbers from Strings in Google Sheets 24

Step 2

Write down the function

how to Extract Numbers from Strings in Google Sheets 25

Step 3

The first argument will be a string cell reference

how to Extract Numbers from Strings in Google Sheets 26

Step 4

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

how to Extract Numbers from Strings in Google Sheets 27

Step 5

Press Enter key, and you’re done

how to Extract Numbers from Strings in Google Sheets 28

how to Extract Numbers from Strings in Google Sheets 29

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

  • The REGEXREPLACE function returns everything other than small and capital alphabets if we pass a set of A-Z and a-z, for a replacement argument. It means any characters like hyphens, dashes, underscores, full stops, slashes, etc will also be returned.
  • The REGEXEXTRACT function only returns the first occurrence as default.
  • We can use any of the above-referenced meta characters to perform several functions using regular expressions
  • The IFNA and IFERROR functions are used to print custom messages when encountering any #N/A error, or general errors.

See also  How to use PERCENTILE Function in Google Sheets [Beginner's Guide]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general