Home › Excel-functies › SUBSTITUEREN
Tekst vervangen in Excel met SUBSTITUEREN
SUBSTITUEREN vervangt bepaalde tekst in een cel door andere tekst. Streepjes uit telefoonnummers halen, punten vervangen door komma's, een terugkerende typfout herstellen: allemaal zonder de oorspronkelijke gegevens aan te raken. In de Engelse Excel heet de functie SUBSTITUTE.
SUBSTITUEREN · In deze uitleg
Syntaxis van SUBSTITUEREN
=SUBSTITUEREN(tekst; oude_tekst; nieuwe_tekst; [rang_getal])| Argument | Betekenis |
|---|---|
| tekst | De tekst of cel die je wilt aanpassen. |
| oude_tekst | De tekst die je zoekt. SUBSTITUEREN is hoofdlettergevoelig. |
| nieuwe_tekst | De vervanging. Gebruik "" om de oude tekst weg te halen. |
| rang_getal (optioneel) | Welke keer je wilt vervangen. Laat het weg om alle keren te vervangen. |
Voorbeelden
| Doel | Formule | Uitkomst |
|---|---|---|
| Streepjes weghalen | =SUBSTITUEREN("06-1234-5678"; "-"; "") | 0612345678 |
| Een woord vervangen | =SUBSTITUEREN("Rapport K1"; "K1"; "K2") | Rapport K2 |
| Alleen de 2e keer | =SUBSTITUEREN("a-b-c"; "-"; "/"; 2) | a-b/c |
| Alle spaties weghalen | =SUBSTITUEREN(A2; " "; "") | Geen enkele spatie |
Meerdere vervangingen tegelijk
Zet SUBSTITUEREN in zichzelf, één laag per vervanging. Om haakjes en streepjes uit een telefoonnummer te halen:
=SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(A2; "("; ""); ")"; ""); "-"; "")"(020) 123-4567" wordt "020 1234567".
Tellen hoe vaak een teken voorkomt
Vergelijk de lengte vóór en na het weghalen van het teken:
=LENGTE(A2) - LENGTE(SUBSTITUEREN(A2; ","; ""))Voor "appel,banaan,kers" geeft dit 2 komma's, dus 3 onderdelen. Meer over LENGTE in de uitleg van LENGTE.
Getallen met het verkeerde scheidingsteken herstellen
Geïmporteerde getallen in Engelse notatie, zoals "1,234.50", zijn in een Nederlandse Excel tekst. Wissel de scheidingstekens om en zet om:
=WAARDE(SUBSTITUEREN(SUBSTITUEREN(A2; ","; ""); "."; ","))Dat geeft het getal 1234,5. Een kortere manier is NUMERIEKE.WAARDE; zie tekst omzetten naar een getal.
SUBSTITUEREN, VERVANGEN of Zoeken en vervangen?
- SUBSTITUEREN vervangt tekst op basis van wat het is: elke "-" in de cel.
- VERVANGEN vervangt tekst op basis van positie:
=VERVANGEN(A2; 1; 3; "")haalt de eerste drie tekens weg. - Ctrl+H (Zoeken en vervangen) verandert de cellen zelf, zonder formule. Sneller voor een eenmalige opschoning.
Veelgestelde vragen
Is SUBSTITUEREN hoofdlettergevoelig?
Ja. SUBSTITUEREN(A2; "abc"; "x") vervangt "ABC" niet. Zet de tekst eerst in KLEINE.LETTERS als hoofdletters niet uitmaken.
Kan SUBSTITUEREN met jokertekens werken?
Nee. Het vervangt alleen exacte tekst. Gebruik Zoeken en vervangen (Ctrl+H) voor vervangingen met jokertekens.
Hoe vervang ik regeleinden?
Regeleinden zijn TEKEN(10): =SUBSTITUEREN(A2; TEKEN(10); ", ") maakt er komma's van.
Wat is SUBSTITUEREN in het Engels?
SUBSTITUTE. En VERVANGEN heet in het Engels REPLACE.