Alle versiesVoorbeelden vragen Excel 2021 of nieuwerTekst

VERVANGEN

REPLACE in Engelstalige Excel

Zet op een gekozen positie, over een gekozen lengte, nieuwe tekst in de plaats van wat er stond, welke tekens dat ook zijn.

Moeilijkheidsgraad

Laatst bijgewerkt: 13 oktober 2026

Bekijk de uitgewerkte voorbeelden

ABCDE
1BoekstukRelatieNummer auditfile20252025
2VRK-2026-0412D1001VRK-2025-0412=VERVANGEN(A2, 5, 4, "2025")=VERVANGEN(A2; 5; 4; "2025")
3INK-2026-0377C2001
4BNK-2026-0120D1014
5MEM-2024-0009C2007
Opmerking: E1 bevat het boekjaar; de variant gebruikt die cel. De laatste boeking is bewust een memoriaalboeking uit 2024.

Resultaat: VRK-2025-0412

=VERVANGEN(A2, 5, 4, "2025")
=VERVANGEN(A2; 5; 4; "2025")

Wat VERVANGEN doet en wanneer je het gebruikt

Wat het doet: het vervangt een stuk tekst, aangewezen met een positie en een lengte, door nieuwe tekst, bijvoorbeeld het jaartal in een boekstuknummer.

Wanneer je het gebruikt: als je het stuk aanwijst met zijn plek: positie 5 tot en met 8 van een contractnummer, alles behalve de laatste vier cijfers, een spatie na het vierde teken. Wijs je het aan met zijn inhoud (elk streepje), neem dan SUBSTITUEREN.

Hoe Excel de functie berekent

Excel nummert de tekens van oud_tekst vanaf 1. Daarna werkt het zo:

  • alles vóór begin_getal blijft staan
  • vanaf begin_getal worden aantal_tekens tekens overgeslagen
  • op die plek komt nieuw_tekst
  • daarachter komt wat er na de overgeslagen tekens nog over is

Als som: de eerste begin_getal - 1 tekens, plus nieuw_tekst, plus de rest. De uitkomst is altijd tekst.

Let op: VERVANGEN werkt met posities, niet met inhoud. aantal_tekens is het aantal tekens dat verdwijnt, dus gebruik 0 om alleen in te voegen; wil je een tekst vervangen op wat er staat, neem dan SUBSTITUEREN.

Denkmodel

Correctietape op een regel tekst: je plakt op een bepaalde plek een stuk van een bepaalde lengte af en typt eroverheen. Is dat stuk nul lang, dan prop je er alleen extra letters tussen.

Syntaxis

Officiële documentatie van VERVANGEN
VERVANGEN(oud_tekst, begin_getal, aantal_tekens, nieuw_tekst)
VERVANGEN(oud_tekst; begin_getal; aantal_tekens; nieuw_tekst)
ArgumentSoortBetekenis
oud_tekstVerplichtDe oorspronkelijke tekst die je wilt aanpassen; meestal een celverwijzing.
begin_getalVerplichtPositie van het eerste teken dat vervangen wordt, geteld vanaf 1.
aantal_tekensVerplichtHoeveel tekens er weg gaan. Bij 0 gaat er niets weg en wordt nieuw_tekst ingevoegd.
nieuw_tekstVerplichtDe tekst die in het gat komt. Gebruik "" om te verwijderen zonder te vervangen.

Geeft terug:Tekst: oud_tekst waarin de gekozen tekens vervangen zijn door nieuw_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

Het boekjaar in boekstuknummers herstellen

Veldhuis Installatie BV exporteerde de subadministratie met het exportjaar in elk boekstuknummer: VRK-2026-0412. In de auditfile staat bij dezelfde boekingen het boekjaar, 2025. Om de twee lijsten te kunnen aansluiten heb je van elk nummer de 2025-versie nodig, met het volgnummer aan het eind ongewijzigd.

ABCDEF
1BoekstukRelatieNummer auditfile20252025
2VRK-2026-0412D1001VRK-2025-0412=VERVANGEN(A2, 5, 4, "2025")=VERVANGEN(A2; 5; 4; "2025")
3INK-2026-0377C2001
4BNK-2026-0120D1014
5MEM-2024-0009C2007
6
Formulecel (C2)InvoerOpmerking: E1 bevat het boekjaar; de variant gebruikt die cel. De laatste boeking is bewust een memoriaalboeking uit 2024.

Formule in C2

=VERVANGEN(A2, 5, 4, "2025")
=VERVANGEN(A2; 5; 4; "2025")

Resultaat: VRK-2025-0412

