Beschikbaar in:Excel voor Microsoft 365Categorie:Tekst

REGEXTEST

REGEXTEST in Engelstalige Excel

Controleer of een tekst een patroon bevat, of er helemaal aan voldoet, zoals een referentieschema, en krijg WAAR of ONWAAR.

Moeilijkheidsgraad

Laatst bijgewerkt: 23 oktober 2026

Bekijk de uitgewerkte voorbeelden

Fragment van het werkblad: in D2:D6 staat de formule
Rij	A	B	C	D
1	Referentie	Klant	Bedrag	Volgt het schema
2	INV-250412	Bakker Installatie	€1,210.00€ 1.210,00	WAAR=REGEXTEST(A2, "^INV-[0-9]{6}$")=REGEXTEST(A2; "^INV-[0-9]{6}$")
3	INV-25041	Hofman Aannemers	€845.50€ 845,50	ONWAAR=REGEXTEST(A3, "^INV-[0-9]{6}$")=REGEXTEST(A3; "^INV-[0-9]{6}$")
4	INV-250413	Van Dijk Transport	€2,420.00€ 2.420,00	WAAR=REGEXTEST(A4, "^INV-[0-9]{6}$")=REGEXTEST(A4; "^INV-[0-9]{6}$")
5	INV-2504138	De Wit Metaal	€660.00€ 660,00	ONWAAR=REGEXTEST(A5, "^INV-[0-9]{6}$")=REGEXTEST(A5; "^INV-[0-9]{6}$")
6	INV 250414	Smit Groenvoorziening	€1,815.00€ 1.815,00	ONWAAR=REGEXTEST(A6, "^INV-[0-9]{6}$")=REGEXTEST(A6; "^INV-[0-9]{6}$")

Resultaat: WAAR

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

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

Wat REGEXTEST doet en wanneer je het gebruikt

REGEXTEST toetst een tekst aan een patroon en antwoordt WAAR of ONWAAR. Zo zie je of een referentie, code of omschrijving het schema volgt dat je beschrijft, bijvoorbeeld INV- gevolgd door precies zes cijfers.

REGEXTEST is een van de functies voor Microsoft 365 die een reguliere expressie lezen, samen met REGEXEXTRACT en REGEXREPLACE.

Waarvoor je de functie gebruikt

REGEXTEST is de juiste keuze als de vraag is of een tekst een schema volgt, iets wat jokertekens niet kunnen uitdrukken:

referentieschema's: factuurnummers, boekingssleutels, documentnummers
formaattoetsen op stamgegevens: IBAN's, btw-nummers, postcodes
afwijkingen in een export markeren, met FILTER of een hulpkolom

Jokertekens blijven genoeg als je alleen naar een vast woord zoekt: AANTAL.ALS met *correctie* is korter en werkt in elke Excel-versie. Let op: ? accepteert elk teken, dus INV-?????? laat ook letters door, terwijl [0-9]{6} alleen zes cijfers accepteert.

AANTAL.ALS met een jokerteken of REGEXTEST?

Vergelijk met AANTAL.ALS

AANTAL.ALS: lijkt de referentie op INV- en iets
```
=AANTAL.ALS(A2, "INV-??????")=1
```
```
=AANTAL.ALS(A2; "INV-??????")=1
```
REGEXTEST: volgt het precies het schema
```
=REGEXTEST(A2, "^INV-[0-9]{6}$")
```
```
=REGEXTEST(A2; "^INV-[0-9]{6}$")
```

Wat er verschilt tussen de twee

? accepteert elk teken, dus AANTAL.ALS laat ook INV-25041A door; [0-9]{6} accepteert zes cijfers en niets anders.
AANTAL.ALS negeert hoofdletters; REGEXTEST is hoofdlettergevoelig, tenzij je case_sensitivity op 1 zet.
AANTAL.ALS heeft de =1 nodig om van een aantal WAAR of ONWAAR te maken; REGEXTEST antwoordt zelf met WAAR of ONWAAR.
AANTAL.ALS werkt in elke Excel-versie; REGEXTEST heeft Microsoft 365 nodig.

Collega met Excel 2019 of ouder

