Beschikbaar in:Excel voor Microsoft 365Categorie:Tekst

REGEXREPLACE

REGEXREPLACE in Engelstalige Excel

Vervang de delen van een tekst die aan een patroon voldoen en gebruik stukken van de treffer in de nieuwe tekst.

Moeilijkheidsgraad

Laatst bijgewerkt: 23 oktober 2026

Fragment van het werkblad: in C2:C5 staat de formule
Rij	A	B	C
1	Leverancier	Btw-nummer zoals getypt	Opgeschoond
2	Hoogland Staalbouw BV	NL 8123.45.678.B01	NL812345678B01=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "")
3	Rijnmond Software BV	NL-8567-89-012-B01	NL856789012B01=REGEXREPLACE(B3, "[^A-Za-z0-9]", "")=REGEXREPLACE(B3; "[^A-Za-z0-9]"; "")
4	Delta Verpakking BV	NL 8456 78901 B02	NL845678901B02=REGEXREPLACE(B4, "[^A-Za-z0-9]", "")=REGEXREPLACE(B4; "[^A-Za-z0-9]"; "")
5	Kuiper Logistiek BV	nl.8234.56.789.b01	nl823456789b01=REGEXREPLACE(B5, "[^A-Za-z0-9]", "")=REGEXREPLACE(B5; "[^A-Za-z0-9]"; "")

Resultaat: NL812345678B01

=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")

=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "")

Wat REGEXREPLACE doet en wanneer je het gebruikt

REGEXREPLACE, REGEXTEST en REGEXEXTRACT zijn functies van Microsoft 365 die tekst lezen via een reguliere expressie: een patroon dat tekst beschrijft aan de hand van de vorm, zoals twee cijfers, een streepje en vier cijfers.

REGEXREPLACE vervangt elk deel van een tekst dat aan het patroon voldoet door nieuwe tekst. In die nieuwe tekst halen $1 en $2 de delen terug die het patroon tussen haakjes heeft gezet, zodat je een verwijzing kunt herordenen en opnieuw opmaken, en er niet alleen iets uit weghaalt.

Waarvoor je de functie gebruikt

REGEXREPLACE past bij het normaliseren van verwijzingen voordat je ze vergelijkt of opzoekt: spaties en leestekens uit btw-nummers halen, een postcode in één vorm zetten, van een journaalsleutel een boekstuknummer maken. Ook tekstexports repareer je ermee, zoals datums die andersom staan. Staat de te vervangen tekst vast, bijvoorbeeld één streepje, dan doet SUBSTITUEREN hetzelfde in elke Excel-versie en leest de formule overzichtelijker.

SUBSTITUEREN of REGEXREPLACE?

Vergelijk met SUBSTITUEREN

SUBSTITUEREN: één vast teken per aanroep
```
=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2, " ", ""), ".", ""), "-", "")
```
```
=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2; " "; ""); "."; ""); "-"; "")
```
REGEXREPLACE: één patroon voor alles
```
=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")
```
```
=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "")
```

Wat er verschilt tussen de twee

SUBSTITUEREN heeft per teken (spatie, punt, streepje) een eigen geneste aanroep nodig en mist het volgende teken dat je tegenkomt, zoals een schuine streep.
REGEXREPLACE zegt wat je bewaart in plaats van wat je weghaalt, dus een nieuw scheidingsteken vraagt geen nieuwe aanroep.
SUBSTITUEREN kan delen van een tekst niet van volgorde veranderen; REGEXREPLACE kan dat met $1 en $2.
SUBSTITUEREN werkt in elke Excel-versie; REGEXREPLACE vraagt Microsoft 365.

Collega met Excel 2019 of ouder

Excel 2021, Excel 2024 en oudere versies hebben geen REGEXREPLACE en tonen #NAAM? zodra het bestand opnieuw rekent. Gaat het bestand naar die versies, houd dan SUBSTITUEREN aan voor vaste teksten.