Stap voor stap
  1. oud_tekst is A2: VRK-2026-0412, 13 tekens. V, R en K zijn 1 tot en met 3, het streepje is 4, het jaartal beslaat 5 tot en met 8, het tweede streepje is 9 en 0412 beslaat 10 tot en met 13.
  2. begin_getal is 5, dus alles vóór positie 5 blijft staan: VRK-.
  3. aantal_tekens is 4, dus de posities 5, 6, 7 en 8 gaan eruit: 2026.
  4. In het gat komt nieuw_tekst, 2025, en daarachter de rest vanaf positie 9: -0412. VRK- + 2025 + -0412 wordt VRK-2025-0412, en dat staat in C2.
  5. Kopieer C2 omlaag. C3 geeft INK-2025-0377 en C4 geeft BNK-2025-0120. C5 geeft MEM-2025-0009, terwijl die memoriaalboeking in 2024 is geboekt: VERVANGEN kijkt niet welk jaar er stond, het overschrijft positie 5 tot en met 8, wat daar ook staat.

Let op: Heeft een nummer een langer voorvoegsel, zoals VRKX-2026-0412, dan komt 2025 over de verkeerde tekens heen: op positie 5 tot en met 8 staat nu -202, en de uitkomst is VRKX20256-0412. Je krijgt geen waarschuwing; controleer dus of alle nummers dezelfde opbouw hebben.

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

Waarom het werkt

Het jaartal staat altijd op dezelfde vier posities, dus als je het aanwijst met zijn plek, zit je altijd goed. Omdat VERVANGEN niet naar de inhoud kijkt, pakt één formule elk dagboekvoorvoegsel op dezelfde manier aan. SUBSTITUEREN(A2; "2026"; "2025") zou de boeking uit 2024 laten staan, wat voor die rij klopt, maar alleen door wat er toevallig in de cel staat.

Meer om op te letten

  • Boekingen die echt bij een ander jaar horen, zoals MEM-2024-0009, worden ook overschreven. Filter eerst op het jaar, anders laat de aansluiting een verschil zien dat er niet is.

Varianten

=VERVANGEN(A2, 5, 4, $E$1)
=VERVANGEN(A2; 5; 4; $E$1)

Haalt het boekjaar uit E1 in plaats van uit de formule, zodat je volgend jaar maar één cel hoeft aan te passen. VERVANGEN maakt zelf tekst van het getal 2025.

Halfgevorderd

Factuurnummers dezelfde streepjesopbouw geven

Je sluit openstaande facturen in de debiteurenadministratie aan op het grootboek. In het grootboek staan factuurnummers op twee manieren: 250412 zonder streepje en 25-0377 met streepje. De debiteurenadministratie gebruikt 25-0412. Voeg je na het tweede teken een streepje in, dan klopt de eerste soort, maar krijgt de tweede soort er twee.

ABCDE
1Factuurnummer (GB)RelatieBedragOpbouw debiteuren
2250412D1001€12,100.00€ 12.100,00
325-0377D1014€4,235.00€ 4.235,0025-0377=VERVANGEN(SUBSTITUEREN(A3, "-", ""), 3, 0, "-")=VERVANGEN(SUBSTITUEREN(A3; "-"; ""); 3; 0; "-")
4250398D1001€968.00€ 968,00
525-0455D1022€7,260.00€ 7.260,00
6
Formulecel (D3)Invoer

Formule in D3

=VERVANGEN(SUBSTITUEREN(A3, "-", ""), 3, 0, "-")
=VERVANGEN(SUBSTITUEREN(A3; "-"; ""); 3; 0; "-")

Resultaat: 25-0377

Stap voor stap
  1. Excel rekent eerst SUBSTITUEREN(A3; "-"; "") uit. A3 is 25-0377, 7 tekens. SUBSTITUEREN zoekt op inhoud en haalt elk streepje weg, waar dat ook staat: 250377, 6 tekens.
  2. VERVANGEN krijgt 250377 binnen. begin_getal is 3, dus 25 (positie 1 en 2) blijft staan.
  3. aantal_tekens is 0: er gaat niets uit. De 0 op positie 3 blijft staan.
  4. Vóór positie 3 komt nieuw_tekst, een streepje, en daarna volgt 0377. 25 + streepje + 0377 wordt 25-0377, 7 tekens. Dat staat in D3.
  5. Zonder SUBSTITUEREN zou VERVANGEN het streepje in 25-0377 zetten zoals het er staat, vóór positie 3: 25- + -0377 wordt 25--0377 met twee streepjes, 8 tekens, en die vind je niet terug in de debiteurenadministratie.
  6. In D2 valt er bij 250412 geen streepje weg te halen, en het wordt 25-0412. D4 levert 25-0398 en D5 25-0455. Alle vier hebben nu dezelfde opbouw als de debiteurenadministratie.