Excel 2021, Excel 2024 en oudere versies hebben geen REGEXTEST en tonen #NAAM? zodra het bestand opnieuw rekent. Gaat het bestand naar die versies, toets het schema dan met AANTAL.ALS en een jokerteken, of met LINKS en LENGTE zoals het voorbeeld Expert laat zien.

Lees de pagina over AANTAL.ALS

Hoe Excel de functie berekent

Excel probeert het patroon op elke plek in de tekst te passen, vanaf het eerste teken:

een letterlijk teken zoals I moet precies dat teken zijn, dus het patroon INV- wordt overal gevonden waar de tekst INV- bevat
[0-9] accepteert één cijfer, [A-Z] één hoofdletter, en een getal tussen accolades zoals {6} geeft aan hoe vaak het onderdeel ervoor moet voorkomen
^ past alleen helemaal aan het begin van de tekst en $ alleen helemaal aan het eind

Zodra één plek past, geeft REGEXTEST WAAR. Past geen enkele plek, dan is het ONWAAR. Zonder ^ en $ mag het patroon op een deel van de tekst passen; met beide telt alleen de hele tekst.

Let op: Zonder ^ en $ is het antwoord WAAR zodra het patroon ergens in de tekst voorkomt, dus een te lange referentie komt toch door een toets op precies zes cijfers.

Denkmodel

Zie het patroon als een mal. Zonder ankers schuif je de mal langs de tekst en kijk je of hij ergens past; met ^ en $ zet je hem vast aan beide uiteinden.

Begrippen op deze pagina: reguliere expressie, jokerteken, celverwijzing

Syntaxis

Officiële documentatie van REGEXTEST

REGEXTEST(tekst, patroon, [case_sensitivity])

REGEXTEST(tekst; patroon; [case_sensitivity])

Argumenten
Argument	Soort	Betekenis	Standaard
`tekst`	Verplicht	De tekst of cel die je toetst.
`patroon`	Verplicht	De reguliere expressie waarnaar je zoekt.
`case_sensitivity`	Optioneel	0 = hoofdlettergevoelig (standaard), 1 = hoofdletters negeren.	`0`

Geeft terug:WAAR als een deel van de tekst bij het patroon past, anders ONWAAR.

Formules gebruiken komma's (,) tussen de argumenten, zoals in de Engelse Excel. Formules gebruiken puntkomma's (;) tussen de argumenten, zoals in de Nederlandse Excel. Formules gebruiken puntkomma's (;) tussen de argumenten, zoals in de Nederlandse Excel; de Engelse Excel gebruikt komma's (,).

Uitgewerkte voorbeelden

Voorbeeldwerkmap downloaden(Excelbestand, 5 werkbladen, 18 kB)

Beginner

Factuurnummers toetsen aan het INV-schema

Je test het verkoopdagboek van een cliënt wiens facturen nummers hebben van INV- gevolgd door precies zes cijfers. De export heeft een kolom met referenties en je wilt per rij WAAR of ONWAAR, zodat de afwijkingen opvallen.

Fragment van het werkblad: in D2:D6 staat de formule
Rij	A	B	C	D
1	Referentie	Klant	Bedrag	Volgt het schema
2	INV-250412	Bakker Installatie	€1,210.00€ 1.210,00	WAAR=REGEXTEST(A2, "^INV-[0-9]{6}$")=REGEXTEST(A2; "^INV-[0-9]{6}$")
3	INV-25041	Hofman Aannemers	€845.50€ 845,50	ONWAAR=REGEXTEST(A3, "^INV-[0-9]{6}$")=REGEXTEST(A3; "^INV-[0-9]{6}$")
4	INV-250413	Van Dijk Transport	€2,420.00€ 2.420,00	WAAR=REGEXTEST(A4, "^INV-[0-9]{6}$")=REGEXTEST(A4; "^INV-[0-9]{6}$")
5	INV-2504138	De Wit Metaal	€660.00€ 660,00	ONWAAR=REGEXTEST(A5, "^INV-[0-9]{6}$")=REGEXTEST(A5; "^INV-[0-9]{6}$")
6	INV 250414	Smit Groenvoorziening	€1,815.00€ 1.815,00	ONWAAR=REGEXTEST(A6, "^INV-[0-9]{6}$")=REGEXTEST(A6; "^INV-[0-9]{6}$")
7

