Available in:Excel for Microsoft 365Category:Text

REGEXTEST

REGEXTEST in Dutch Excel

Check whether a text contains a pattern, or completely follows it, such as a reference scheme, and get TRUE or FALSE.

Difficulty rating

Last updated: 23 October 2026

Sheet excerpt: D2:D6 holds the formula
Row	A	B	C	D
1	Reference	Customer	Amount	Follows scheme
2	INV-250412	Bakker Installatie	€1,210.00€ 1.210,00	TRUE=REGEXTEST(A2, "^INV-[0-9]{6}$")=REGEXTEST(A2; "^INV-[0-9]{6}$")
3	INV-25041	Hofman Aannemers	€845.50€ 845,50	FALSE=REGEXTEST(A3, "^INV-[0-9]{6}$")=REGEXTEST(A3; "^INV-[0-9]{6}$")
4	INV-250413	Van Dijk Transport	€2,420.00€ 2.420,00	TRUE=REGEXTEST(A4, "^INV-[0-9]{6}$")=REGEXTEST(A4; "^INV-[0-9]{6}$")
5	INV-2504138	De Wit Metaal	€660.00€ 660,00	FALSE=REGEXTEST(A5, "^INV-[0-9]{6}$")=REGEXTEST(A5; "^INV-[0-9]{6}$")
6	INV 250414	Smit Groenvoorziening	€1,815.00€ 1.815,00	FALSE=REGEXTEST(A6, "^INV-[0-9]{6}$")=REGEXTEST(A6; "^INV-[0-9]{6}$")

Result: TRUE

=REGEXTEST(A2, "^INV-[0-9]{6}$")

=REGEXTEST(A2; "^INV-[0-9]{6}$")

What REGEXTEST does and when to use it

REGEXTEST checks a text against a pattern and answers TRUE or FALSE. It tells you whether a reference, code or description follows the scheme you describe, for example INV- followed by exactly six digits.

It is one of the Microsoft 365 functions that read a regular expression, together with REGEXEXTRACT and REGEXREPLACE.

Typical uses

Reach for it when the question is whether a text follows a scheme, which wildcards cannot express:

reference schemes: invoice numbers, journal entry keys, document numbers
format tests on master data: IBANs, VAT numbers, postcodes
flagging the exceptions in an export, with FILTER or a helper column

Wildcards stay enough when you only look for a fixed word: COUNTIF with *correction* is shorter and works in every Excel version. Note that ? accepts any character, so INV-?????? also passes letters, where [0-9]{6} accepts only six digits.

COUNTIF with a wildcard or REGEXTEST?

Compare with COUNTIF

COUNTIF: does the reference look like INV- and something
```
=COUNTIF(A2, "INV-??????")=1
```
```
=COUNTIF(A2; "INV-??????")=1
```
REGEXTEST: does it follow the scheme exactly
```
=REGEXTEST(A2, "^INV-[0-9]{6}$")
```
```
=REGEXTEST(A2; "^INV-[0-9]{6}$")
```

What changes between the two

? accepts any character, so COUNTIF also passes INV-25041A; [0-9]{6} accepts six digits and nothing else.
COUNTIF ignores upper and lower case; REGEXTEST is case sensitive unless you set case_sensitivity to 1.
COUNTIF needs the =1 to turn a count into TRUE or FALSE; REGEXTEST answers TRUE or FALSE itself.
COUNTIF works in every Excel version; REGEXTEST needs Microsoft 365.

Colleague on Excel 2019 or older

Excel 2021, Excel 2024 and older versions do not have REGEXTEST and show #NAME? once the file recalculates. For files that go to those versions, test the scheme with COUNTIF and a wildcard, or with LEFT and LEN as the Master example shows.

Read the COUNTIF page

How Excel evaluates it

Excel tries to fit the pattern into the text at every position, from the first character on:

an ordinary character such as I must be that character, so the pattern INV- is found anywhere the text holds INV-
[0-9] accepts one digit, [A-Z] one capital letter, and a number in braces such as {6} says how many times the item before it must occur
^ fits only at the very start of the text and $ only at the very end

As soon as one position works, REGEXTEST returns TRUE. If no position works it returns FALSE. Without ^ and $ the pattern may fit a part of the text; with both, only the whole text counts.