Let op: Een nummer met een spatie, 25 0412, houdt die spatie: SUBSTITUEREN haalt alleen streepjes weg, dus de uitkomst is 25- 0412 en die sluit nog steeds niet aan. Haal de spaties op dezelfde manier weg voordat je invoegt.

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

Waarom het werkt

Elke functie doet het deel waar ze goed in is. SUBSTITUEREN werkt op inhoud en haalt het streepje weg waar het ook stond, zodat elk factuurnummer dezelfde vorm van zes tekens krijgt. VERVANGEN werkt op positie en zet met aantal_tekens 0 een streepje op precies positie 3 van die gelijke vorm.

Meer om op te letten

  • Ook een nummer met een tikfout, zoals 2504 (twee cijfers ontbreken), krijgt zijn streepje na het tweede teken: 25-04. De formule maakt op, maar controleert niet of het nummer klopt. Is dat belangrijk, controleer dan de opgeschoonde waarde met LENGTE.

Varianten

=VERVANGEN(SUBSTITUEREN(SUBSTITUEREN(A3, " ", ""), "-", ""), 3, 0, "-")
=VERVANGEN(SUBSTITUEREN(SUBSTITUEREN(A3; " "; ""); "-"; ""); 3; 0; "-")

Haalt ook spaties weg, niet alleen streepjes, voor grootboeken waarin sommige nummers met een spatie zijn ingetypt.

Gevorderd

IBAN's maskeren, behalve begin en eind

Voor de opvolging van bevestigingen plak je de bankgegevens van crediteuren in een werkpapier dat naar het hele auditteam en naar de financiële afdeling van de cliënt gaat. De IBAN's moeten gemaskeerd worden: laat de landcode met controlecijfers (de eerste 4 tekens) en de laatste 4 tekens zien, en zet sterretjes op alles daartussen. Een IBAN is niet in elk land even lang, dus het aantal sterretjes moet zich aan de lengte aanpassen.

ABCDE
1RelatieIBANSaldoGemaskeerd
2C2001NL27XBNK0192837465€1,840.00€ 1.840,00NL27**********7465=VERVANGEN(B2, 5, LENGTE(B2) - 8, HERHALING("*", LENGTE(B2) - 8))=VERVANGEN(B2; 5; LENGTE(B2) - 8; HERHALING("*"; LENGTE(B2) - 8))
3C2007BE71XBNK12345678€615.20€ 615,20
4C2019DE44XBNK00123456789012€2,975.00€ 2.975,00
5
Formulecel (D2)InvoerOpmerking: De rekeningnummers zijn fictief en gebruiken een verzonnen bankcode, XBNK.

Formule in D2

=VERVANGEN(B2, 5, LENGTE(B2) - 8, HERHALING("*", LENGTE(B2) - 8))
=VERVANGEN(B2; 5; LENGTE(B2) - 8; HERHALING("*"; LENGTE(B2) - 8))

Resultaat: NL27**********7465

Stap voor stap
  1. Excel rekent eerst LENGTE(B2) uit. NL27XBNK0192837465 is NL27 (4) + XBNK (4) + 0192837465 (10) = 18 tekens.
  2. LENGTE(B2) - 8 = 18 - 8 = 10: trek de 4 tekens vooraan en de 4 achteraan eraf, en er blijven 10 tekens over om te verbergen. Op beide plekken in de formule komt die 10 terecht.
  3. HERHALING("*"; 10) maakt nieuw_tekst: tien sterretjes.
  4. VERVANGEN laat positie 1 tot en met 4 staan (NL27), haalt vanaf positie 5 10 tekens weg (XBNK019283, positie 5 tot en met 14), zet de tien sterretjes neer en plakt de rest erachter: 7465 (positie 15 tot en met 18).
  5. D2 toont NL27**********7465: 4 + 10 + 4 = 18 tekens, even lang als het origineel.
  6. In D3 telt de Belgische IBAN 16 tekens, dus 16 - 8 = 8 sterretjes: BE71********5678. In D4 heeft de Duitse IBAN er 22, en dus 14 sterretjes: DE44**************9012.

Let op: Is een IBAN met spaties opgeslagen, NL27 XBNK 0192 8374 65, dan telt hij 22 tekens. Het masker verbergt dan 14 tekens, spaties meegerekend, en laat aan het eind 4 65 zien: het ziet er gemaskeerd uit, maar er worden andere cijfers getoond. Haal de spaties eerst weg met SUBSTITUEREN.

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