Formulecel (D2:D6)

Formule in D2

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

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

Resultaat: WAAR

Stap voor stap

In A2 staat INV-250412 en het patroon is ^INV-[0-9]{6}$. Stuk voor stuk gelezen: ^ het begin van de tekst, INV- die vier tekens, [0-9]{6} precies zes cijfers, $ het eind van de tekst.
Excel begint bij het eerste teken. I, N, V en - passen op de eerste vier tekens. Daarna zijn zes cijfers nodig: 2, 5, 0, 4, 1, 2 zijn allemaal cijfers. $ past, want de tekst eindigt daar. Het hele patroon past, dus D2 toont WAAR.
Kopieer D2 naar beneden. In A3 staat INV-25041: na INV- volgen maar 5 cijfers en dan eindigt de tekst, dus het zesde cijfer ontbreekt. Omdat ^ alleen het begin toestaat, wordt geen andere startplek geprobeerd. Het antwoord is ONWAAR.
In A4 staat INV-250413, zes cijfers, dus WAAR. In A5 staat INV-2504138, zeven cijfers: het zesde cijfer past, maar $ eist het eind van de tekst en er staat nog een 8, dus ONWAAR.
In A6 staat INV 250414. Het patroon eist een koppelteken na INV en vindt een spatie, dus ONWAAR.
Haal ^ en $ weg en D5 verandert: INV-[0-9]{6} vindt INV-250413 in INV-2504138, dus de toets zegt WAAR voor een nummer dat te lang is.

Let op: Een referentie met een spatie aan het eind, zoals INV-250412 met een spatie erachter, valt af door $. Vult de export waarden aan met spaties, maak de kolom dan eerst schoon met SPATIES.WISSEN.

Meer over dit voorbeeld: waarom het werkt, nog 1 valkuil, 2 varianten

Waarom het werkt

REGEXTEST vraagt of het patroon ergens in de tekst past. De ankers maken daarvan de vraag of het in de hele tekst past, en {6} maakt het aantal cijfers exact. Dat laatste kunnen jokertekens niet: INV-?????? accepteert ook letters, omdat ? voor elk teken staat.

Meer om op te letten

Test een nieuw patroon met een waarde die één teken te lang is en een die één teken te kort is. Een patroon dat alleen juiste waarden ziet, bewijst niets.

Varianten

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

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

Eén formule in D2 geeft alle vijf de antwoorden en loopt naar beneden over; kies dit als je geen formule hoeft te kopiëren.

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

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

Draait het antwoord om, zodat de afwijkingen WAAR zijn; handig voor een filter op de kolom.

Halfgevorderd

IBAN's van leveranciers toetsen, hoofdletters negeren

In het leveranciersbestand controleer je of elk Nederlands IBAN het formaat NL, twee cijfers, vier letters en tien cijfers heeft. Sommige zijn in kleine letters getypt, en elke rij moet OK of Controleren tonen, zodat je de afwijkingen kunt nalopen.

