REGEXTEST
Return TRUE if text matches a regular expression — a valid email, an order number, a postcode — and FALSE if not.
Syntax
REGEXTEST(text, pattern, [case_sensitivity])| Argument | Required | Description |
|---|---|---|
text | Required | The text to test. |
pattern | Required | A regular expression. |
case_sensitivity | Optional | 0 (the default) for case-sensitive matching, 1 to ignore case. |
Examples
Every example below was executed against VisiGrid engine 0.51.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=REGEXTEST(A2,"^ORD-\d{5}$") | TRUE | ORD- followed by exactly five digits. |
=REGEXTEST(A3,"^ORD-\d{5}$") | FALSE | |
=REGEXTEST(A3,"^ord-",1) | TRUE | With case_sensitivity 1, ORD and ord both match. |
=REGEXTEST(B2,"^[^@\s]+@[^@\s]+\.[a-z]+$") | TRUE | |
=REGEXTEST(B3,"@") | FALSE |
Validation in one cell
REGEXTEST is the check behind a data-quality column: is every order number in
the right shape, does every row have something that looks like an email. Pair it
with IF for a readable flag, fill it down, then count the
flags with COUNTIF:
=IF(REGEXTEST(A2,"^ORD-\d{5}$"),"ok","check")Or count matches across a column in one cell — given a range, REGEXTEST
tests each cell:
=SUMPRODUCT(--REGEXTEST(A2:A100,"^ORD-\d{5}$"))Anchor the pattern
Without ^ and $, a pattern matches anywhere in the text: \d{5} is TRUE for
“ORD-209310” because it contains five digits in a row. Anchoring to the start
and end makes it a test of the whole value.
Case
Matching is case-sensitive unless the third argument is 1. \w and \d cover
accented and non-Latin letters and digits, so “José” is all word characters.
Not supported
Excel’s regex engine is PCRE2. This one runs in linear time on any input, which
rules out lookahead, lookbehind and backreferences; those patterns return
#VALUE!.
Excel compatibility
Matches Excel's REGEXTEST. Excel uses PCRE2; this engine has no lookaround ((?=...), (?<=...)) or backreferences (\1), and a pattern using them returns #VALUE! rather than matching differently.