Watch out: Without ^ and $ the answer is TRUE when the pattern appears anywhere in the text, so a reference that is too long still passes a test for exactly six digits.

Mental model

Think of the pattern as a stencil. Without anchors you slide it along the text and ask whether it fits anywhere; with ^ and $ you pin it to both ends.

Terms used on this page: regular expression, wildcard, reference

Syntax

Official REGEXTEST documentation

REGEXTEST(text, pattern, [case_sensitivity])

REGEXTEST(text; pattern; [case_sensitivity])

Arguments
Argument	Type	Meaning	Default
`text`	Required	The text or cell to test.
`pattern`	Required	The regular expression to look for.
`case_sensitivity`	Optional	0 = case sensitive (default), 1 = ignore case.	`0`

Return value:TRUE if any part of the text fits the pattern, otherwise FALSE.

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

Test invoice numbers against the INV- scheme

You are testing the sales journal of a client whose invoices are numbered INV- followed by exactly six digits. The export has a column of references, and you want a TRUE or FALSE per row so that the exceptions stand out.

Sheet excerpt: D2:D6 holds the formula
Row	A	B	C	D
1	Reference	Customer	Amount	Follows scheme
2	INV-250412	Bakker Installatie	€1,210.00€ 1.210,00	TRUE=REGEXTEST(A2, "^INV-[0-9]{6}$")=REGEXTEST(A2; "^INV-[0-9]{6}$")
3	INV-25041	Hofman Aannemers	€845.50€ 845,50	FALSE=REGEXTEST(A3, "^INV-[0-9]{6}$")=REGEXTEST(A3; "^INV-[0-9]{6}$")
4	INV-250413	Van Dijk Transport	€2,420.00€ 2.420,00	TRUE=REGEXTEST(A4, "^INV-[0-9]{6}$")=REGEXTEST(A4; "^INV-[0-9]{6}$")
5	INV-2504138	De Wit Metaal	€660.00€ 660,00	FALSE=REGEXTEST(A5, "^INV-[0-9]{6}$")=REGEXTEST(A5; "^INV-[0-9]{6}$")
6	INV 250414	Smit Groenvoorziening	€1,815.00€ 1.815,00	FALSE=REGEXTEST(A6, "^INV-[0-9]{6}$")=REGEXTEST(A6; "^INV-[0-9]{6}$")
7

Formula cell (D2:D6)

Formula in D2

=REGEXTEST(A2, "^INV-[0-9]{6}$")

=REGEXTEST(A2; "^INV-[0-9]{6}$")

Result: TRUE

Step by step

A2 holds INV-250412 and the pattern is ^INV-[0-9]{6}$. Read piece by piece: ^ the start of the text, INV- those four characters, [0-9]{6} exactly six digits, $ the end of the text.
Excel starts at the first character. I, N, V and - fit the first four characters. Then six digits are needed: 2, 5, 0, 4, 1, 2 are all digits. $ fits because the text ends right there. The whole pattern fits, so D2 shows TRUE.
Copy D2 down. A3 holds INV-25041: after INV- only 5 digits follow and then the text ends, so the sixth digit is missing. Because ^ allows only the start, no other starting position is tried. The answer is FALSE.
A4 holds INV-250413, six digits, so TRUE. A5 holds INV-2504138, seven digits: the sixth digit fits, but $ requires the end of the text and an 8 is still there, so FALSE.
A6 holds INV 250414. The pattern needs a hyphen after INV and finds a space, so FALSE.
Remove ^ and $ and D5 changes: INV-[0-9]{6} finds INV-250413 inside INV-2504138, so the test says TRUE for a number that is too long.

Watch out: A reference with a trailing space, such as INV-250412 with a space after it, fails because of $. If the export pads values, clean the column with TRIM first.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

REGEXTEST asks whether the pattern fits somewhere in the text. The anchors turn that into whether it fits the whole text, and {6} makes the number of digits exact. Wildcards cannot do the second part: INV-?????? also accepts letters, because ? stands for any character.

More to watch out for

Test a new pattern with a value that is one character too long and one that is one character too short. A pattern that only ever sees correct values proves nothing.

Variations

=REGEXTEST(A2:A6, "^INV-[0-9]{6}$")

=REGEXTEST(A2:A6; "^INV-[0-9]{6}$")

One formula in D2 returns all five answers and spills down; use it when you do not need to copy a formula.

