REGEXEXTRACT function
REGEXEXTRACT pulls out the part of a string that matches a regular expression, such as digits or an email address. It handles messy text better than MID and FIND, and returns #N/A when nothing matches.
Example
Read it as a sentence: find the first run of digits in order 123, which gives 123.
Syntax
REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Arguments in [square brackets] are optional.
-
text(required) - The text to search.
-
pattern(required) - The regular expression to match.
-
return_mode(optional) - 0 first match (default), 1 all matches as an array, 2 capture groups of the first match.
-
case_sensitivity(optional) - 0 case-sensitive (default), 1 ignore case.
Returns: The matched text, or an array of matches.
One REGEXEXTRACT formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=REGEXEXTRACT("a1b22c333","\d+") | 1 | First match |
=REGEXEXTRACT("a1b22c333","\d+",1) | 1 | All matches spill |
=REGEXEXTRACT("ab-12","([a-z]+)-(\d+)",2) | ab | Capture groups spill |
=REGEXEXTRACT("ABC","b",0,1) | B | Case-insensitive |
=REGEXEXTRACT(12345,"3.") | 34 | Number input |
=REGEXEXTRACT({"a1","b2"},"\d") | 1 | Array input spills |
=REGEXEXTRACT("abc","\d") | #N/A | No match |
=REGEXEXTRACT("ABC","b") | #N/A | Case-sensitive default |
Watch out for
- Returns #N/A when there is no match.
- Return modes 1 and 2 spill to several cells.
Where it works
- Excel
- REGEXEXTRACT is only in Excel for Microsoft 365 and Excel for the web. Recent Microsoft 365 builds only.
- Google Sheets
- Same name, but behaves differently. Sheets REGEXEXTRACT has no return_mode or case arguments; it uses RE2 syntax.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .