Alle versiesVoorbeelden vragen Excel 2021 of nieuwerTekst

SUBSTITUEREN

SUBSTITUTE in Engelstalige Excel

Zet iets anders in de plaats van een stuk tekst, overal in de cel of alleen bij het zoveelste voorkomen, en zoekt daarbij op inhoud, niet op positie.

Moeilijkheidsgraad

Laatst bijgewerkt: 13 oktober 2026

Bekijk de uitgewerkte voorbeelden

ABCD
1RelatieNaamIBAN (specificatie)IBAN (schoon)
2D1001Dekker Installatietechniek BVNL91 ABNA 0417 1643 00NL91ABNA0417164300=SUBSTITUEREN(C2, " ", "")=SUBSTITUEREN(C2; " "; "")
3D1002Bakker Groothandel BVNL86 INGB 0002 4455 88
4D1003Van Dijk VerhuurNL20INGB 0001 2345 67
5D1004Smit Metaal BVNL44RABO0123456789
Tekens: extra spatieOpmerking: C4 heeft twee spaties na NL20. C5 heeft helemaal geen spatie.

Resultaat: NL91ABNA0417164300

=SUBSTITUEREN(C2, " ", "")
=SUBSTITUEREN(C2; " "; "")

Wat SUBSTITUEREN doet en wanneer je het gebruikt

Wat het doet: het vervangt een stuk tekst door andere tekst, overal waar het in een cel voorkomt, bijvoorbeeld de spaties uit een IBAN halen.

Wanneer je het gebruikt: om geïmporteerde tekst op te schonen, een scheidingsteken te ruilen, een teken weg te halen of te tellen hoe vaak iets voorkomt. Het is Zoeken en vervangen als formule die altijd actueel blijft. Staat de tekst altijd op dezelfde positie met dezelfde lengte, dan is VERVANGEN directer.

Hoe Excel de functie berekent

Excel loopt tekst van links naar rechts door op zoek naar oud_tekst, en daarbij tellen hoofdletters mee. Elke overeenkomst krijgt een volgnummer: eerste, tweede, derde.

Zonder rang_getal komt nieuw_tekst in de plaats van elke overeenkomst; met rang_getal alleen van de overeenkomst met dat nummer, de rest blijft staan.

Excel zoekt niet verder in een vervanging die het al heeft gemaakt, dus a vervangen door aa raakt niet in een eindeloze lus.

Let op: SUBSTITUEREN is hoofdlettergevoelig. Komt oud_tekst niet precies overeen, dan krijg je de oorspronkelijke tekst ongewijzigd terug, zonder foutwaarde.

Denkmodel

Een corrector met een pen die een bepaald woord overal doorstreept en er een ander woord boven schrijft, of, als je hem een nummer geeft, langs de regel telt en alleen dat ene woord aanpast.

Begrippen op deze pagina: cel

Syntaxis

Officiële documentatie van SUBSTITUEREN
SUBSTITUEREN(tekst, oud_tekst, nieuw_tekst, [rang_getal])
SUBSTITUEREN(tekst; oud_tekst; nieuw_tekst; [rang_getal])
ArgumentSoortBetekenis
tekstVerplichtDe tekst of cel waarin je iets vervangt.
oud_tekstVerplichtWaar je naar zoekt; hoofdlettergevoelig, geen jokertekens.
nieuw_tekstVerplichtWat ervoor in de plaats komt; "" haalt het weg.
rang_getalOptioneelWelk voorkomen je vervangt; laat het weg om alle te vervangen.

Geeft terug:Tekst, met nieuw_tekst op de plek van oud_tekst

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, 17 kB)

Beginner

Spaties uit IBAN-nummers halen vóór het koppelen

Op de debiteurenspecificatie van de klant staan de IBAN-nummers met de hand ingetypt in groepjes van vier: NL91 ABNA 0417 1643 00, één keer met een dubbele spatie en één keer helemaal zonder.

In de XAF-auditfile staan de IBAN-nummers zonder spaties, dus een zoekactie tussen beide lijsten vindt niets. In een extra kolom haal je elke spatie uit het IBAN.

ABCDE
1RelatieNaamIBAN (specificatie)IBAN (schoon)
2D1001Dekker Installatietechniek BVNL91 ABNA 0417 1643 00NL91ABNA0417164300=SUBSTITUEREN(C2, " ", "")=SUBSTITUEREN(C2; " "; "")
3D1002Bakker Groothandel BVNL86 INGB 0002 4455 88
4D1003Van Dijk VerhuurNL20INGB 0001 2345 67
5D1004Smit Metaal BVNL44RABO0123456789
6
Formulecel (D2)InvoerTekens: extra spatieOpmerking: C4 heeft twee spaties na NL20. C5 heeft helemaal geen spatie.