Waarom het werkt

Voor VERVANGEN maakt het niet uit welke tekens je verbergt, alleen waar ze staan en hoeveel het er zijn, en dat is precies wat maskeren is. Door LENGTE passen beide getallen zich aan elke IBAN aan, en HERHALING maakt nieuw_tekst precies zo lang als het stuk dat het afdekt. De gemaskeerde waarde houdt zo zijn oorspronkelijke lengte en vorm.

Meer om op te letten

  • Maskeren met een formule beschermt niets zolang de oorspronkelijke kolom in de werkmap staat. Verberg of verwijder kolom B, of plak de gemaskeerde kolom als waarden in het werkpapier dat je deelt.
  • Is een waarde korter dan 8 tekens, dan wordt LENGTE(B2) - 8 negatief en geeft VERVANGEN #WAARDE!. Dat is een bruikbaar signaal dat er geen IBAN in de cel staat.

Varianten

=LINKS(B2, 4) & HERHALING("*", LENGTE(B2) - 8) & RECHTS(B2, 4)
=LINKS(B2; 4) & HERHALING("*"; LENGTE(B2) - 8) & RECHTS(B2; 4)

Bouwt het masker op uit drie stukken in plaats van het midden te overschrijven. Dezelfde uitkomst; sommigen zien zo beter welke delen zichtbaar blijven.

ExpertExcel 2021 of nieuwer

Bankreferenties opmaken met twee ingevoegde streepjes

In de export van het bankafschrift staat de betalingsreferentie van elke ontvangst als tien tekens, VRK2501234, terwijl het grootboek hem bewaart als VRK-25-01234. Om ze aan te sluiten wil je één formule die de kale referenties opmaakt, de al opgemaakte met rust laat en bij het kopiëren niet ontspoort. Eén referentie is met de hand met streepjes ingetypt, en in één staat door kopiëren en plakken een spatie vooraan.

ABCD
1BankreferentieBedragGrootboekformaat
2VRK2501234€12,100.00€ 12.100,00VRK-25-01234=LET( ruw, SPATIES.WISSEN(A2), isRuw, LENGTE(ruw) = 10, metSeconden, VERVANGEN(ruw, 6, 0, "-"), opgemaakt, VERVANGEN(metSeconden, 4, 0, "-"), ALS(isRuw, opgemaakt, ruw) )=LET( ruw; SPATIES.WISSEN(A2); isRuw; LENGTE(ruw) = 10; metSeconden; VERVANGEN(ruw; 6; 0; "-"); opgemaakt; VERVANGEN(metSeconden; 4; 0; "-"); ALS(isRuw; opgemaakt; ruw) )
3INK2500377€4,235.00€ 4.235,00
4VRK-25-00412€968.00€ 968,00
5VRK2500998€7,260.00€ 7.260,00
6
Formulecel (C2)InvoerTekens: extra spatieOpmerking: A5 begint met een spatie. A4 is al met de hand opgemaakt.

Formule in C2

=LET(
  ruw, SPATIES.WISSEN(A2),
  isRuw, LENGTE(ruw) = 10,
  metSeconden, VERVANGEN(ruw, 6, 0, "-"),
  opgemaakt, VERVANGEN(metSeconden, 4, 0, "-"),
  ALS(isRuw, opgemaakt, ruw)
)
=LET(
  ruw; SPATIES.WISSEN(A2);
  isRuw; LENGTE(ruw) = 10;
  metSeconden; VERVANGEN(ruw; 6; 0; "-");
  opgemaakt; VERVANGEN(metSeconden; 4; 0; "-");
  ALS(isRuw; opgemaakt; ruw)
)

Resultaat: VRK-25-01234

Stap voor stap
  1. ruw is SPATIES.WISSEN(A2): VRK2501234, ongewijzigd, want er staan geen spaties in. isRuw controleert of LENGTE(ruw) = 10: 3 letters + 7 cijfers = 10, dus WAAR.
  2. metSeconden voegt eerst het rechterstreepje in. VERVANGEN(ruw; 6; 0; "-") laat positie 1 tot en met 5 staan (VRK25), haalt niets weg, zet een streepje neer en plakt 01234 erachter: VRK25-01234, 11 tekens.
  3. opgemaakt voegt het linkerstreepje in. Positie 4 is nog steeds de oorspronkelijke positie 4, want de eerste invoeging zat rechts daarvan: VERVANGEN(metSeconden; 4; 0; "-") laat VRK staan, zet een streepje neer en plakt 25-01234 erachter. Uitkomst: VRK-25-01234, 12 tekens.
  4. isRuw is WAAR, dus ALS kiest opgemaakt. C2 toont VRK-25-01234.
  5. In C4 is VRK-25-00412 al 12 tekens lang. isRuw is ONWAAR, en ALS geeft ruw ongewijzigd terug. Zonder die controle zouden de invoegingen VRK--2-5-00412 opleveren.
  6. In C5 maakt SPATIES.WISSEN van de 11 tekens in A5 VRK2500998, 10 tekens, met als uitkomst VRK-25-00998. Zonder SPATIES.WISSEN was LENGTE 11, gold de rij als al opgemaakt en bleef de spatie vooraan in de referentie staan, zodat de aansluiting met het grootboek mislukt.