Lees de pagina over SUBSTITUEREN

Hoe Excel de functie berekent

Excel leest de tekst en het patroon, loopt de tekst van links naar rechts langs en markeert elk stuk dat aan het patroon voldoet. Een treffer eindigt voordat het volgende zoeken begint, dus treffers overlappen nooit. Elk gemarkeerd stuk wordt vervangen door de nieuwe tekst; daarin is $1 de tekst die het eerste paar haakjes in het patroon heeft vastgelegd, $2 het tweede paar, enzovoort. Tekst buiten de treffers blijft staan. Bij exemplaar 0 (standaard) wordt elke treffer vervangen; een positief getal vervangt alleen die treffer geteld van links, een negatief getal alleen die treffer geteld van rechts. Past er niets, dan komt de tekst ongewijzigd terug, en het resultaat is altijd tekst.

Let op: REGEXREPLACE meldt niet dat een patroon niets vindt: de tekst komt ongewijzigd terug. Ziet het resultaat er hetzelfde uit als de invoer, toets het patroon dan met REGEXTEST.

Begrippen op deze pagina: reguliere expressie, celverwijzing

Syntaxis

Officiële documentatie van REGEXREPLACE

REGEXREPLACE(tekst, patroon, vervanging, [exemplaar], [case_sensitivity])

REGEXREPLACE(tekst; patroon; vervanging; [exemplaar]; [case_sensitivity])

Argumenten
Argument	Soort	Betekenis	Standaard
`tekst`	Verplicht	De tekst waarin je zoekt.
`patroon`	Verplicht	De reguliere expressie die aangeeft wat je vervangt.
`vervanging`	Verplicht	De nieuwe tekst; $1, $2 voegen delen van de treffer in.
`exemplaar`	Optioneel	Welke treffer: 0 = alle (standaard), n = de n-de, negatief telt vanaf het eind.	`0`
`case_sensitivity`	Optioneel	0 = hoofdlettergevoelig (standaard), 1 = hoofdletters negeren.	`0`

Geeft terug:De tekst met de treffers vervangen, of de oorspronkelijke tekst als er niets past.

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

Spaties en punten uit btw-nummers halen

In het crediteurenbestand van de cliënt staan btw-nummers op vier verschillende manieren getypt. Voordat je ze vergelijkt met de nummers op de facturen, wil je ze als één aaneengesloten reeks letters en cijfers hebben.

Fragment van het werkblad: in C2:C5 staat de formule
Rij	A	B	C
1	Leverancier	Btw-nummer zoals getypt	Opgeschoond
2	Hoogland Staalbouw BV	NL 8123.45.678.B01	NL812345678B01=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "")
3	Rijnmond Software BV	NL-8567-89-012-B01	NL856789012B01=REGEXREPLACE(B3, "[^A-Za-z0-9]", "")=REGEXREPLACE(B3; "[^A-Za-z0-9]"; "")
4	Delta Verpakking BV	NL 8456 78901 B02	NL845678901B02=REGEXREPLACE(B4, "[^A-Za-z0-9]", "")=REGEXREPLACE(B4; "[^A-Za-z0-9]"; "")
5	Kuiper Logistiek BV	nl.8234.56.789.b01	nl823456789b01=REGEXREPLACE(B5, "[^A-Za-z0-9]", "")=REGEXREPLACE(B5; "[^A-Za-z0-9]"; "")
6

Formulecel (C2:C5)

Formule in C2

=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")

=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "")

Resultaat: NL812345678B01

Stap voor stap

In B2 staat NL 8123.45.678.B01, 18 tekens. Het patroon [^A-Za-z0-9] is een tekenklasse: de ^ direct na de openende blokhaak betekent “elk teken dat niet in deze lijst staat”, en de lijst bestaat uit alle letters en alle cijfers.
Excel loopt de tekst van links naar rechts langs. N, L en elk cijfer staan in de lijst en passen dus niet. De spatie na NL en de drie punten staan niet in de lijst: vier treffers.
De vervanging is een lege tekst (""), dus alle vier de treffers worden verwijderd. Het exemplaar is weggelaten, wat 0 betekent: elke treffer, niet alleen de eerste.
18 tekens min 4 verwijderde = 14 tekens: C2 toont NL812345678B01.
Kopieer C2 naar beneden. NL-8567-89-012-B01 verliest 4 streepjes en wordt NL856789012B01; NL 8456 78901 B02 verliest 3 spaties en wordt NL845678901B02; nl.8234.56.789.b01 verliest 4 punten en wordt nl823456789b01, nog steeds in kleine letters.

Let op: De letters behouden hun hoofdletters of kleine letters: nl823456789b01 is niet gelijk aan NL823456789B01 voor een hoofdlettergevoelig patroon zoals ^NL in REGEXTEST. Een vergelijking met =, X.ZOEKEN of SOMMEN.ALS negeert hoofdletters, dus daar geeft het geen problemen.

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

Waarom het werkt

Een omgekeerde tekenklasse noemt wat je bewaart, zodat je niet elk scheidingsteken hoeft op te sommen dat je kunt tegenkomen. De lege vervanging maakt van elke treffer niets, en het standaardexemplaar laat dat voor alle treffers gelden.

Meer om op te letten

Gebruik dit patroon niet op namen: een letter zoals é staat niet in A-Z en wordt dus samen met de spaties verwijderd.

Varianten

=REGEXREPLACE(B2, "[ .-]", "")

=REGEXREPLACE(B2; "[ .-]"; "")

Noem de scheidingstekens die je verwacht (spatie, punt, streepje) in plaats van alles wat overblijft. Een teken dat je niet verwachtte blijft dan zichtbaar in het resultaat.

=HOOFDLETTERS(REGEXREPLACE(B2, "[^A-Za-z0-9]", ""))

=HOOFDLETTERS(REGEXREPLACE(B2; "[^A-Za-z0-9]"; ""))

Zet HOOFDLETTERS eromheen als de volgende stap hoofdlettergevoelig vergelijkt. De vierde leverancier geeft dan NL823456789B01.

Halfgevorderd

Postcodes in één vorm zetten

Je verstuurt adresbevestigingen en legt de klantenlijst van de cliënt naast een postcodetabel. In de lijst staan postcodes als 3011ab, 2406 be en 2801 mm , en één invoer die helemaal geen postcode is. Je wilt vier cijfers, één spatie en twee letters, en de afwijkende invoer laat je staan om na te lopen.

Fragment van het werkblad: in C2:C6 staat de formule
Rij	A	B	C
1	Klant	Postcode zoals getypt	Postcode
2	Schouten Installatie BV	3011ab	3011 ab=REGEXREPLACE(B2, "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$", "$1 $2", 0, 1)=REGEXREPLACE(B2; "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$"; "$1 $2"; 0; 1)
3	Smit Handel BV	2406 be	2406 be=REGEXREPLACE(B3, "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$", "$1 $2", 0, 1)=REGEXREPLACE(B3; "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$"; "$1 $2"; 0; 1)
4	Prins Bouw BV	2991 XK	2991 XK=REGEXREPLACE(B4, "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$", "$1 $2", 0, 1)=REGEXREPLACE(B4; "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$"; "$1 $2"; 0; 1)
5	Van Dijk BV	2801mm	2801 mm=REGEXREPLACE(B5, "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$", "$1 $2", 0, 1)=REGEXREPLACE(B5; "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$"; "$1 $2"; 0; 1)
6	De Boer BV	Postbus 17	Postbus 17=REGEXREPLACE(B6, "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$", "$1 $2", 0, 1)=REGEXREPLACE(B6; "^\s([1-9][0-9]{3})\s([A-Z]{2})\s$"; "$1 $2"; 0; 1)
7

Formulecel (C2:C6)Tekens: extra spatie

Formule in C2

=REGEXREPLACE(B2, "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)

=REGEXREPLACE(B2; "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1)

Resultaat: 3011 ab

Stap voor stap

In B2 staat 3011ab. Het patroon leest zo: eventuele witruimte (\s*), vier cijfers die niet met 0 beginnen als groep 1, eventuele witruimte, twee letters als groep 2, eventuele witruimte, einde van de tekst. De ankers ^ en $ laten het patroon de hele cel beschrijven.
Het vierde argument is 0 (elke treffer). Dat moet er staan om bij het vijfde te komen, en dat is 1: hoofdletters negeren. Daarom accepteert [A-Z] ook ab.
Het patroon past op de hele tekst: groep 1 is 3011, groep 2 is ab, en de witruimte ertussen is nul tekens.
De vervanging $1 $2 schrijft groep 1, één spatie en groep 2: 3011 ab in C2. Zonder de 1 zou [A-Z] geen kleine letters accepteren, zou er niets passen en zou C2 nog steeds 3011ab tonen.
Kopieer C2 naar beneden. 2801 mm heeft een spatie aan beide kanten en twee binnenin; dat wordt allemaal gematcht maar niet vastgelegd, dus het resultaat is 2801 mm. 2991 XK is al goed en komt hetzelfde terug.
Postbus 17 past op geen enkel deel van het patroon en komt dus terug als Postbus 17, zonder foutmelding.

Let op: Een waarde die ongewijzigd terugkomt is al goed of geen postcode. Gebruik REGEXTEST met hetzelfde patroon om dat te onderscheiden voordat je de kolom vertrouwt.

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

Waarom het werkt

De groepen scheiden wat je bewaart van wat je weggooit. De spaties om en tussen de delen worden wel gematcht maar niet vastgelegd, dus ze verdwijnen, en de vervanging zet de ene spatie terug die je wilt.

Meer om op te letten

Het resultaat behoudt de hoofdletters of kleine letters van de invoer (3011 ab). Opzoeken negeert hoofdletters, dus het vergelijken werkt; zet HOOFDLETTERS rond de formule als de postcode op een brief wordt afgedrukt.

Varianten

=REGEXREPLACE(B2, "(?i)^\s*([1-9][0-9]{3})\s*([a-z]{2})\s*$", "$1 $2")

=REGEXREPLACE(B2; "(?i)^\s*([1-9][0-9]{3})\s*([a-z]{2})\s*$"; "$1 $2")

(?i) aan het begin van het patroon negeert hoofdletters, dus het vierde en vijfde argument kunnen wegblijven. Hetzelfde resultaat: 3011 ab.

Gevorderd

Opzoeken op dagboek en boekstuk in een sleutel

Je exporttool maakt per journaalpost één sleutel: dagboek, boekstuknummer, relatie en btw-percentage, gescheiden door verticale streepjes. Je loopt de boeking INK 25-0391 na, maar boekstuknummer 25-0391 komt in twee dagboeken voor. Je wilt het bedrag van de juiste, zonder hulpkolom.

Fragment van het werkblad: in F2 staat de formule
Rij	A	B	C	E	F
1	Journaalsleutel	Omschrijving	Bedrag	Dagboek en boekstuk	Bedrag
2	VRK\|25-0377\|D1001\|21%	Onderhoud juli	€12,100.00€ 12.100,00	INK 25-0391	€872.00€ 872,00=X.ZOEKEN(E2, REGEXREPLACE(A2:A6, "^([A-Z]{3})\\|([0-9]{2}-[0-9]{4})\\|.$", "$1 $2"), C2:C6, "Geen treffer")=X.ZOEKEN(E2; REGEXREPLACE(A2:A6; "^([A-Z]{3})\\|([0-9]{2}-[0-9]{4})\\|.$"; "$1 $2"); C2:C6; "Geen treffer")
3	VRK\|25-0391\|D1007\|21%	Montage kantoor	€4,235.50€ 4.235,50
4	INK\|25-0391\|C2004\|9%	Catering december	€872.00€ 872,00
5	INK\|25-0402\|C2011\|21%	Softwarelicenties	€3,025.00€ 3.025,00
6	MEM\|25-0013\|-\|0%	Afschrijving december	€2,340.00€ 2.340,00
7

Formulecel (F2)Invoer

Formule in F2

=X.ZOEKEN(E2, REGEXREPLACE(A2:A6, "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|.*$", "$1 $2"), C2:C6, "Geen treffer")

=X.ZOEKEN(E2; REGEXREPLACE(A2:A6; "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|.*$"; "$1 $2"); C2:C6; "Geen treffer")

Resultaat: €872.00€ 872,00

Stap voor stap

In E2 staat INK 25-0391. De binnenste REGEXREPLACE rekent eerst, op alle vijf de sleutels in A2:A6 tegelijk.
In elke sleutel neemt het patroon drie letters als groep 1, een verticaal streepje (\|, met backslash omdat een los | “of” betekent), het boekstuknummer als groep 2, een tweede streepje en .*$, dat de rest van de sleutel opslokt. De hele sleutel is één treffer.
De vervanging $1 $2 houdt groep 1 en 2 met een spatie ertussen en laat relatie en btw vallen. De vijf sleutels worden VRK 25-0377, VRK 25-0391, INK 25-0391, INK 25-0402 en MEM 25-0013.
Die matrix is de zoeken_matrix van X.ZOEKEN. Die zoekt INK 25-0391: de eerste is nee, de tweede (VRK 25-0391, hetzelfde boekstuknummer maar een ander dagboek) is nee, de derde is ja.
X.ZOEKEN geeft de derde cel van C2:C6 terug, dat is C4: €872.00€ 872,00 in F2.
Zet E2 op VRK 25-0391 en dezelfde formule geeft €4,235.50€ 4.235,50, de verkoopboeking.

Let op: Houd je alleen groep 2 ($2) aan, dan vindt 25-0391 eerst de verkoopboeking (€4,235.50€ 4.235,50) en komt X.ZOEKEN nooit bij de inkoopboeking: geen foutmelding, wel een verkeerd bedrag. Neem alles mee wat de sleutel uniek maakt.

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

Waarom het werkt

X.ZOEKEN vergelijkt met de matrix die je meegeeft, en REGEXREPLACE bouwt die matrix ter plekke op, zodat de bronkolom blijft zoals geëxporteerd en je geen hulpkolom nodig hebt. De groepen houden precies de twee delen die een boekstuk uniek maken en laten de rest vallen.

Meer om op te letten

Een sleutel die niet aan het patroon voldoet, zoals vrk|25-0391|... in kleine letters, komt ongewijzigd terug en wordt nooit gevonden, dus de tekst “Geen treffer” verschijnt. Wisselt de export in hoofdletters, geef dan 0 mee als vierde en 1 als vijfde argument.

Varianten

=REGEXREPLACE(A2, "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([DC][0-9]{4})\|.*$", "$3 ($1 $2)")

=REGEXREPLACE(A2; "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([DC][0-9]{4})\|.*$"; "$3 ($1 $2)")

Drie groepen, in een andere volgorde: eerst de relatie, dan dagboek en boekstuk. A2 wordt D1001 (VRK 25-0377). De memoriaalsleutel in A6 heeft geen relatiecode en komt dus ongewijzigd terug.

=FILTER(C2:C6, REGEXREPLACE(A2:A6, "^[A-Z]{3}\|([0-9]{2}-[0-9]{4})\|.*$", "$1")="25-0391")

=FILTER(C2:C6; REGEXREPLACE(A2:A6; "^[A-Z]{3}\|([0-9]{2}-[0-9]{4})\|.*$"; "$1")="25-0391")

Alle regels met boekstuknummer 25-0391, welk dagboek ook: het resultaat loopt over met €4,235.50€ 4.235,50 en €872.00€ 872,00. Zo zie je de dubbelen voordat je er een kiest.

Expert

IBAN's in verschillende notaties gelijktrekken en uitlezen

In de export van het bankafschrift staat de tegenpartij in de omschrijving, en de IBAN daarin is geschreven zoals het systeem van de cliënt hem toevallig afdrukte: in groepjes van vier, in kleine letters, met punten, met streepjes of zonder scheidingstekens. Voor de crediteurenbevestiging wil je één schone kolom met IBAN's, uitgespreid vanuit één formule, en een markering op de regel zonder IBAN.

Fragment van het werkblad: in E2 staat de formule
Rij	A	B	C	E
1	Datum	Omschrijving	Bedrag	IBAN (schoon)
2	29-12-2025	Aannemer Kuipers, NL91 ABNA 0417 1643 00	€4,235.50€ 4.235,50	NL91ABNA0417164300=LET( schoon, HOOFDLETTERS(REGEXREPLACE(B2:B7, "[^A-Za-z0-9]", "")), ALS.NB(REGEXEXTRACT(schoon, "NL[0-9]{2}[A-Z]{4}[0-9]{10}"), "geen IBAN") )=LET( schoon; HOOFDLETTERS(REGEXREPLACE(B2:B7; "[^A-Za-z0-9]"; "")); ALS.NB(REGEXEXTRACT(schoon; "NL[0-9]{2}[A-Z]{4}[0-9]{10}"); "geen IBAN") )
3	29-12-2025	Drukkerij Van Houten nl02abna0123456789	€872.00€ 872,00	NL02ABNA0123456789
4	30-12-2025	Lenting Kantoorservice NL86.INGB.0002.4455.88	€1,190.40€ 1.190,40	NL86INGB0002445588
5	30-12-2025	Tuincentrum De Eik NL69-INGB-0123-4567-89	€360.00€ 360,00	NL69INGB0123456789
6	31-12-2025	Autobedrijf Rensen NL20INGB0001234567	€2,750.00€ 2.750,00	NL20INGB0001234567
7	31-12-2025	Gemeente Lingewaard, leges 2025-118	€148.50€ 148,50	no IBAN
8

Formulecel (E2)Overloopresultaat

Formule in E2

=LET(
  schoon, HOOFDLETTERS(REGEXREPLACE(B2:B7, "[^A-Za-z0-9]", "")),
  ALS.NB(REGEXEXTRACT(schoon, "NL[0-9]{2}[A-Z]{4}[0-9]{10}"), "geen IBAN")
)

=LET(
  schoon; HOOFDLETTERS(REGEXREPLACE(B2:B7; "[^A-Za-z0-9]"; ""));
  ALS.NB(REGEXEXTRACT(schoon; "NL[0-9]{2}[A-Z]{4}[0-9]{10}"); "geen IBAN")
)

Resultaat: NL91ABNA0417164300 / NL02ABNA0123456789 / NL86INGB0002445588 / NL69INGB0123456789 / NL20INGB0001234567 / no IBANNL91ABNA0417164300 / NL02ABNA0123456789 / NL86INGB0002445588 / NL69INGB0123456789 / NL20INGB0001234567 / no IBAN

Stap voor stap

LET koppelt schoon aan de eerste expressie. REGEXREPLACE krijgt in één keer het hele bereik B2:B7, dus het geeft zes teksten terug, één per regel, en de formule loopt over.
Het patroon [^A-Za-z0-9] past op elk teken dat geen letter of cijfer is: spaties, punten, streepjes en komma's. De vervanging is een lege tekst, dus elk daarvan wordt verwijderd. Regel 1, Aannemer Kuipers, NL91 ABNA 0417 1643 00 (40 tekens), wordt AannemerKuipersNL91ABNA0417164300 (33 tekens).
De IBAN blijft over omdat hij alleen uit letters en cijfers bestaat. Welke scheidingstekens de schrijver ook gebruikte (de spaties in regel 1, de kleine letters van regel 2, de punten van regel 3, de streepjes van regel 4), de tekens zelf blijven staan en alleen de scheidingstekens verdwijnen.
HOOFDLETTERS maakt er daarna hoofdletters van, dus regel 2 wordt DRUKKERIJVANHOUTENNL02ABNA0123456789. De zes opgeschoonde teksten zijn AANNEMERKUIPERSNL91ABNA0417164300, DRUKKERIJVANHOUTENNL02ABNA0123456789, LENTINGKANTOORSERVICENL86INGB0002445588, TUINCENTRUMDEEIKNL69INGB0123456789, AUTOBEDRIJFRENSENNL20INGB0001234567 en GEMEENTELINGEWAARDLEGES2025118.
REGEXEXTRACT zoekt nu in elke opgeschoonde tekst naar NL[0-9]{2}[A-Z]{4}[0-9]{10}: NL, twee cijfers, vier hoofdletters, tien cijfers, samen 18 tekens. Het vindt NL91ABNA0417164300, NL02ABNA0123456789, NL86INGB0002445588, NL69INGB0123456789 en NL20INGB0001234567. De woorden ervoor blijven achter omdat ze geen deel van de treffer zijn.
Regel 6, GEMEENTELINGEWAARDLEGES2025118, heeft geen NL gevolgd door twee cijfers, vier hoofdletters en tien cijfers, dus REGEXEXTRACT geeft voor die regel #N/B.
ALS.NB vervangt alleen die #N/B door geen IBAN en laat de vijf IBAN's ongemoeid. De zes resultaten lopen over van E2 tot en met E7. Het zijn teksten, dus een opzoeking tegen een kolom met IBAN's vergelijkt ze als tekst.

Let op: Opschonen plakt woorden aan elkaar. De tekst Van Houten NL gevolgd door een factuurnummer wordt VANHOUTENNL... en kan met een los patroon een valse treffer geven. Houd het uitleespatroon specifiek: landcode, twee controlecijfers, vier bankletters en precies tien cijfers.

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

Waarom het werkt

Eerst opschonen houdt het uitleespatroon leesbaar en streng: als de scheidingstekens weg zijn, ziet elke IBAN er hetzelfde uit en vindt één strak patroon ze allemaal. Eerst uitlezen met een tolerant patroon dat op elke mogelijke plek een optionele spatie, punt of streepje toestaat werkt ook, maar het patroon wordt lang, is gevoelig voor fouten en laat je daarna nog steeds met scheidingstekens zitten die je moet verwijderen. Gebruik REGEXREPLACE hier niet als je Excel geen regexfuncties heeft of als de scheidingstekens bekend en weinig zijn: een SUBSTITUEREN-keten met DEEL en VIND.ALLES doet hetzelfde in elke versie, zoals de tweede variant laat zien.

Meer om op te letten

Een buitenlandse IBAN zoals DE89 3704 0044 0532 0130 00 wordt wel opgeschoond maar niet gevonden, omdat het patroon met NL begint. Die regel toont geen IBAN. Gebruik voor meerdere landen een breder patroon zoals [A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}, en controleer de uitkomsten met de hand.
Zonder HOOFDLETTERS wordt de IBAN in kleine letters van regel 2 niet gevonden: [A-Z]{4} past niet op abna, dus die regel zou geen IBAN tonen. Houd HOOFDLETTERS in de formule, of begin het patroon met (?i) en accepteer dat het resultaat zijn kleine letters houdt.

Varianten

=ALS.NB(HOOFDLETTERS(REGEXREPLACE(REGEXEXTRACT(B2:B7, "(?i)NL[0-9]{2}[ .-]?[A-Z]{4}([ .-]?[0-9]{4}){2}[ .-]?[0-9]{2}"), "[^A-Za-z0-9]", "")), "")

=ALS.NB(HOOFDLETTERS(REGEXREPLACE(REGEXEXTRACT(B2:B7; "(?i)NL[0-9]{2}[ .-]?[A-Z]{4}([ .-]?[0-9]{4}){2}[ .-]?[0-9]{2}"); "[^A-Za-z0-9]"; "")); "")

Eerst uitlezen met een tolerant patroon dat één spatie, punt of streepje tussen de delen toestaat, en dan alleen de IBAN opschonen. Het resultaat is hetzelfde, maar het patroon is langer en hapert bij scheidingstekens die het niet verwachtte (bijvoorbeeld twee spaties).

=ALS.FOUT(DEEL(HOOFDLETTERS(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2, " ", ""), ".", ""), "-", "")), VIND.ALLES("NL", HOOFDLETTERS(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2, " ", ""), ".", ""), "-", ""))), 18), "")