Formule in D2

=SUBSTITUEREN(C2, " ", "")
=SUBSTITUEREN(C2; " "; "")

Resultaat: NL91ABNA0417164300

Stap voor stap
  1. Excel leest C2: NL91 ABNA 0417 1643 00, 22 tekens, met een spatie op positie 5, 10, 15 en 20.
  2. oud_tekst is één spatie. Excel zoekt van links en vindt vier overeenkomsten. Omdat rang_getal ontbreekt, worden er alle vier vervangen.
  3. nieuw_tekst is leeg, dus elke spatie wordt door niets vervangen. D2 toont NL91ABNA0417164300, 22 - 4 = 18 tekens.
  4. Kopieer D2 naar beneden. C3, NL86 INGB 0002 4455 88, heeft vier spaties en wordt NL86INGB0002445588. C4, NL20 INGB 0001 2345 67, heeft vijf spaties (twee na NL20) en wordt NL20INGB0001234567.
  5. In C5 staat NL44RABO0123456789, zonder spatie. Er valt niets te vervangen, dus D5 toont het IBAN zoals het was: NL44RABO0123456789, zonder foutwaarde.

Let op: Een IBAN dat je uit een pdf-bankafschrift kopieert, kan een vaste spatie bevatten. SUBSTITUEREN met " " ziet die niet, en de zoekactie blijft mislukken. Blijft LENGTE op 22 staan, vervang dan ook TEKEN(160).

Meer over dit voorbeeld: waarom het werkt, 1 variant

Waarom het werkt

SUBSTITUEREN zoekt op inhoud, dus het maakt niet uit waar de spaties staan of hoeveel het er zijn. Zonder rang_getal gaat elke overeenkomst weg, en een lege nieuw_tekst maakt van vervangen verwijderen. Rijen die al schoon waren komen ongewijzigd door, dus dezelfde formule werkt voor de hele specificatie.

Varianten

=HOOFDLETTERS(SUBSTITUEREN(C2, " ", ""))
=HOOFDLETTERS(SUBSTITUEREN(C2; " "; ""))

Zet de letters ook in hoofdletters, voor specificaties waarin iemand nl91 abna heeft getypt.

Halfgevorderd

Alleen het tweede streepje in boekstuknummers aanpassen

De XAF-export nummert boekstukken als dagboek, jaar en volgnummer, zoals INK-25-0412. De subadministratie van de klant schrijft INK-25/0412, met een schuine streep vóór het volgnummer, terwijl het streepje na de dagboekcode moet blijven. Bankboekstukken zoals BNK-0415 hebben geen jaardeel en moeten ongewijzigd blijven, zodat je beide lijsten kunt koppelen.

ABCD
1BoekstukBedragNotatie subadministratie
2INK-25-0412€2,420.00€ 2.420,00INK-25/0412=SUBSTITUEREN(A2, "-", "/", 2)=SUBSTITUEREN(A2; "-"; "/"; 2)
3INK-25-0413€845.50€ 845,50
4BNK-0415€12,500.00€ 12.500,00
5VRK-25-1187€3,630.00€ 3.630,00
6MEM-25-0031€975.00€ 975,00
7
Formulecel (C2)Invoer

Formule in C2

=SUBSTITUEREN(A2, "-", "/", 2)
=SUBSTITUEREN(A2; "-"; "/"; 2)

Resultaat: INK-25/0412

Stap voor stap
  1. Excel leest A2, INK-25-0412, en zoekt naar een streepje. Het vindt het eerste op positie 4 en telt het als voorkomen 1.
  2. rang_getal is 2, dus voorkomen 1 blijft staan. Het zoeken gaat door en vindt het volgende streepje op positie 7: voorkomen 2.
  3. Dat streepje wordt vervangen door een schuine streep. C2 toont INK-25/0412.
  4. Zonder rang_getal zou SUBSTITUEREN(A2; "-"; "/") beide overeenkomsten vervangen en INK/25/0412 teruggeven, en dat komt niet meer overeen met de subadministratie.
  5. Kopieer C2 naar C6. A4 is BNK-0415, met maar één streepje. Voorkomen 2 bestaat niet, dus er wordt niets vervangen en C4 toont BNK-0415, zoals het scenario vraagt.
  6. C5 geeft VRK-25/1187 en C6 geeft MEM-25/0031. De formule vindt het streepje op inhoud, niet op positie, dus een langer volgnummer werkt precies zo.