Fragment van het werkblad: in C2:C7 staat de formule
Rij	A	B	C
1	Leverancier	IBAN	Formaat
2	Dekker Bouwmaterialen BV	NL12RABO0123456789	OK=ALS(REGEXTEST(B2, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Controleren")=ALS(REGEXTEST(B2; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Controleren")
3	Van Dijk Transport	nl45ingb0987654321	OK=ALS(REGEXTEST(B3, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Controleren")=ALS(REGEXTEST(B3; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Controleren")
4	Hoekstra Installatie	NL67INGB098765432	Controleren=ALS(REGEXTEST(B4, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Controleren")=ALS(REGEXTEST(B4; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Controleren")
5	Smit Groenvoorziening	NL33 ABNA 0123 4567 89	Controleren=ALS(REGEXTEST(B5, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Controleren")=ALS(REGEXTEST(B5; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Controleren")
6	De Wit Metaal	DE89370400440532013000	Controleren=ALS(REGEXTEST(B6, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Controleren")=ALS(REGEXTEST(B6; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Controleren")
7	Yilmaz Catering	NL58ABNA0612345678	OK=ALS(REGEXTEST(B7, "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$", 1), "OK", "Controleren")=ALS(REGEXTEST(B7; "^NL[0-9]{2}[A-Z]{4}[0-9]{10}$"; 1); "OK"; "Controleren")
8

Formulecel (C2:C7)Opmerking: B5 bevat het IBAN zoals het op een factuur staat, in groepen van vier met spaties.

Formule in C2

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

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

Resultaat: OK

Stap voor stap

Van binnen naar buiten: REGEXTEST leest B2, NL12RABO0123456789, en het patroon. ^NL is het begin en de letters NL, [0-9]{2} twee cijfers, [A-Z]{4} vier hoofdletters, [0-9]{10} tien cijfers, $ het eind. Het derde argument 1 zegt dat Excel hoofdletters mag negeren.
NL past, 12 past, RABO past, 0123456789 is 10 cijfers en past, en de tekst eindigt. REGEXTEST geeft WAAR, dus ALS geeft OK in C2.
In B3 staat nl45ingb0987654321, helemaal in kleine letters. Omdat hoofdletters genegeerd worden, past nl op NL en ingb op [A-Z]{4}, dus WAAR en OK. Zonder het derde argument past de kleine n niet op N en krijgt een correct IBAN toch Controleren.
In B4 staat NL67INGB098765432: na INGB komen 9 cijfers (098765432) en het patroon eist er 10, dus ONWAAR en Controleren.
In B5 staat NL33 ABNA 0123 4567 89. Na NL33 wil het patroon een letter en het vindt een spatie, dus ONWAAR. B6 begint met DE, en ^NL faalt meteen. Beide krijgen Controleren.
In B7 staat NL58ABNA0612345678: NL, 02, ABNA, 10 cijfers, eind. WAAR en OK.

Let op: Spaties laten de toets mislukken, zoals in B5. Het IBAN kan juist zijn, maar het formaat is niet dat van het patroon. Haal eerst de spaties weg, zoals de eerste variant doet.

Meer over dit voorbeeld: waarom het werkt, nog 1 valkuil, 2 varianten

Waarom het werkt

Het derde argument verandert alleen hoe letters worden vergeleken; de cijfers en de lengtes blijven streng gecontroleerd. ALS maakt van WAAR of ONWAAR een woord waarop een beoordelaar kan filteren. De toets controleert alleen de vorm: de controlecijfers worden niet berekend, dus een IBAN met de juiste vorm en een verkeerd getypt cijfer komt er toch door.

Meer om op te letten

Hoofdletters negeren is hier de juiste keuze, maar het is niet neutraal. Moet het systeem van de cliënt hoofdletters bevatten, dan is een IBAN in kleine letters een bevinding, en met 1 zie je die niet. Gebruik daar de standaard.

Varianten

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

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

Haalt eerst de spaties weg, zodat de gedrukte vorm in B5 NL33ABNA0123456789 wordt en doorkomt.

=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) aan het begin van het patroon doet hetzelfde als het derde argument. Kies dit als het patroon in een cel staat en zijn eigen hoofdletterregel moet meenemen.

Gevorderd

Boekingssleutels tonen die het schema overtreden

Een auditfile-export bevat per boeking een sleutel: dagboek, boekstuknummer, relatie en btw-tarief, gescheiden door |, zoals in VRK|25-0388|D1002|21%. Je wilt alleen de regels waarvan de sleutel het schema overtreedt, met hun bedrag, als één lijst voor je werkpapier.

Fragment van het werkblad: in D2 staat de formule
Rij	A	B	D	E
1	Boekingssleutel	Bedrag	Sleutel	Bedrag
2	VRK\|25-0388\|D1002\|21%	€12,100.00€ 12.100,00	vrk\|25-0392\|D1005\|21%=FILTER(A2:B9, NIET(REGEXTEST(A2:A9, "^(VRK\|INK\|BNK\|MEM)\\|[0-9]{2}-[0-9]{4}\\|[DC][0-9]{4}\\|(0\|9\|21)%$")), "Geen afwijkingen")=FILTER(A2:B9; NIET(REGEXTEST(A2:A9; "^(VRK\|INK\|BNK\|MEM)\\|[0-9]{2}-[0-9]{4}\\|[DC][0-9]{4}\\|(0\|9\|21)%$")); "Geen afwijkingen")	€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

Formulecel (D2)OverloopresultaatTekens: extra spatieOpmerking: Het schema: dagboek VRK, INK, BNK of MEM; boekstuk als twee cijfers, een koppelteken en vier cijfers; relatie D of C en vier cijfers; btw 0%, 9% of 21%. De sleutel in A9 eindigt met een spatie.

Formule in D2

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

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

Resultaat: 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

Stap voor stap

Van binnen naar buiten: REGEXTEST krijgt het hele bereik A2:A9, toetst dus alle 8 sleutels in één keer en geeft 8 antwoorden. Het patroon leest zo: ^ begin; (VRK|INK|BNK|MEM) een van de vier dagboeken (tussen de haakjes betekent de gewone | "of"); \| een echt verticaal streepje |; [0-9]{2}-[0-9]{4} jaar en boekstuknummer; \|; [DC][0-9]{4} relatie D of C plus vier cijfers; \|; (0|9|21)% een btw-tarief van 0, 9 of 21 procent; $ eind.
Rij voor rij: A2, A3, A5 en A6 passen op het hele patroon. A4 begint met de kleine letters vrk, die op geen van de vier dagboeken passen omdat toetsen hoofdlettergevoelig is. A7 heeft 03975: na het koppelteken wil het patroon vier cijfers en dan \|, maar er komt een vijfde cijfer.
A8 heeft een btw-tarief van 19%, en geen van 0, 9 of 21 past. A9 past helemaal tot 21%, maar daarna volgt een spatie en $ wil het eind van de tekst. REGEXTEST geeft WAAR, WAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR, ONWAAR.
NIET draait elk antwoord om: ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, WAAR, WAAR. WAAR markeert nu een afwijking.
FILTER houdt de rijen van A2:B9 over waar het argument opnemen WAAR is: werkbladrijen 4, 7, 8 en 9. Het resultaat is 4 rijen bij 2 kolommen en loopt over in D2:E5: vrk|25-0392|D1005|21% met €890.00€ 890,00, VRK|25-03975|D1004|9% met €560.00€ 560,00, INK|25-0401|C2003|19% met €2,400.00€ 2.400,00 en VRK|25-0402|D1003|21% met €7,800.00€ 7.800,00.
Komt elke sleutel door de toets, dan zijn alle antwoorden na NIET ONWAAR, blijft er niets over en toont de cel Geen afwijkingen.

Let op: Stille fout: laat je de backslashes in \| weg, dan wordt elke gewone | "of". Het patroon zegt dan WAAR zodra één stukje gevonden is, dus alle 8 sleutels komen door de toets en de cel toont Geen afwijkingen, zonder foutmelding.

Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 2 varianten

Waarom het werkt

REGEXTEST werkt op een heel bereik tegelijk en geeft één antwoord per cel, precies de vorm die FILTER nodig heeft voor het argument opnemen. NIET maakt van "volgt het schema" het tegenovergestelde "overtreedt het schema", zodat FILTER de bevindingen toont.

Meer om op te letten

De sleutel in A9 ziet er op het scherm goed uit. Alleen de spatie na % maakt er een afwijking van, en $ vangt die op. Laat $ staan en bepaal dan of zo'n spatie een bevinding is of iets om met SPATIES.WISSEN schoon te maken.
Het bereik met de sleutels en het gefilterde bereik moeten even hoog zijn, hier A2:A9 en A2:B9. Een bereik van één rij meer geeft #WAARDE!.

Varianten

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

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

Telt de afwijkingen in plaats van ze te tonen: 4. Gebruik dit voor het totaal op je werkpapier.

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

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

Met hoofdletters genegeerd komt vrk|25-0392|D1005|21% door de toets en blijven 3 rijen over. Gebruik dit alleen als hoofdletters geen deel van het schema zijn.

Expert

Lijst btw-nummers toetsen en de afwijkingen noemen

Voordat je de btw-nummers van zeven klanten met het officiële register vergelijkt, toets je het formaat: NL, negen cijfers, B en twee cijfers. Eén cel meldt hoeveel er afvallen en noemt ze, met een patroon dat in een cel staat en dat collega's kunnen lezen en aanpassen.

Fragment van het werkblad: in E2 staat de formule
Rij	A	B	D	E
1	Klant	Btw-nummer	Patroon	^NL[0-9]{9}B[0-9]{2}$
2	Postma Installatie BV	NL123456789B01	Resultaat	3 te controleren: NL345678901B1, NL 456789012 B01, BE0123456789=LET( btwnr, B2:B8, patroon, E1, geldig, REGEXTEST(btwnr, patroon, 1), afwijkend, SOMPRODUCT(--NIET(geldig)), ALS(afwijkend = 0, "Alles geldig", afwijkend & " te controleren: " & TEKST.COMBINEREN(", ", WAAR, FILTER(btwnr, NIET(geldig)))) )=LET( btwnr; B2:B8; patroon; E1; geldig; REGEXTEST(btwnr; patroon; 1); afwijkend; SOMPRODUCT(--NIET(geldig)); ALS(afwijkend = 0; "Alles geldig"; afwijkend & " te controleren: " & TEKST.COMBINEREN(", "; WAAR; FILTER(btwnr; NIET(geldig)))) )
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

Formulecel (E2)Invoer

Formule in E2

=LET(
  btwnr, B2:B8,
  patroon, E1,
  geldig, REGEXTEST(btwnr, patroon, 1),
  afwijkend, SOMPRODUCT(--NIET(geldig)),
  ALS(afwijkend = 0, "Alles geldig", afwijkend & " te controleren: " & TEKST.COMBINEREN(", ", WAAR, FILTER(btwnr, NIET(geldig))))
)

=LET(
  btwnr; B2:B8;
  patroon; E1;
  geldig; REGEXTEST(btwnr; patroon; 1);
  afwijkend; SOMPRODUCT(--NIET(geldig));
  ALS(afwijkend = 0; "Alles geldig"; afwijkend & " te controleren: " & TEKST.COMBINEREN(", "; WAAR; FILTER(btwnr; NIET(geldig))))
)

Resultaat: 3 te controleren: NL345678901B1, NL 456789012 B01, BE0123456789

Stap voor stap

LET koppelt de namen in volgorde. btwnr wordt de 7 cellen van B2:B8, en patroon wordt de tekst in E1, ^NL[0-9]{9}B[0-9]{2}$. Er is nog niets getoetst.
geldig is REGEXTEST over alle 7 cellen met case_sensitivity 1. B2 past. B3, nl234567890b01, past omdat kleine letters als NL en B tellen. B4, NL345678901B1, heeft na de B één cijfer waar er twee nodig zijn. B5 heeft spaties. B6 past. B7, BE0123456789, begint met BE. B8 past. De matrix is WAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR.
afwijkend: NIET maakt van de matrix ONWAAR, ONWAAR, WAAR, WAAR, ONWAAR, WAAR, ONWAAR. De dubbele min maakt er 0, 0, 1, 1, 0, 1, 0 van en SOMPRODUCT telt ze op: 3.
Het laatste argument van LET is de ALS. afwijkend = 0 is ONWAAR, dus ALS neemt de tweede tak.
FILTER houdt de cellen van btwnr over waar NIET(geldig) WAAR is: NL345678901B1, NL 456789012 B01 en BE0123456789. TEKST.COMBINEREN zet , ertussen en de &-tekens zetten 3 en de tekst te controleren: ervoor. Het resultaat is 3 te controleren: NL345678901B1, NL 456789012 B01, BE0123456789.
Verbeter B4 naar NL345678901B01 en het aantal zakt naar 2. Verbeter alle drie en afwijkend is 0, zodat de cel Alles geldig toont.

Let op: Het spiegelbeeld van de hoofdletterfout: case_sensitivity 1 lijkt veilig maar verbergt nl234567890b01. Moet het systeem van de cliënt hoofdletters bewaren, dan is die rij een bevinding. Met de standaard 0 is het aantal 4 in plaats van 3.

Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 2 varianten

Waarom het werkt

LET geeft het resultaat van REGEXTEST één keer een naam, zodat het aantal en de lijst dezelfde antwoorden gebruiken, en het patroon in E1 is zichtbaar voor een beoordelaar en heeft geen verdubbelde aanhalingstekens nodig. Gebruik deze constructie niet als de regel alleen een begin en een lengte is: EN(LINKS(B2; 2) = "NL"; LENGTE(B2) = 14) is korter te lezen en werkt in elke Excel-versie, ten koste van de controle of het middenstuk uit cijfers bestaat. Geen van beide laat zien of het nummer bestaat; daarvoor heb je het register nodig.

Meer om op te letten

Een patroon in een cel wordt gelezen zoals het getypt is. Een spatie aan het eind van E1 wordt deel van het patroon en elk nummer valt af. Vallen alle rijen tegelijk af, controleer dan eerst de patroon-cel.
Doorkomen bewijst de vorm, niet het nummer. NL999999999B99 komt door de toets en bestaat misschien niet.

Varianten

=EN(LINKS(B2, 2) = "NL", LENGTE(B2) = 14)

=EN(LINKS(B2; 2) = "NL"; LENGTE(B2) = 14)

Het alternatief voor oudere Excel-versies, voor één nummer: een begin en een lengte. Op deze gegevens geeft het dezelfde antwoorden als het patroon, maar NL12345678XB01 komt er ook door.

=X.ZOEKEN(ONWAAR, REGEXTEST(B2:B8, E1, 1), B2:B8, "Alles geldig")

=X.ZOEKEN(ONWAAR; REGEXTEST(B2:B8; E1; 1); B2:B8; "Alles geldig")

Geeft alleen het eerste nummer dat afvalt: NL345678901B1. Voeg -1 toe als zesde argument van X.ZOEKEN (laat het vijfde leeg) voor het laatste.

Veelgemaakte fouten

Veelgemaakte fouten
Symptoom	Oorzaak	Oplossing
WAAR voor een referentie die te lang is	Het patroon heeft geen `^` en `$`, dus een treffer ergens in de tekst is genoeg.	Schrijf `^INV-[0-9]{6}$` om de hele cel te toetsen. Test elk nieuw patroon met een waarde die één teken te lang is en een die één teken te kort is.
ONWAAR voor inv-250412, dat er goed uitziet	Toetsen is standaard hoofdlettergevoelig, dus een kleine `inv` past niet op `INV`.	Zet het derde argument op 1, of laat het patroon beginnen met `(?i)`. Laat de standaard staan als het schema zelf hoofdletters eist.
WAAR voor bijna elke tekst	Een `\|` in een patroon betekent "of", niet het teken zelf. In `VRK\|25-0388` leest Excel: `VRK` of `25-0388`.	Schrijf `\\|` voor het teken zelf. Binnen een groep zoals `(VRK\|INK)` is de gewone `\|` juist.
WAAR voor een datum met het verkeerde scheidingsteken	Een `.` in een patroon accepteert elk teken, dus `^[0-9]{2}.[0-9]{2}$` laat `25-04` en `25x04` door.	Schrijf `\.` voor een echte punt. Een koppelteken buiten blokhaken heeft geen backslash nodig.
#NAAM? in Excel 2021 of 2024	REGEXTEST bestaat alleen in Microsoft 365. Oudere versies kennen de functie niet.	Gaat het bestand naar Excel 2021 of 2024, bouw de toets dan op uit LINKS en LENGTE, zoals het alternatief in het voorbeeld Expert laat zien.

Gebruikt in combinaties

De klant opzoeken met een regexHaal het factuurnummer met REGEXEXTRACT uit een bankomschrijving, maak er een patroon van en laat X.ZOEKEN dat patroon vergelijken met een factuurlijst waarin nummers op verschillende manieren zijn getypt.Excel voor Microsoft 365 (Deze combinatie vereist Excel voor Microsoft 365)