SUBSTITUEREN en VERVANGEN in Excel: tekst vervangen
Bijgewerkt op . Engelse naam: SUBSTITUTE
SUBSTITUEREN vervangt een stukje tekst door andere tekst, bijvoorbeeld =SUBSTITUEREN(A2;"-";"") om alle streepjes uit een code te halen. VERVANGEN doet iets vergelijkbaars, maar op een vaste positie in de tekst. In de Engelse Excel heten ze SUBSTITUTE en REPLACE.
Syntax
=SUBSTITUEREN(tekst; oud_tekst; nieuw_tekst; [rang_getal])
- tekst
- De tekst of cel waarin je iets wilt vervangen, bijvoorbeeld A2.
- oud_tekst
- De tekst die je wilt vervangen, tussen aanhalingstekens. Let op: hoofdlettergevoelig.
- nieuw_tekst
- De tekst die ervoor in de plaats komt. Met "" haal je de oude tekst weg.
- rang_getal(optioneel)
- Welke keer je wilt vervangen als de tekst vaker voorkomt. Laat je het weg, dan worden alle exemplaren vervangen.
Hoe werkt SUBSTITUEREN?
SUBSTITUEREN zoekt in een tekst naar een stukje tekst en zet er iets anders voor in de plaats. Je geeft op waarin je zoekt, wat je zoekt en waarmee je het vervangt:
=SUBSTITUEREN(A2;"straat";"str.")maakt van Kerkstraat 12 de tekst Kerkstr. 12
Standaard vervangt SUBSTITUEREN elke keer dat de tekst voorkomt. Komt de oude tekst helemaal niet voor, dan krijg je de oorspronkelijke tekst terug, zonder foutmelding.
Let op: SUBSTITUEREN is hoofdlettergevoelig. "straat" vervangt niet het woord Straat met een hoofdletter. Wil je beide vervangen, nest dan twee keer SUBSTITUEREN of zet de tekst eerst om met KLEINE.LETTERS.
Tekst weghalen met een lege vervanging
Vervang je iets door een lege tekst "", dan haal je het weg. Zo schoon je snel gegevens op:
=SUBSTITUEREN(A2;"-";"")haalt alle streepjes weg: AB-12-XY wordt AB12XY=SUBSTITUEREN(A2;" ";"")haalt alle spaties weg: 4331 AB wordt 4331AB=SUBSTITUEREN(A2;"€";"")haalt het euroteken uit een geïmporteerd bedrag
Het resultaat is altijd tekst. Wil je daarna rekenen, zet het dan om met WAARDE: =WAARDE(SUBSTITUEREN(A2;"€";"")).
Wil je meerdere tekens weghalen, nest de functie dan: =SUBSTITUEREN(SUBSTITUEREN(A2;"(";"");")";"") haalt zowel het openingshaakje als het sluithaakje weg. Werk van binnen naar buiten: de binnenste SUBSTITUEREN wordt eerst uitgevoerd.
Alleen de tweede of derde keer vervangen
Met het vierde argument, rang_getal, vervang je maar één exemplaar. Staat in A2 de code NL-ZLD-0042-B en wil je alleen het derde streepje vervangen door een spatie, dan typ je:
=SUBSTITUEREN(A2;"-";" ";3)geeft NL-ZLD-0042 B
De eerste twee streepjes blijven staan. Zonder dat vierde argument zouden alle drie de streepjes vervangen worden.
Dit is handig bij codes en bestandsnamen die uit vaste delen bestaan. Je kunt het rang_getal ook berekenen. Met LENGTE en SUBSTITUEREN tel je eerst hoe vaak een teken voorkomt, en dat getal gebruik je om precies de laatste keer te vervangen.
Verschil tussen SUBSTITUEREN en VERVANGEN
Beide functies vervangen tekst, maar ze werken op een andere manier:
- SUBSTITUEREN zoekt op inhoud. Je zegt wélke tekst weg moet en Excel zoekt waar die staat.
- VERVANGEN werkt op positie. Je zegt vanaf welk teken en hoeveel tekens er vervangen moeten worden, wat er ook staat.
De syntax van VERVANGEN is =VERVANGEN(oud_tekst; begin_getal; aantal_tekens; nieuw_tekst). Met =VERVANGEN(A2;1;4;"****") vervang je de eerste vier tekens door sterretjes, bijvoorbeeld om een klantnummer deels te verbergen.
Gebruik SUBSTITUEREN als je weet wat er moet veranderen, en VERVANGEN als je weet waar het staat. Met aantal_tekens 0 voeg je met VERVANGEN tekst in zonder iets weg te halen: =VERVANGEN(A2;5;0;" ") zet een spatie na het vierde teken, zodat 4331AB weer 4331 AB wordt.
SUBSTITUEREN of Zoeken en vervangen?
Voor een eenmalige klus kun je ook Zoeken en vervangen gebruiken. Selecteer de kolom, druk op Ctrl + H, vul in wat je zoekt en waarmee je het vervangt, en klik op Alles vervangen. Excel past de cellen dan direct aan, zonder hulpkolom.
De functie SUBSTITUEREN is beter als de gegevens regelmatig opnieuw binnenkomen, bijvoorbeeld uit een export. De formule werkt dan automatisch op de nieuwe gegevens. Ook blijven de oorspronkelijke gegevens bewaard, wat handig is als je iets wilt controleren.
Let op bij Zoeken en vervangen: daar zijn * en ? jokertekens. Wil je een echt sterretje vervangen, zoek dan op ~*. Bij SUBSTITUEREN zijn * en ? gewoon tekens.
Voorbeelden
Streepjes uit een artikelcode halen
Een leverancier gebruikt codes met streepjes, je eigen systeem zonder. Je wilt de codes omzetten.
=SUBSTITUEREN(A2;"-";"") in cel B2
| A | B | C | |
|---|---|---|---|
| 1 | Code leverancier | Eigen code | |
| 2 | AB-12-XY | AB12XY | |
| 3 | |||
| 4 |
Klik op cel B2 om de formule te zien of aan te passen.
Alle streepjes worden vervangen door niets: AB12XY.
E-maildomein aanpassen na een naamswijziging
Het bedrijf heeft een nieuwe naam en dus een nieuw e-maildomein. Je past de adressen in één keer aan.
=SUBSTITUEREN(A2;"@oudebakkerij.nl";"@bakkerijdeboer.nl") in cel B2
| A | B | C | |
|---|---|---|---|
| 1 | Oud adres | Nieuw adres | |
| 2 | lisa@oudebakkerij.nl | lisa@bakkerijdeboer.nl | |
| 3 | |||
| 4 |
Klik op cel B2 om de formule te zien of aan te passen.
Het deel na het apenstaartje wordt vervangen: lisa@bakkerijdeboer.nl.
Een spatie in een postcode zetten met VERVANGEN
Postcodes zijn zonder spatie ingevoerd. Je wilt een spatie tussen de cijfers en de letters.
=VERVANGEN(A2;5;0;" ") in cel B2
| A | B | C | |
|---|---|---|---|
| 1 | Ingevoerd | Met spatie | |
| 2 | 4331AB | 4331 AB | |
| 3 | |||
| 4 |
Klik op cel B2 om de formule te zien of aan te passen.
VERVANGEN haalt op positie 5 nul tekens weg en zet er een spatie neer: 4331 AB.
Veelgemaakte fouten
Hoofdletters verschillen
SUBSTITUEREN is hoofdlettergevoelig, dus "straat" vervangt geen "Straat". Nest twee SUBSTITUEREN-functies of zet de tekst eerst om met KLEINE.LETTERS.
Rekenen met het resultaat
SUBSTITUEREN geeft tekst terug, ook als het eruitziet als een getal. Zet het om met WAARDE als je ermee wilt rekenen.
SUBSTITUEREN en VERVANGEN door elkaar halen
VERVANGEN verwacht een positie en een aantal tekens, geen zoektekst. Wil je op inhoud vervangen, gebruik dan SUBSTITUEREN.
Oefen het zelf
Lezen is één, het zelf typen is wat blijft hangen. Deze lessen gaan erover:
Veelgestelde vragen
Hoe vervang ik tekst in een cel met een formule?
Gebruik =SUBSTITUEREN(A2;"oud";"nieuw"). Excel vervangt elke keer dat de oude tekst voorkomt.
Wat is het verschil tussen SUBSTITUEREN en VERVANGEN?
SUBSTITUEREN vervangt op basis van de tekst zelf. VERVANGEN vervangt op basis van de positie, met een beginpositie en een aantal tekens.
Hoe verwijder ik alle spaties uit een cel?
Gebruik =SUBSTITUEREN(A2;" ";""). Wil je alleen de overbodige spaties weghalen en één spatie tussen woorden houden, gebruik dan SPATIES.WISSEN.
Is SUBSTITUEREN hoofdlettergevoelig?
Ja. Een a en een A zijn voor SUBSTITUEREN verschillende tekens. Zet de tekst eventueel eerst om met KLEINE.LETTERS.
Hoe heten SUBSTITUEREN en VERVANGEN in het Engels?
SUBSTITUEREN heet SUBSTITUTE en VERVANGEN heet REPLACE.