REGEXEXTRACT

What it does

REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

REGEXEXTRACT searches text for the regular expression pattern and returns what it matched.

Unlike its siblings it is not one function but three, selected by return_mode, and the three differ in shape as well as in content:

That table is the whole function, and each row deserves a sentence.

Mode 0 performs a leftmost search and returns the matched substring. Not the whole text, not the position — the matched text itself.

Mode 1 returns all non-overlapping matches. This is the mode that makes REGEXEXTRACT a dynamic-array function: the result spills, and its length depends on the data, which is exactly the case that dynamic arrays were introduced for. The scan order and the non-overlap rule are standard regex semantics (each search resumes after the previous match), though nothing consulted here states how a zero-width match is advanced past — a real question, since a pattern that can match empty would otherwise loop.

Mode 2 returns the capture groups of the first match. This is the mode that turns a regex into a parser: one pattern with parenthesised groups splits a string into its parts in a single call. Note what it does not say — whether group 0 (the whole match) is included, whether unmatched optional groups appear as empty strings or #N/A, and what the shape is (row or column). None of those is settled here.

The dialect is PCRE2, documented on the family's REGEXTEST page. And one further documented note matters more than it looks: REGEXEXTRACT always returns text values. Extracting "123" gives you the string "123", and Microsoft's page explicitly points you at VALUE to convert it — which drags in every locale question on the VALUE page. For numeric extraction, NUMBERVALUE with explicit separators is the more portable second step.

Arguments

REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

Two argument positions cause trouble.

return_mode is the position readers get wrong when porting from other spreadsheet dialects, where the equivalent function's third argument means something else entirely. It is a shape selector: changing it changes whether you get a scalar or an array, which changes whether the formula spills, which changes whether it fits where you put it.

case_sensitivity has the same inverted-looking polarity as elsewhere in the family: 0 is case-sensitive. It also overlaps with PCRE2's inline (?i) flag, and nothing consulted here states which wins if they disagree.

Try it

Quirks

Result and edge cases

The return kind depends on return_mode: Text in mode 0, Array of text in modes 1 and 2. Array results are published by the host-side adaptation step described in the call pipeline, which is also where #SPILL! comes from.

• Everything comes back as text. Documented, and worth repeating because it is the most common source of downstream surprise.
• Non-text arguments coerce to text by the ordinary rules in coercion and lifting; the generated battery rows where a number is passed as both text and pattern are coercion artefacts, not regex behaviour.
• Errors propagate ahead of matching.
• An empty referenced cell delivers Empty, distinct from an omitted argument; see the value universe.
• Array-valued text — the obvious way to extract from a column — is not asserted here, and it interacts badly with modes 1 and 2 in any case: an array of arrays is not a value this model admits (see the value universe), so lifting cannot be unconditional.

Errors

Microsoft's page publishes no explicit error table for this function (confirmed on the retrieved page). What can be stated:

How to implement

Notes for implementers

• Mode 2 is where the design decisions live. Group numbering, group 0, unmatched optional groups, named groups, and result orientation are five separate choices, and this Handbook has evidence for none of them. Write down what you chose.
• Advance past zero-width matches in mode 1, or the all-matches loop does not terminate. The standard rule is to bump the search position by one when a match is empty; whether Excel does that, and whether the empty match is included in the output, is unprobed.
• The escape set is part of the contract. The upstream record's live-Excel comparison lists, as admitted on the build it tested: \d \D \w \W \s \S; \A \Z \z \b \B; \n \t \r \f \v \e \h; and the escaped literals \. \* \+ \? \[ \] \( \) \| \^ \$ \/ \\. It lists unknown letter escapes such as \q \k \m \g \p \c \x \y \j \o as rejected. That is an upstream observation on one Excel build, not a Handbook claim; re-probe before depending on it.
• Results are text, so do not helpfully convert. Returning a number because the match looked numeric would be a divergence from the documented contract.
• Decide the no-match shape for modes 1 and 2 explicitly, since an empty array is not representable.
• Bound the match effort. A pathological pattern in a recalculating workbook is a hang.

Rust code

17 regexextract_fn "REGEXEXTRACT" (2, 4) [V "text", V "pattern", V "return_mode", V "case_sensitivity"] |s, a, sv| { regexextract(s, a) }

fn regexextract(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let text = text_arg(site, a, 0)?;
    let mut pattern = text_arg(site, a, 1)?;
    let mode = if given(a, 2) {
        num(site, a, 2)?.trunc()
    } else {
        0.0
    };
    let sensitive = case_sensitive(site, a, 3)?;
    // Fn5 regex002: one capture around the entire supported pattern is its
    // first whole match. The existing parser still refuses inner groups and
    // other unsupported syntax; no second matching implementation is needed.
    let mut captured = false;
    if mode == 2.0 && tk::is_well_formed(&pattern) {
        let raw = pattern.to_string_lossy();
        if let Some(inner) = raw.strip_prefix('(').and_then(|s| s.strip_suffix(')')) {
            pattern = ExcelText::from_utf16_code_units(inner.encode_utf16().collect());
            captured = true;
        }
    }
    let (chars, found) = regex(site, &text, &pattern, sensitive, mode == 0.0 || captured)?;
    match (mode as i64, found.as_slice()) {
        (_, []) if mode == 0.0 || mode == 1.0 => fail(WorksheetErrorCode::NA),
        (0, [(start, end), ..]) | (1, [(start, end)]) => Ok(crate::catalogue::kit::text_value(
            &chars[*start..*end].iter().collect::<String>(),
        )),
        // Fn5 regex003..005 settle the horizontal shape. Lifted fn2 roots078/079
        // retain each call's first cell through the existing evaluator.
        (1, matches) => {
            let cells = matches
                .iter()
                .map(|(start, end)| {
                    crate::catalogue::kit::text_value(
                        &chars[*start..*end].iter().collect::<String>(),
                    )
                })
                .collect();
            Ok(CalcValue::array(
                CalcArray::new(
                    ArrayShape {
                        rows: 1,
                        cols: matches.len(),
                    },
                    cells,
                )
                .expect("a nonempty list of matches"),
            ))
        }
        (2, [(start, end), ..]) if captured => Ok(crate::catalogue::kit::text_value(
            &chars[*start..*end].iter().collect::<String>(),
        )),
        // A supported group-free pattern is Value even without a match
        // (fn5 regex001); an escaped parenthesis is still group-free (006).
        (2, _) if !captured => fail(WorksheetErrorCode::Value),
        // No-match capture-group behavior remains unmeasured.
        (0..=2, _) => Err(site.not_modelled(Some(2))),
        _ => fail(WorksheetErrorCode::Value),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• REGEXTEST — the predicate version. Use it when you only need to know whether a match exists; it avoids the #N/A-handling that REGEXEXTRACT forces on you.
• REGEXREPLACE — the rewriting version, same engine.
• TEXTAFTER, TEXTBEFORE, TEXTSPLIT — the delimiter-based extraction family. For anything that is genuinely delimiter-shaped, these are clearer, cheaper and far easier to audit than a regex. Reach for REGEXEXTRACT when the structure is a pattern, not a separator.
• MID with FIND/SEARCH — the legacy extraction idiom. Not superseded; still the right tool when the position is known.
• VALUE and NUMBERVALUE — the necessary second step whenever what you extracted was a number.
• FILTER and IFNA — the usual companions for mode-1 results.

Named variants

No named variants are recorded yet.