=ALS.FOUT(DEEL(HOOFDLETTERS(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2; " "; ""); "."; ""); "-"; "")); VIND.ALLES("NL"; HOOFDLETTERS(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2; " "; ""); "."; ""); "-"; ""))); 18); "")

Wanneer je REGEXREPLACE niet gebruikt: in een versie zonder regexfuncties, of als je de scheidingstekens kent. De SUBSTITUEREN-keten haalt spaties, punten en streepjes weg, VIND.ALLES zoekt de eerste NL en DEEL neemt 18 tekens. Het werkt in elke versie, maar je moet het per regel naar beneden kopiëren, en het gaat mis als een naam vóór de IBAN NL bevat.

Veelgemaakte fouten

Veelgemaakte fouten
Symptoom	Oorzaak	Oplossing
De tekst komt ongewijzigd terug, zonder foutmelding	Het patroon past niet. Het is standaard hoofdlettergevoelig, dus `[A-Z]` slaat kleine letters over, of een anker (`^` of `$`) eist een vorm die de tekst niet heeft, zoals een spatie vooraan.	Toets het patroon met REGEXTEST op dezelfde cel. Geef 0 mee als vierde en 1 als vijfde argument om hoofdletters te negeren, en sta spaties toe met `\s*` waar de gegevens ze kunnen hebben.
Veel meer tekens veranderen dan je wilde	Een `.` zonder backslash past op elk teken, niet alleen op een punt. Vervang je `.` in `1.250`, dan verdwijnt de hele tekst.	Schrijf `\.` voor een echte punt en schrijf de andere speciale tekens op dezelfde manier met een backslash: `\\|`, `\(`, `\?`.
Slechts een deel van de treffers veranderde, of de verkeerde	Het exemplaar is niet 0. Een positief getal telt van links en een negatief getal van rechts, dus 2 en -1 wijzen verschillende treffers aan zodra een tekst er drie heeft.	Gebruik 0 voor elke treffer, en -1 als de laatste degene is die je nodig hebt. Test met een tekst met één treffer en met een tekst met drie.
Opgeschoonde bedragen die SOM overslaat	Het resultaat is tekst, ook als het op een getal lijkt, zoals `1250` nadat je een duizendtalpunt hebt weggehaald.	Zet WAARDE eromheen: `WAARDE(REGEXREPLACE(A2;"\.";""))`. WAARDE leest het decimaalteken volgens de regio-instelling, dus een tekst met een decimale komma vraagt een Nederlandse instelling.
#NAAM? in een oudere Excel-versie	De werkmap wordt geopend in Excel 2021, Excel 2024 of ouder, en die hebben geen functies voor reguliere expressies.	Houd zulke werkmappen in Microsoft 365, plak de opgeschoonde waarden als waarden voordat je het bestand verstuurt, of doe het opschonen in Power Query of VBA.