Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .