REGEXTEST

What it does

REGEXTEST(text, pattern, [case_sensitivity])

REGEXTEST answers one question: does any part of the text match the pattern?

Formally, it returns TRUE if there exists any substring of text — including the empty substring, where the pattern permits it — that the regular expression pattern matches, and FALSE otherwise. It is a search, not a full match: the pattern does not have to describe the entire string. To demand a full match you anchor the pattern yourself with ^ and $, or with \A and \z.

The regex dialect is documented. Microsoft's page states that this function, together with REGEXEXTRACT and REGEXREPLACE, uses the PCRE2 flavour of regular expressions. That single sentence is the most valuable thing on the documentation page, because it converts a large open question ("which regex is this?") into a citation. It does not, however, mean everything PCRE2 can express is reachable from a worksheet cell — see below.

REGEXTEST returns a Logical, which makes it the natural condition for IF, FILTER and conditional formatting, and the natural building block for validation columns. Because it returns TRUE/FALSE rather than a value or an error, it composes better than the older ISNUMBER(SEARCH(...)) idiom it replaces.

Arguments

REGEXTEST(text, pattern, [case_sensitivity])

The argument that surprises people is the third, and in two ways. First its polarity: 0 means case-sensitive. The name reads like a switch that turns sensitivity on, and it is the opposite — 1 turns matching case-insensitive. Second, it duplicates something the pattern can already say: PCRE2 supports the inline (?i) flag, so there are two mechanisms for the same effect and nothing consulted here states how they interact when they disagree.

Note also what is not an argument: there is no way to ask for the match position or the matched text. That is REGEXEXTRACT's job.

Try it

Quirks

Result and edge cases

The return kind is Logical.

• Non-text arguments coerce to text. REGEXTEST(0, 0) asks whether the string "0" matches the pattern "0", which is a coercion consequence described in coercion and lifting, not a regex behaviour. The generated battery on this page shows several rows of exactly this shape and they should be read that way.
• A pattern that can match the empty string makes the answer trivially TRUE for any text, which is a common way to write a predicate that silently always passes. REGEXTEST(x, "a*") is TRUE for every x.
• The empty text argument is a real edge and is not obviously FALSE: an empty-matching pattern should still succeed against it. This Handbook has not established Excel's behaviour there.
• Errors propagate ahead of any matching.
• An empty referenced cell delivers Empty, distinct from an omitted argument; see the value universe.
• Array arguments. Whether REGEXTEST lifts over an array-valued text — which would be the natural way to validate a column — is not asserted here. The general lifting rules are in coercion and lifting.

Errors

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

How to implement

Notes for implementers

• Do not implement a regex subset with a silent fallthrough. This is the concrete lesson of BUG-FUNC-041 and it generalises: if your parser meets a construct it does not understand, fail, never guess. A subset engine that errors on what it cannot do is honest and usable; one that quietly reinterprets is worse than useless, because its wrong answers look like right ones.
• The escape set is a specification, not an implementation detail. The upstream record's live-Excel comparison lists, as admitted on the build it tested: the shorthand classes \d \D \w \W \s \S; the zero-width assertions \A \Z \z \b \B; the character escapes \n \t \r \f \v \e \h; and the escaped literal metacharacters \. \* \+ \? \[ \] \( \) \| \^ \$ \/ \\. It lists unknown letter escapes such as \q \k \m \g \p \c \x \y \j \o as rejected. Treat that as an upstream observation under one Excel build, not as a Handbook claim, and re-probe before relying on it — \p in particular is a Unicode-property escape in full PCRE2, so its appearance in the rejected list is a signal about the build tested rather than about PCRE2.
• Zero-width assertions must be assertion atoms, not literals. Modelling \b as a character is how the original defect arose.
• Anchor semantics need a line-mode decision. Whether ^ and $ match at embedded newlines depends on multiline mode; PCRE2 has both, and nothing consulted here says which Excel uses.
• Guard against catastrophic backtracking. A cell formula that never returns is a worse user experience than a #VALUE!. PCRE2 has match limits; use them, and decide what error a limit hit produces.
• Case-insensitivity is a folding choice, and the upstream contract records only ASCII folding in its local slice. Excel folds beyond ASCII, including inside character class ranges (FN-REGEXTEST-001), so ASCII folding alone will miss matches Excel makes.

Rust code

16 regextest_fn "REGEXTEST" (2, 3) [V "text", V "pattern", V "case_sensitivity"] |s, a, sv| { regextest(s, a) }

fn regextest(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let text = text_arg(site, a, 0)?;
    let pattern = text_arg(site, a, 1)?;
    let sensitive = case_sensitive(site, a, 2)?;
    let (_, found) = regex(site, &text, &pattern, sensitive, true)?;
    Ok(CalcValue::logical(!found.is_empty()))
}

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

• REGEXEXTRACT — same engine, returns the matched text instead of a verdict. REGEXTEST(t,p) is close to NOT(ISNA(REGEXEXTRACT(t,p))), though the two differ in how they report a pattern error.
• REGEXREPLACE — same engine, rewrites the matches.
• SEARCH and FIND — the predecessors. ISNUMBER(SEARCH(needle, hay)) is the classic containment test; SEARCH supports only *, ? and the ~ escape, and is case-insensitive while FIND is case-sensitive. REGEXTEST does not supersede either; both remain current, and for a plain substring test SEARCH is clearer and faster.
• COUNTIF with wildcards, and IFS/SWITCH chains, are the other idioms REGEXTEST displaces in validation work.
• EXACT — for whole-string equality, which does not need a regex.
• ISNUMBER(SEARCH(...)) returns TRUE/FALSE too, but silently treats a missing needle as FALSE; REGEXTEST distinguishes "no match" from "bad pattern" if the engine reports the latter.

Named variants

No named variants are recorded yet.