How to Use the REGEXEXTRACT Function in Google Sheets
=REGEXEXTRACT(text, regular_expression). For example, the pattern "\d+" returns the first run of digits.
What REGEXEXTRACT does
LEFT, MID and FIND work when the text has a fixed layout. When the layout varies, such as an order number somewhere inside a sentence, a regular expression describes what to look for instead of where it sits. REGEXEXTRACT returns what it finds.
REGEXEXTRACT syntax
=REGEXEXTRACT(text, regular_expression)
text: the input text.regular_expression: the pattern. The first part of the text that matches it is returned.
Per Google's help page, Sheets uses the RE2 regular expression syntax, except for Unicode character class matching. The function works only with text, not numbers, and it returns text.
A step-by-step example
Cell A2 holds a sentence with an order number inside it.
| A | |
|---|---|
| 1 | Message |
| 2 | Order 4821 shipped |
=REGEXEXTRACT(A2, "\d+")
This returns 4821. Here is why: \d matches one digit and the + means "one or more", so the pattern matches the first run of digits. Google's own example does the same thing: extracting "\d+" from a sentence returns 241, the first number.
The result is text
The help page says the output is text. To get a number you can add up, wrap the formula in VALUE:
=VALUE(REGEXEXTRACT(A2, "\d+"))
This returns the number 4821. The reverse also holds: to use a number as input, convert it to text first with TEXT.
Capture groups
Parentheses create capture groups. Without them the function returns the whole match. With them, per the help page, it can return multiple results. Google's example REGEXEXTRACT("You can also extract multiple values from text.", "You can also (\w+) multiple (\w+) from text.") returns two columns: extract and values.
Common mistakes
- Passing a number. Convert it with TEXT first.
- Forgetting the quotation marks around the pattern.
- Expecting every match. Only the first match is returned.
- Using the result as a number. It is text until you apply VALUE.
Try it yourself
Use the cell above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: First number
Write a formula that extracts the first run of digits from the text in A2.
Exercise 2: As a real number
Write a formula that extracts the digits from A2 and returns them as a number, not text.
What to learn next
- LEFT, RIGHT and MID: pull part of a text string by position.
- FIND: locate text inside a string.
- SUBSTITUTE: replace text inside a string.
Syntax and rules in this guide were checked against Google's REGEXEXTRACT help page. See all guides.