REGEXEXTRACT
Pull text out of a string using a regular expression pattern. Great for grabbing phone numbers, codes, or anything with a predictable shape.
Spreadsheet editor
Spreadsheet editor
Syntax
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Returns: Text Arguments
| Argument | Required | Description |
|---|---|---|
| text | Yes | The text you want to search inside. Type it in quotation marks or point to a cell that holds the text. |
| pattern | Yes | The regular expression that describes what you want to pull out. Excel uses the PCRE2 style of regex. |
| return_mode | No | What to return. 0 (the default) gives the first match. 1 returns every match as an array. 2 returns the capturing groups (the parts you wrap in parentheses) from the first match as an array. |
| case_sensitivity | No | Set to 0 (the default) to treat uppercase and lowercase as different, or 1 to ignore case while matching. |
About
REGEXEXTRACT searches your text for a pattern you describe with a regular expression and returns the part that matches. You hand it the text and a pattern, and it pulls back the first match it finds. This makes it perfect for grabbing values that follow a shape, like an order number, an email, or the digits inside a messy string, even when their position keeps changing.
Use it when a simple delimiter split is not enough. Functions like TEXTAFTER and TEXTBEFORE work well when you can point at one character, but REGEXEXTRACT shines when you need to match a pattern instead. With the return_mode argument you can get just the first match, every match as an array, or the capturing groups (the pieces you wrap in parentheses) from the first match. The case_sensitivity argument lets you decide whether uppercase and lowercase should count as the same.
REGEXEXTRACT works in Microsoft 365 and uses the PCRE2 flavor of regex, the same style you may know from many programming languages. It always returns text, so wrap the result in VALUE when you need a real number. On older versions of Excel you can fall back to combinations of MID, FIND, and SEARCH, though they take far more work to set up.
Use it when a simple delimiter split is not enough. Functions like TEXTAFTER and TEXTBEFORE work well when you can point at one character, but REGEXEXTRACT shines when you need to match a pattern instead. With the return_mode argument you can get just the first match, every match as an array, or the capturing groups (the pieces you wrap in parentheses) from the first match. The case_sensitivity argument lets you decide whether uppercase and lowercase should count as the same.
REGEXEXTRACT works in Microsoft 365 and uses the PCRE2 flavor of regex, the same style you may know from many programming languages. It always returns text, so wrap the result in VALUE when you need a real number. On older versions of Excel you can fall back to combinations of MID, FIND, and SEARCH, though they take far more work to set up.
Examples
Handle rows with no match
Watch what happens when a note has no order code. The plain formula returns #N/A, so wrap it in IFERROR to show a friendly message instead. Edit the notes to see both columns react.
Spreadsheet editor
Add up matched numbers
See why a matched price will not add up on its own. REGEXEXTRACT returns text, so use VALUE to turn it into a real number, then SUM the column. Change the amounts and watch the total update.
Spreadsheet editor
Watch out for
⚠
#N/A when nothing matches
If the pattern finds nothing in the text, REGEXEXTRACT returns a #N/A error, which can break the cells that depend on it.
→ Wrap the formula in IFERROR to return a blank or a friendly message, like =IFERROR(REGEXEXTRACT(A2, "\d+"), "").
⚠
The result is text, not a number
REGEXEXTRACT always returns text, so a matched number like 2045 will not add up or sort as a number.
→ Convert it with VALUE, like =VALUE(REGEXEXTRACT(A2, "\d+")), to get a real number back.
⚠
Backslashes need care in patterns
Regex shortcuts like \d and \w start with a backslash, and it is easy to forget them or type the wrong one, which makes the pattern miss.
→ Build the pattern slowly and test it on a few rows. Use \d for a digit, \w for a letter or digit, and \s for a space.
Tips & notes
REGEXEXTRACT uses the PCRE2 style of regex, so patterns you know from languages like Python or JavaScript usually work the same way. Set case_sensitivity to 1 when you want to ignore case. To check whether a pattern matches at all without pulling it out, REGEXTEST is the companion function, and REGEXREPLACE swaps matches for new text. When you only need to split on a single character, TEXTAFTER and TEXTBEFORE are simpler choices.
Common questions
How do I get all matches instead of just the first one?
Set return_mode to 1. For example, =REGEXEXTRACT(A2, "\d+", 1) returns every number in the text as a spilled array instead of only the first.
Why does REGEXEXTRACT return text when I expected a number?
REGEXEXTRACT always returns text, even when the match looks like a number. Wrap it in VALUE to turn the result into a real number you can calculate with.
Can I make the match ignore uppercase and lowercase?
Yes. Set the case_sensitivity argument to 1, like =REGEXEXTRACT(A2, "error", 0, 1), so it matches "Error", "ERROR", and "error" alike.
Does REGEXEXTRACT work in Google Sheets?
Yes. Google Sheets has REGEXEXTRACT too, with the syntax REGEXEXTRACT(text, regular_expression). It does not support the return_mode and case_sensitivity arguments, so it returns the first match only.