Let op: Een boekstuknummer dat met een extra streepje is getypt, zoals INK-25-04-12, krijgt de schuine streep op de verkeerde plek: INK-25/04-12. Er verschijnt geen foutwaarde; pas bij het koppelen gaat het mis.

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

Waarom het werkt

Met rang_getal vervangt SUBSTITUEREN niet meer alles, maar alleen het voorkomen dat je aanwijst. Omdat er van links wordt geteld, is het tweede streepje altijd het streepje vóór het volgnummer, hoe lang de dagboekcode ook is. Een ontbrekend voorkomen is geen fout, en daardoor komen de bankboekstukken ongewijzigd door.

Meer om op te letten

  • Met rang_getal 0 zeg je niet 'allemaal': je krijgt #WAARDE!. Wil je alles vervangen, laat het argument dan weg.

Varianten

=VERVANGEN(A2, 7, 1, "/")
=VERVANGEN(A2; 7; 1; "/")

VERVANGEN op positie geeft voor INK-25-0412 hetzelfde resultaat, maar maakt van BNK-0415 BNK-04/5 en gaat mis zodra een dagboekcode vier letters heeft.

Gevorderd

De laatste komma in een lijst boekstukken vervangen door 'en'

Bij je journaalpostentest zet je de handmatige MEM-boekstukken per grootboekrekening in één cel, gescheiden door komma's.

Voor de managementletter wil je vóór het laatste boekstuk 'en' in plaats van een komma: MEM-0031, MEM-0044 en MEM-0052. Het aantal boekstukken wisselt per rekening, dus je kunt niet vast in de formule zetten welke komma de laatste is.

ABCD
1GrootboekrekeningHandmatige boekstukkenVoor de brief
24000MEM-0031, MEM-0044, MEM-0052MEM-0031, MEM-0044 en MEM-0052=SUBSTITUEREN(B2, ",", " en", LENGTE(B2) - LENGTE(SUBSTITUEREN(B2, ",", "")))=SUBSTITUEREN(B2; ","; " en"; LENGTE(B2) - LENGTE(SUBSTITUEREN(B2; ","; "")))
34500MEM-0017, MEM-0090
48000MEM-0003
51300MEM-0021, MEM-0035, MEM-0068, MEM-0071
6
Formulecel (C2)Invoer

Formule in C2

=SUBSTITUEREN(B2, ",", " en", LENGTE(B2) - LENGTE(SUBSTITUEREN(B2, ",", "")))
=SUBSTITUEREN(B2; ","; " en"; LENGTE(B2) - LENGTE(SUBSTITUEREN(B2; ","; "")))

Resultaat: MEM-0031, MEM-0044 en MEM-0052