=NOT(REGEXTEST(A2, "^INV-[0-9]{6}$"))

=NOT(REGEXTEST(A2; "^INV-[0-9]{6}$"))

Flips the answer so the exceptions are TRUE, which suits a filter on the column.

Intermediate

Test supplier IBANs and ignore capitals

In the supplier master file you check that every Dutch IBAN has the format NL, two digits, four letters and ten digits. Some were typed in lowercase, and each row should show OK or Check so you can follow up the exceptions.

Sheet excerpt: C2:C7 holds the formula
Row	A	B	C
1	Supplier	IBAN	Format
2	Dekker Bouwmaterialen BV	NL12RABO0123456789	OK=IF(REGEXTEST(B2, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")=IF(REGEXTEST(B2; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")
3	Van Dijk Transport	nl45ingb0987654321	OK=IF(REGEXTEST(B3, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")=IF(REGEXTEST(B3; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")
4	Hoekstra Installatie	NL67INGB098765432	Check=IF(REGEXTEST(B4, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")=IF(REGEXTEST(B4; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")
5	Smit Groenvoorziening	NL33 ABNA 0123 4567 89	Check=IF(REGEXTEST(B5, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")=IF(REGEXTEST(B5; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")
6	De Wit Metaal	DE89370400440532013000	Check=IF(REGEXTEST(B6, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")=IF(REGEXTEST(B6; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")
7	Yilmaz Catering	NL58ABNA0612345678	OK=IF(REGEXTEST(B7, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")=IF(REGEXTEST(B7; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")
8

Formula cell (C2:C7)Note: B5 holds the IBAN as printed on an invoice, in groups of four with spaces.

Formula in C2

=IF(REGEXTEST(B2, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")

=IF(REGEXTEST(B2; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")

Result: OK

Step by step

Inside out: REGEXTEST reads B2, NL12RABO0123456789, and the pattern. ^NL is the start and the letters NL, [0-9]{2} two digits, [A-Z]{4} four capitals, [0-9]{10} ten digits, $ the end. The third argument 1 tells Excel to ignore case.
NL fits, 12 fits, RABO fits, 0123456789 is 10 digits and fits, and the text ends. REGEXTEST returns TRUE, so IF returns OK in C2.
B3 holds nl45ingb0987654321, all lowercase. Because case is ignored, nl fits NL and ingb fits [A-Z]{4}, so TRUE and OK. Without the third argument the lowercase n would not fit N, and a correct IBAN would get Check.
B4 holds NL67INGB098765432: after INGB come 9 digits (098765432) and the pattern needs 10, so FALSE and Check.
B5 holds NL33 ABNA 0123 4567 89. After NL33 the pattern wants a letter and finds a space, so FALSE. B6 starts with DE, and ^NL fails at once. Both get Check.
B7 holds NL58ABNA0612345678: NL, 02, ABNA, 10 digits, end. TRUE and OK.

Watch out: Spaces fail the test, as in B5. The IBAN may be right, but the format is not the one in the pattern. Remove the spaces first, as the first variation does.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

The third argument changes only how letters are compared; the digits and the lengths are still checked strictly. IF turns the TRUE or FALSE into a word that a reviewer can filter on. The test checks the shape only: it does not calculate the check digits, so an IBAN with the right shape and a mistyped digit still passes.

More to watch out for

Ignoring case is the right choice here, but it is not neutral. If the client's system must hold capitals, a lowercase IBAN is a finding, and with 1 you would not see it. Use the default there.

Variations

=IF(REGEXTEST(SUBSTITUTE(B2, " ", ""), "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Check")

=IF(REGEXTEST(SUBSTITUTE(B2; " "; ""); "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Check")

Removes the spaces first, so the printed form in B5 becomes NL33ABNA0123456789 and passes.

=REGEXTEST(B2, "(?i)^NL[0-9]{2}[A-Z]{4}[0-9]{10}$")

=REGEXTEST(B2; "(?i)^NL[0-9]{2}[A-Z]{4}[0-9]{10}$")

(?i) at the start of the pattern does the same as the third argument. Prefer it when the pattern lives in a cell and must carry its own case rule.

Advanced

List journal keys that break the scheme

An auditfile export carries a key per journal line: journal, entry number, relation and VAT rate, separated by |, as in VRK|25-0388|D1002|21%. You want only the lines whose key breaks the scheme, with their amount, as one list for your working paper.

Sheet excerpt: D2 holds the formula
Row	A	B	D	E
1	Journal key	Amount	Key	Amount
2	VRK\|25-0388\|D1002\|21%	€12,100.00€ 12.100,00	vrk\|25-0392\|D1005\|21%=FILTER(A2:B9, NOT(REGEXTEST(A2:A9, "^(VRK\|INK\|BNK\|MEM)\\|[0-9]{2}-[0-9]{4}\\|[DC][0-9]{4}\\|(0\|9\|21)%$")), "No exceptions")=FILTER(A2:B9; NOT(REGEXTEST(A2:A9; "^(VRK\|INK\|BNK\|MEM)\\|[0-9]{2}-[0-9]{4}\\|[DC][0-9]{4}\\|(0\|9\|21)%$")); "No exceptions")	€890.00€ 890,00
3	INK\|25-0391\|C2001\|21%	€4,235.50€ 4.235,50	VRK\|25-03975\|D1004\|9%	€560.00€ 560,00
4	vrk\|25-0392\|D1005\|21%	€890.00€ 890,00	INK\|25-0401\|C2003\|19%	€2,400.00€ 2.400,00
5	MEM\|25-0395\|D1002\|21%	€15,000.00€ 15.000,00	VRK\|25-0402\|D1003\|21%	€7,800.00€ 7.800,00
6	BNK\|25-0397\|C2001\|0%	€3,025.00€ 3.025,00
7	VRK\|25-03975\|D1004\|9%	€560.00€ 560,00
8	INK\|25-0401\|C2003\|19%	€2,400.00€ 2.400,00
9	VRK\|25-0402\|D1003\|21%	€7,800.00€ 7.800,00
10

Formula cell (D2)Spilled resultMarks: extra spaceNote: The scheme: journal VRK, INK, BNK or MEM; entry as two digits, a hyphen and four digits; relation D or C and four digits; VAT 0%, 9% or 21%. The key in A9 ends with a space.

Formula in D2

=FILTER(A2:B9, NOT(REGEXTEST(A2:A9, "^(VRK|INK|BNK|MEM)\|[0-9]{2}-[0-9]{4}\|[DC][0-9]{4}\|(0|9|21)%$")), "No exceptions")

=FILTER(A2:B9; NOT(REGEXTEST(A2:A9; "^(VRK|INK|BNK|MEM)\|[0-9]{2}-[0-9]{4}\|[DC][0-9]{4}\|(0|9|21)%$")); "No exceptions")

Result: vrk|25-0392|D1005|21% | €890.00 / VRK|25-03975|D1004|9% | €560.00 / INK|25-0401|C2003|19% | €2,400.00 / VRK|25-0402|D1003|21% | €7,800.00vrk|25-0392|D1005|21% | € 890,00 / VRK|25-03975|D1004|9% | € 560,00 / INK|25-0401|C2003|19% | € 2.400,00 / VRK|25-0402|D1003|21% | € 7.800,00

Step by step

Inside out: REGEXTEST gets the whole range A2:A9, so it tests all 8 keys in one go and returns 8 answers. The pattern reads: ^ start; (VRK|INK|BNK|MEM) one of four journals (inside the brackets the plain | means "or"); \| a real pipe character; [0-9]{2}-[0-9]{4} year and entry number; \|; [DC][0-9]{4} relation D or C plus four digits; \|; (0|9|21)% a VAT rate of 0, 9 or 21 percent; $ end.
Row by row: A2, A3, A5 and A6 fit the whole pattern. A4 starts with the lowercase vrk, which fits none of the four journals because matching is case sensitive. A7 has 03975: after the hyphen the pattern wants four digits and then \|, but a fifth digit comes.
A8 has a VAT rate of 19%, and none of 0, 9 or 21 fits. A9 fits all the way to 21%, but a space follows and $ wants the end of the text. REGEXTEST returns TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE.
NOT turns every answer round: FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE. Now TRUE marks an exception.
FILTER keeps the rows of A2:B9 where the include argument is TRUE: sheet rows 4, 7, 8 and 9. The result is 4 rows by 2 columns and spills into D2:E5: vrk|25-0392|D1005|21% with €890.00€ 890,00, VRK|25-03975|D1004|9% with €560.00€ 560,00, INK|25-0401|C2003|19% with €2,400.00€ 2.400,00 and VRK|25-0402|D1003|21% with €7,800.00€ 7.800,00.
If every key passes, all answers after NOT are FALSE, nothing is kept, and the cell shows No exceptions.

Watch out: Silent error: leave out the backslashes in \| and each plain | becomes "or". The pattern then says TRUE as soon as any one piece is found, so all 8 keys pass and the cell shows No exceptions, with no error.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

REGEXTEST works on a whole range at once and returns one answer per cell, which is exactly the shape FILTER needs for its include argument. NOT turns "follows the scheme" into "breaks the scheme", so FILTER lists the findings.

More to watch out for

The key in A9 looks correct on screen. Only the space after % makes it an exception, and $ is what catches it. Keep $, then decide whether such a space is a finding or something to clean with TRIM.
The key range and the filtered range must have the same height, here A2:A9 and A2:B9. A range one row longer gives #VALUE!.

Variations

=SUMPRODUCT(--NOT(REGEXTEST(A2:A9, "^(VRK|INK|BNK|MEM)\|[0-9]{2}-[0-9]{4}\|[DC][0-9]{4}\|(0|9|21)%$")))

=SUMPRODUCT(--NOT(REGEXTEST(A2:A9; "^(VRK|INK|BNK|MEM)\|[0-9]{2}-[0-9]{4}\|[DC][0-9]{4}\|(0|9|21)%$")))

Counts the exceptions instead of listing them: 4. Use it for the total on your working paper.

=FILTER(A2:B9, NOT(REGEXTEST(A2:A9, "^(VRK|INK|BNK|MEM)\|[0-9]{2}-[0-9]{4}\|[DC][0-9]{4}\|(0|9|21)%$", 1)), "No exceptions")

=FILTER(A2:B9; NOT(REGEXTEST(A2:A9; "^(VRK|INK|BNK|MEM)\|[0-9]{2}-[0-9]{4}\|[DC][0-9]{4}\|(0|9|21)%$"; 1)); "No exceptions")

With case ignored, vrk|25-0392|D1005|21% passes and 3 rows remain. Use it only when capitals are not part of the scheme.

Master

Check a VAT number list and name the failures

Before you check the VAT numbers of seven customers against the official register, you test their format: NL, nine digits, B and two digits. One cell reports how many fail and lists them, using a pattern kept in a cell that colleagues can read and change.

Sheet excerpt: E2 holds the formula
Row	A	B	D	E
1	Customer	VAT number	Pattern	^NL[0-9]{9}B[0-9]{2}$
2	Postma Installatie BV	NL123456789B01	Result	3 to check: NL345678901B1, NL 456789012 B01, BE0123456789=LET( vat, B2:B8, pattern, E1, valid, REGEXTEST(vat, pattern, 1), failed, SUMPRODUCT(--NOT(valid)), IF(failed = 0, "All valid", failed & " to check: " & TEXTJOIN(", ", TRUE, FILTER(vat, NOT(valid)))) )=LET( vat; B2:B8; pattern; E1; valid; REGEXTEST(vat; pattern; 1); failed; SUMPRODUCT(--NOT(valid)); IF(failed = 0; "All valid"; failed & " to check: " & TEXTJOIN(", "; TRUE; FILTER(vat; NOT(valid)))) )
3	Bakker Metaal BV	nl234567890b01
4	Brands Bouw BV	NL345678901B1
5	De Boer Handel BV	NL 456789012 B01
6	Smit Groenvoorziening BV	NL567890123B02
7	Jansen Logistiek BV	BE0123456789
8	Yilmaz Catering BV	NL678901234B01
9

Formula cell (E2)Input

Formula in E2

=LET(
  vat, B2:B8,
  pattern, E1,
  valid, REGEXTEST(vat, pattern, 1),
  failed, SUMPRODUCT(--NOT(valid)),
  IF(failed = 0, "All valid", failed & " to check: " & TEXTJOIN(", ", TRUE, FILTER(vat, NOT(valid))))
)

=LET(
  vat; B2:B8;
  pattern; E1;
  valid; REGEXTEST(vat; pattern; 1);
  failed; SUMPRODUCT(--NOT(valid));
  IF(failed = 0; "All valid"; failed & " to check: " & TEXTJOIN(", "; TRUE; FILTER(vat; NOT(valid))))
)

Result: 3 to check: NL345678901B1, NL 456789012 B01, BE0123456789

Step by step

LET binds the names in order. vat becomes the 7 cells of B2:B8, and pattern becomes the text in E1, ^NL[0-9]{9}B[0-9]{2}$. Nothing is tested yet.
valid is REGEXTEST over all 7 cells with case_sensitivity 1. B2 fits. B3, nl234567890b01, fits because lowercase counts as NL and B. B4, NL345678901B1, has one digit after the B where two are needed. B5 has spaces. B6 fits. B7, BE0123456789, starts with BE. B8 fits. The array is TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE.
failed: NOT turns the array into FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE. The double minus makes it 0, 0, 1, 1, 0, 1, 0, and SUMPRODUCT adds them: 3.
The last argument of LET is the IF. failed = 0 is FALSE, so IF takes the second branch.
FILTER keeps the cells of vat where NOT(valid) is TRUE: NL345678901B1, NL 456789012 B01 and BE0123456789. TEXTJOIN puts , between them, and the & signs put 3 and the text to check: in front. The result is 3 to check: NL345678901B1, NL 456789012 B01, BE0123456789.
Correct B4 to NL345678901B01 and the count drops to 2. Correct all three and failed is 0, so the cell shows All valid.

Watch out: The mirror image of the case mistake: case_sensitivity 1 looks safe but hides nl234567890b01. If the client's system must store capitals, that row is a finding. With the default 0 the count is 4 instead of 3.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

LET names the REGEXTEST result once, so the count and the list use the same answers, and the pattern in E1 is visible to a reviewer and needs no doubled quotes. Do not use this construction when the rule is only a prefix and a length: AND(LEFT(B2, 2) = "NL", LEN(B2) = 14) is shorter to read and works in every Excel version, at the price of not checking that the middle is digits. Neither version tells you whether the number exists; that needs the register.

More to watch out for

A pattern in a cell is read exactly as typed. A trailing space in E1 becomes part of the pattern and every number fails. When all rows fail at once, check the pattern cell first.
A pass proves the shape, not the number. NL999999999B99 passes the test and may not exist.

Variations

=AND(LEFT(B2, 2) = "NL", LEN(B2) = 14)

=AND(LEFT(B2; 2) = "NL"; LEN(B2) = 14)

The older-Excel alternative for one number: a prefix and a length. On this data it gives the same answers as the pattern, but NL12345678XB01 would also pass.

=XLOOKUP(FALSE, REGEXTEST(B2:B8, E1, 1), B2:B8, "All valid")

=XLOOKUP(FALSE; REGEXTEST(B2:B8; E1; 1); B2:B8; "All valid")

Returns only the first number that fails: NL345678901B1. Add -1 as the sixth argument of XLOOKUP (leave the fifth empty) for the last one.

Common mistakes

Common mistakes
Symptom	Cause	Fix
TRUE for a reference that is too long	The pattern has no `^` and `$`, so a match anywhere in the text is enough.	Write `^INV-[0-9]{6}$` to test the whole cell. Test every new pattern with a value that is one character too long and one that is one character too short.
FALSE for inv-250412, which looks right	Matching is case sensitive by default, so a lowercase `inv` does not fit `INV`.	Set the third argument to 1, or start the pattern with `(?i)`. Keep the default when the scheme itself requires capitals.
TRUE for almost every text	A `\|` in a pattern means "or", not the character itself. In `VRK\|25-0388` Excel reads: `VRK` or `25-0388`.	Write `\\|` for the character itself. Inside a group such as `(VRK\|INK)` the plain `\|` is correct.
TRUE for a date with the wrong separator	A `.` in a pattern accepts any character, so `^[0-9]{2}.[0-9]{2}$` passes `25-04` and `25x04`.	Write `\.` for a real dot. A hyphen outside square brackets needs no backslash.
#NAME? in Excel 2021 or 2024	REGEXTEST exists in Microsoft 365 only. Older versions do not know the function.	For a file that goes to Excel 2021 or 2024, build the test from LEFT and LEN, as the alternative in the Master example shows.

Used in combinations

Find the customer with a regex lookupPull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.Excel for Microsoft 365 (This combination needs Excel for Microsoft 365)