Let op: De omgekeerde fout is van links naar rechts invoegen met dezelfde posities: VERVANGEN(VERVANGEN(ruw; 4; 0; "-"); 6; 0; "-"). Het eerste streepje schuift alles één plek op, dus het tweede komt één teken te vroeg: VRK-2-501234. Twee streepjes, twaalf tekens: bij een snelle blik valt het niet op.

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

Waarom het werkt

Elke invoeging met VERVANGEN schuift alle latere posities op met de lengte van wat je invoegt. Werk je van rechts naar links, dan heb je daar geen last van: elke invoeging gebeurt rechts van de invoegingen die nog volgen en verschuift die dus niet, zodat de posities die je op de kale tekst hebt uitgeteld blijven kloppen. Met LET heeft elke stap een naam, zodat je de volgorde ziet en per stap kunt testen.

Bestaat de waarde alleen uit cijfers, gebruik dit dan niet: een aangepaste getalnotatie toont de streepjes zonder de waarde te veranderen. Voor tekst is LINKS(ruw; 3) & "-" & DEEL(ruw; 4; 2) & "-" & RECHTS(ruw; 5) het alternatief: elk stuk komt uit het origineel en er is geen volgordeval, maar je moet wel drie uitsneden bijwerken als de opbouw verandert.

Meer om op te letten

  • isRuw kijkt alleen naar de lengte. Een waarde van tien tekens met een tikfout, zoals VRK250123X, wordt dus toch opgemaakt tot VRK-25-0123X. Of de inhoud klopt, controleer je in een aparte test.
  • Een waarde die met de hand is opgemaakt met een ander scheidingsteken, zoals VRK/25/00412, telt 12 tekens en komt ongewijzigd door, slashes en al. Kan dat voorkomen, trek de scheidingstekens dan eerst gelijk met SUBSTITUEREN, nog vóór SPATIES.WISSEN.

Varianten

=VERVANGEN(VERVANGEN(SPATIES.WISSEN(A2), 6, 0, "-"), 4, 0, "-")
=VERVANGEN(VERVANGEN(SPATIES.WISSEN(A2); 6; 0; "-"); 4; 0; "-")

Dezelfde invoegingen van rechts naar links, zonder LET en zonder de controle op al opgemaakte waarden; goed genoeg als alle invoer kaal is.

=LINKS(SPATIES.WISSEN(A2), 3) & "-" & DEEL(SPATIES.WISSEN(A2), 4, 2) & "-" & RECHTS(SPATIES.WISSEN(A2), 5)
=LINKS(SPATIES.WISSEN(A2); 3) & "-" & DEEL(SPATIES.WISSEN(A2); 4; 2) & "-" & RECHTS(SPATIES.WISSEN(A2); 5)

Het alternatief uit de uitleg: drie stukken uit het origineel, zonder verschuivende posities waar je over moet nadenken.

Veelgemaakte fouten

SymptoomOorzaakOplossing
#WAARDE! bij het vervangen van streepjesJe wilde streepjes vervangen door slashes, schreef VERVANGEN(A2; "-"; 1; "/") en kreeg #WAARDE!.VERVANGEN verwacht een positie waar jij de te zoeken tekst neerzette, en "-" is geen getal. Vervangen op inhoud doe je met SUBSTITUEREN: SUBSTITUEREN(A2; "-"; "/").
Bij invoegen verdwijnt een tekenJe wilde een spatie invoegen, gaf aantal_tekens 1 op, en er verdween een teken.Zet aantal_tekens op 0 als er niets mag verdwijnen.
Het tweede streepje staat één positie te vroegMet twee geneste VERVANGEN-functies voeg je twee streepjes in, en het tweede belandt één positie te vroeg.Na de eerste invoeging schuift elke latere positie één op. Werk van rechts naar links: dan staan de posities die je nog moet gebruiken nog op hun oorspronkelijke plek.