Stap voor stap
  1. Excel begint met de binnenste functie. SUBSTITUEREN(B2; ","; "") haalt elke komma uit MEM-0031, MEM-0044, MEM-0052 en geeft MEM-0031 MEM-0044 MEM-0052.
  2. LENGTE(B2) telt 28 tekens: 8 + 1 + 1 + 8 + 1 + 1 + 8 (drie boekstukken, twee komma's, twee spaties). LENGTE van de tekst zonder komma's is 26.
  3. 28 - 26 = 2. Elke komma is één teken, dus het verschil is het aantal komma's, en de laatste komma is voorkomen 2.
  4. De buitenste SUBSTITUEREN vervangt voorkomen 2 van de komma door de tekst " en". De spatie die na de komma stond blijft staan, dus het resultaat is MEM-0031, MEM-0044 en MEM-0052.
  5. Kopieer C2 naar beneden. C3: MEM-0017, MEM-0090 is 18 tekens, 17 zonder de komma, dus 1 komma en voorkomen 1 wordt vervangen: MEM-0017 en MEM-0090. C5: 38 - 35 = 3 komma's, dus MEM-0021, MEM-0035, MEM-0068 en MEM-0071.
  6. C4 gaat mis: MEM-0003 heeft geen komma, 8 - 8 = 0, en rang_getal 0 geeft #WAARDE!. De eerste variant vangt deze rij op.

Let op: Een lijst met een komma aan het eind, zoals MEM-0017, MEM-0090, uit een samenvoeging met een lege cel, telt 2 komma's en vervangt de laatste: MEM-0017, MEM-0090 en. Het ziet eruit als een zin, maar het klopt niet.

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

Waarom het werkt

Een teken weghalen en meten hoeveel korter de tekst wordt is de gangbare manier om voorkomens te tellen in Excel, omdat er geen functie is die ze direct telt. Dat aantal is ook het nummer van het laatste voorkomen, en dat is precies wat rang_getal nodig heeft. De teltruc werkt zo alleen voor één teken; voor een langer patroon deel je het verschil door de lengte van het patroon.

Meer om op te letten

  • Een boekstuk met een komma erin telt als twee boekstukken. Houd het scheidingsteken uit de gegevens of gebruik een ander, zoals een puntkomma.

Varianten

=ALS.FOUT(SUBSTITUEREN(B2, ",", " en", LENGTE(B2) - LENGTE(SUBSTITUEREN(B2, ",", ""))), B2)
=ALS.FOUT(SUBSTITUEREN(B2; ","; " en"; LENGTE(B2) - LENGTE(SUBSTITUEREN(B2; ","; ""))); B2)

Staat er maar één boekstuk en dus geen komma, dan komt dat boekstuk ongewijzigd terug. ALS.FOUT verbergt ook andere fouten, bijvoorbeeld een fout die al in B2 staat; gebruik het dus alleen op schone gegevens.

=LENGTE(B2) - LENGTE(SUBSTITUEREN(B2, ",", "")) + 1
=LENGTE(B2) - LENGTE(SUBSTITUEREN(B2; ","; "")) + 1

De teltruc op zichzelf: het aantal boekstukken is het aantal komma's plus 1.

ExpertExcel 2021 of nieuwer

Factuurverwijzingen normaliseren met vervangingen op volgorde in LET

Om de debiteurenadministratie met het grootboek te koppelen heb je alleen het factuurnummer nodig, zoals 25-0412. In de omschrijvingen staat het in meerdere vormen:

  • Factuur nr. 25-0412
  • Factuur 25-0413
  • Fact 25/0420
  • alleen het nummer

Je schrijft één formule die de vervangingen in een vaste, leesbare volgorde uitvoert, en kopieert die naar beneden.

ABCD
1RelatieOmschrijving zoals geëxporteerdKoppelsleutel
2D1001Factuur nr. 25-041225-0412=LET( ruw, B2, zonderLang, SUBSTITUEREN(ruw, "Factuur nr. ", ""), zonderKort, SUBSTITUEREN(zonderLang, "Factuur ", ""), zonderFact, SUBSTITUEREN(zonderKort, "Fact ", ""), SUBSTITUEREN(zonderFact, "/", "-") )=LET( ruw; B2; zonderLang; SUBSTITUEREN(ruw; "Factuur nr. "; ""); zonderKort; SUBSTITUEREN(zonderLang; "Factuur "; ""); zonderFact; SUBSTITUEREN(zonderKort; "Fact "; ""); SUBSTITUEREN(zonderFact; "/"; "-") )
3D1002Factuur 25-0413
4D1003Fact 25/0420
5D100425-0431
6
Formulecel (C2)Invoer

Formule in C2

=LET(
  ruw, B2,
  zonderLang, SUBSTITUEREN(ruw, "Factuur nr. ", ""),
  zonderKort, SUBSTITUEREN(zonderLang, "Factuur ", ""),
  zonderFact, SUBSTITUEREN(zonderKort, "Fact ", ""),
  SUBSTITUEREN(zonderFact, "/", "-")
)
=LET(
  ruw; B2;
  zonderLang; SUBSTITUEREN(ruw; "Factuur nr. "; "");
  zonderKort; SUBSTITUEREN(zonderLang; "Factuur "; "");
  zonderFact; SUBSTITUEREN(zonderKort; "Fact "; "");
  SUBSTITUEREN(zonderFact; "/"; "-")
)

Resultaat: 25-0412

Stap voor stap
  1. ruw is B2: Factuur nr. 25-0412, 19 tekens.
  2. zonderLang haalt het patroon "Factuur nr. " van 12 tekens als geheel weg: 25-0412, 7 tekens. Dit moet vóór het kortere "Factuur ", anders blijft de tekst nr. achter.
  3. zonderKort zoekt naar "Factuur " en vindt niets meer, dus 25-0412 komt ongewijzigd door. zonderFact doet hetzelfde voor "Fact ".
  4. De laatste stap vervangt "/" door "-" en vindt geen schuine streep, dus C2 toont 25-0412.
  5. Kopieer naar beneden. C3: Factuur 25-0413 (15 tekens) verliest "Factuur " in zonderKort: 25-0413. C4: Fact 25/0420 (12 tekens) verliest "Fact " in zonderFact: 25/0420, en de laatste stap maakt van de schuine streep een streepje: 25-0420. C5: 25-0431 bevat geen patroon en blijft 25-0431.
  6. Elke rij eindigt met 7 tekens. Het resultaat is tekst, en dat is wat een exacte koppeling op de sleutel van de subadministratie nodig heeft.

Let op: De omgekeerde fout, en zo beginnen de meeste mensen: eerst "Factuur " weghalen. Factuur nr. 25-0412 wordt nr. 25-0412, het langere patroon bestaat niet meer en de sleutel komt niet overeen, zonder foutwaarde.

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

Waarom het werkt

Elke SUBSTITUEREN behandelt één patroon, en LET geeft elke stap een naam, zodat de keten van boven naar beneden leest in plaats van van binnen naar buiten.

De volgorde is de eigenlijke ontwerpbeslissing: zit het ene patroon in het andere, hier bevat "Factuur nr. " ook "Factuur ", dan moet het langste, meest specifieke patroon eerst, anders laat het kortere een restant achter dat geen latere stap herkent.

Gebruik deze opbouw niet als de lijst vervangingen lang is of vaak verandert: boven vier of vijf stappen wordt de formule lastig te onderhouden. Het alternatief is een vervangingstabel met twee kolommen (zoeken, vervangen) die je in Excel 2024 of Microsoft 365 met REDUCE verwerkt, of een Power Query-stap met Waarden vervangen; beide houden de regels als gegevens in plaats van in een formule, ten koste van een minder doorzichtige cel.

Meer om op te letten

  • SUBSTITUEREN is hoofdlettergevoelig. Een omschrijving die als FACTUUR 25-0413 is geëxporteerd komt niet overeen met "Factuur " en komt ongewijzigd door. Voeg een stap voor de hoofdletters toe als je bronnen die gebruiken.
  • Controleer de lengte van het resultaat met LENGTE. Een rij die niet op 7 uitkomt had een patroon dat je keten niet kent, en SUBSTITUEREN vertelt je dat niet.

Varianten

=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2, "Factuur nr. ", ""), "Factuur ", ""), "Fact ", ""), "/", "-")
=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(B2; "Factuur nr. "; ""); "Factuur "; ""); "Fact "; ""); "/"; "-")

