Combinatie

De klant opzoeken met een regex

Haal 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.

Gebruikte functies:X.ZOEKEN REGEXEXTRACT REGEXTESTExcel voor Microsoft 365 (Deze combinatie vereist Excel voor Microsoft 365)

Moeilijkheidsgraad

Uitgewerkt voorbeeld

De klant achter een bankontvangst met een rommelige referentie

Situatie

Bij de debiteurenwerkzaamheden bij Veldhuis Installatie BV koppel je bankontvangsten aan de debiteurenlijst. Klanten schrijven het factuurnummer op hun eigen manier in de omschrijving (INV-250412 of inv 25-0412) en ook in de lijst van de cliënt staan de nummers in verschillende notaties: INV-25-0412, inv 25-0415, INV-250420. Exact opzoeken op een opgeschoonde sleutel vraagt eerst veel opschoonwerk. Je wilt de klant naast de ontvangst zien in één formule.

De gegevens

Fragment van het werkblad: in F3 staat de formule
Rij	A	B	C	E	F
1	Factuurnr.	Klant	Openstaand bedrag	Banktekst	SEPA OVERBOEKING inv 25-0420 BETALING DEBITEUR
2	INV-25-0412	Mulder Bouw BV	€12,100.00€ 12.100,00
3	inv 25-0415	De Vries Transport	€8,470.00€ 8.470,00	Klant	Jansen Installatie=ALS(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(sleutel, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel, 2)&"-?"&RECHTS(sleutel, 4)&"$", A2:A7, B2:B7, "Niet in debiteuren", 3)), "Geen factuurnummer in tekst")=ALS(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(sleutel; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel; 2)&"-?"&RECHTS(sleutel; 4)&"$"; A2:A7; B2:B7; "Niet in debiteuren"; 3)); "Geen factuurnummer in tekst")
4	INV-250420	Jansen Installatie	€3,025.00€ 3.025,00
5	INV-25-0433	Bakker Groothandel	€15,730.00€ 15.730,00
6	INV 25-0441	Smit Verhuur	€6,050.00€ 6.050,00
7	INV-25-0447	Van Dijk Schilderwerk	€1,210.00€ 1.210,00
8

Formulecel (F3)Invoer

Formule in F3

=ALS(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(sleutel, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel, 2)&"-?"&RECHTS(sleutel, 4)&"$", A2:A7, B2:B7, "Niet in debiteuren", 3)), "Geen factuurnummer in tekst")

=ALS(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(sleutel; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel; 2)&"-?"&RECHTS(sleutel; 4)&"$"; A2:A7; B2:B7; "Niet in debiteuren"; 3)); "Geen factuurnummer in tekst")

Resultaat: Jansen Installatie

Hoe de onderdelen samenwerken

Let op: Overeenkomstmodus 3 leest de zoekwaarde als patroon en let op hoofdletters. Begin het patroon dus met (?i) en sluit het af met $, anders vindt X.ZOEKEN niets of de verkeerde factuur.

De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.

```
=REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}")
```
```
=REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}")
```
Toetst of de omschrijving wel een factuurnummer bevat: inv, dan een optioneel streepje of een spatie, twee cijfers, een optioneel streepje en vier cijfers. REGEXTEST zoekt het patroon ergens in de tekst en (?i) negeert hoofdletters. De omschrijving SEPA OVERBOEKING inv 25-0420 BETALING DEBITEUR bevat inv 25-0420, dus het resultaat is WAAR.
```
=REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2)
```
```
=REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2)
```
Met return_mode 2 krijg je de groep tussen haakjes terug en vallen inv en de spatie weg. Het resultaat is de tekst 25-0420. Had de bank INV-250420 geschreven, dan was de groep 250420: het streepje in het midden is optioneel, dus beide notaties worden geaccepteerd.
```
="(?i)^inv[- ]?"&LINKS(REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), 2)&"-?"&RECHTS(REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), 4)&"$"
```
```
="(?i)^inv[- ]?"&LINKS(REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); 2)&"-?"&RECHTS(REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); 4)&"$"
```
Bouwt het patroon op uit het uitgeknipte nummer. LINKS neemt de eerste twee tekens (25), RECHTS de laatste vier (0420), zodat het streepje of het ontbreken daarvan in de banktekst niet meer uitmaakt. Het resultaat is de tekst (?i)^inv[- ]?25-?0420$: vanaf het begin van de cel inv, een optioneel streepje of een spatie, 25, een optioneel streepje, 0420 en dan het einde van de cel.
```
=REGEXTEST(A2:A7, "(?i)^inv[- ]?25-?0420$")
```
```
=REGEXTEST(A2:A7; "(?i)^inv[- ]?25-?0420$")
```
Past dat patroon toe op de zes factuurnummers in A2:A7. Het resultaat is één kolom: ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, ONWAAR. Alleen INV-250420 in de derde rij past: INV, een streepje, 25, geen streepje, 0420. De andere vijf eindigen op een ander getal van vier cijfers.
```
=ALS(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(sleutel, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel, 2)&"-?"&RECHTS(sleutel, 4)&"$", A2:A7, B2:B7, "Niet in debiteuren", 3)), "Geen factuurnummer in tekst")
```
```
=ALS(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(sleutel; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); X.ZOEKEN("(?i)^inv[- ]?"&LINKS(sleutel; 2)&"-?"&RECHTS(sleutel; 4)&"$"; A2:A7; B2:B7; "Niet in debiteuren"; 3)); "Geen factuurnummer in tekst")
```
De hele formule. ALS controleert eerst of de omschrijving een factuurnummer bevat. LET knipt het één keer uit en geeft het de naam sleutel. X.ZOEKEN gebruikt met overeenkomstmodus 3 het patroon als zoekwaarde, vindt de overeenkomst in rij 3 van A2:A7 en geeft Jansen Installatie uit B2:B7 terug. Een omschrijving zonder nummer (bijvoorbeeld een salarisbetaling) toont de tweede melding in plaats van #N/B.

Stap voor stap

REGEXTEST(F1; ...) zoekt ergens in SEPA OVERBOEKING inv 25-0420 BETALING DEBITEUR naar inv, een optioneel streepje of een spatie, twee cijfers, een optioneel streepje en vier cijfers. Het vindt inv 25-0420, dus de toets is WAAR en ALS gaat verder met het opzoeken.
REGEXEXTRACT(F1; ...; 2) geeft de groep tussen haakjes van die eerste overeenkomst terug: 25-0420. LET bewaart die als sleutel.
LINKS(sleutel; 2) = 25 en RECHTS(sleutel; 4) = 0420. Samengevoegd met de vaste delen wordt het patroon (?i)^inv[- ]?25-?0420$.
X.ZOEKEN met overeenkomstmodus 3 toetst elke cel van A2:A7 van boven af aan dat patroon. INV-25-0412: na 25- volgt 0412, niet 0420, geen overeenkomst. inv 25-0415: 0415, geen overeenkomst.
INV-250420, de derde cel: INV komt overeen met inv dankzij (?i), het streepje past bij [- ]?, 25 klopt, het optionele streepje ontbreekt, 0420 klopt en $ vindt het einde van de cel. Een overeenkomst op plek 3.
Plek 3 van B2:B7 (Mulder Bouw BV, De Vries Transport, Jansen Installatie) is Jansen Installatie, het resultaat. Het openstaande bedrag in dezelfde rij is 3,0253.025.

Waarom het werkt

Een reguliere expressie beschrijft de vorm van een factuurnummer in plaats van één exacte tekst, dus INV-250420 in de lijst en inv 25-0420 in de banktekst worden herkend als dezelfde factuur. REGEXEXTRACT haalt de cijfers eruit, LINKS en RECHTS bouwen er een patroon van met optionele scheidingstekens, en X.ZOEKEN in overeenkomstmodus 3 gebruikt dat patroon in plaats van een exacte waarde. De ankers ^ en $ zorgen dat de hele cel moet passen, niet alleen een stukje ervan. Dit zijn Microsoft 365-functies.

Let op

Overeenkomstmodus 3 let op hoofdletters. Zonder (?i) komt het patroon ^inv[- ]?25-?0420$ met geen van de zes cellen overeen, want het patroon heeft een kleine inv terwijl de cel met het juiste nummer met hoofdletters INV begint, en je ziet de tekst voor niet gevonden. Begin het patroon met (?i).
Laat je ^ en $ weg, dan past het patroon op elke plek in een cel. Een korter patroon als 25-?04 vindt INV-25-0412 in de eerste rij eerder dan INV-250420, en X.ZOEKEN geeft de eerste treffer: de verkeerde klant. Houd beide ankers in het patroon dat je opbouwt.
Overeenkomstmodus 3 en de REGEX-functies bestaan alleen in Microsoft 365. In Excel 2021 of 2024 geeft de formule #WAARDE!. Een omschrijving zonder factuurnummer laat REGEXEXTRACT #N/B geven, en daarom staat de ALS met REGEXTEST vooraan.

Varianten

=LET(sleutel, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), X.ZOEKEN(WAAR, REGEXTEST(A2:A7, "^inv[- ]?"&LINKS(sleutel, 2)&"-?"&RECHTS(sleutel, 4)&"$", 1), B2:B7, "Niet in debiteuren"))

=LET(sleutel; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); X.ZOEKEN(WAAR; REGEXTEST(A2:A7; "^inv[- ]?"&LINKS(sleutel; 2)&"-?"&RECHTS(sleutel; 4)&"$"; 1); B2:B7; "Niet in debiteuren"))

Hetzelfde opzoeken zonder overeenkomstmodus 3: REGEXTEST toetst elke cel en geeft ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, ONWAAR, en X.ZOEKEN zoekt de eerste WAAR (plek 3). Hoofdletters regel je met het laatste argument 1 in plaats van (?i). Gebruik dit als je de toets ook met andere voorwaarden wilt combineren. Het vraagt nog steeds Microsoft 365, omdat REGEXTEST dat doet.

=LET(sleutel, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), FILTER(A2:C7, REGEXTEST(A2:A7, "^inv[- ]?"&LINKS(sleutel, 2)&"-?04[2-4]\d$", 1), "Niet in debiteuren"))

=LET(sleutel; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); FILTER(A2:C7; REGEXTEST(A2:A7; "^inv[- ]?"&LINKS(sleutel; 2)&"-?04[2-4]\d$"; 1); "Niet in debiteuren"))

Geeft alle overeenkomsten terug in plaats van de eerste: het patroon ^inv[- ]?25-?04[2-4]\d$ staat voor de reeks 25-0420 tot en met 25-0449. Vier cellen passen (INV-250420, INV-25-0433, INV 25-0441, INV-25-0447), dus FILTER geeft vier rijen terug met factuur, klant en openstaand bedrag. Rij 1 en 2 (0412, 0415) vallen buiten de reeks.

Alle combinaties