Dezelfde keten genest zonder LET; werkt in elke versie en leest van binnen naar buiten, dus de eerste vervanging is de binnenste.

Veelgemaakte fouten

SymptoomOorzaakOplossing
De tekst komt ongewijzigd terugSUBSTITUEREN lijkt niets te doen: de tekst komt ongewijzigd terug.oud_tekst kwam niet overeen. Controleer hoofdletters en kleine letters (SUBSTITUEREN is hoofdlettergevoelig) en zoek naar een vaste spatie, TEKEN(160), waar je een gewone verwachtte.
Alle streepjes veranderd, niet éénJe wilde één streepje vervangen, maar ze zijn allemaal veranderd.Voeg rang_getal toe: SUBSTITUEREN(A2; "-"; "/"; 2) verandert alleen het tweede.
SOM telt de opgeschoonde bedragen niet meeJe hebt de scheidingstekens voor duizendtallen uit de bedragen gehaald, maar SOM telt de kolom nog steeds niet mee.SUBSTITUEREN levert altijd tekst op, ook als er alleen cijfers overblijven. Zet er WAARDE omheen, dan krijg je een getal.
Alleen bij sommige rijen een verkeerd resultaatEen reeks geneste SUBSTITUEREN-functies geeft alleen voor sommige rijen een verkeerd resultaat.De volgorde telt zodra het ene patroon in het andere zit. Vervang eerst het langste, meest specifieke patroon; zie het voorbeeld op niveau Expert.

Gebruikt in combinaties

  • Een geplakte lijst splitsen en opschonen tot één kolomSplits een in één cel geplakte lijst factuurnummers op de komma's, haal de losse spaties weg met SPATIES.WISSEN en zet de stukjes met NAAR.KOLOM onder elkaar, klaar om te vergelijken.Excel 2024 of nieuwer