Naar de inhoud

Excel-functie · in Engelse Excel SUBSTITUTE

SUBSTITUERENin Excel

SUBSTITUEREN vervangt in een tekst elk voorkomen van een stukje tekst door iets anders. Zo haal je streepjes uit telefoonnummers, vervang je een oude afdelingsnaam door een nieuwe of tel je hoe vaak een teken voorkomt. In Engelse Excel heet de functie SUBSTITUTE.

=SUBSTITUEREN(tekst; oud_tekst; nieuw_tekst; [rang_getal])Engelse Excel: =SUBSTITUTE(text, old_text, new_text, [instance_num])

De syntaxis van SUBSTITUEREN

Nederlandse Excel: =SUBSTITUEREN(tekst; oud_tekst; nieuw_tekst; [rang_getal]). Engelse Excel: =SUBSTITUTE(text, old_text, new_text, [instance_num]).

  • tekst: de tekst of cel waarin je iets wilt vervangen.
  • oud_tekst: wat er weg moet, tussen aanhalingstekens.
  • nieuw_tekst: wat ervoor in de plaats komt. Met "" haal je de oude tekst gewoon weg.
  • rang_getal: welk voorkomen je wilt vervangen. Laat je het weg, dan vervangt Excel ze allemaal.

Met Nederlandse landinstellingen scheid je de argumenten met een puntkomma, ook als je Excel in het Engels gebruikt.

Voorbeeld: streepjes uit telefoonnummers

Telefoonnummers staan in kolom A als 06-12345678. Voor een import heb je ze nodig zonder streepje. =SUBSTITUEREN(A2;"-";"") geeft 0612345678.

Moeten er ook spaties uit, zet de functie dan in zichzelf: =SUBSTITUEREN(SUBSTITUEREN(A2;"-";"");" ";""). De binnenste haalt de streepjes weg, de buitenste daarna de spaties.

Meer toepassingen

  • =SUBSTITUEREN(A2;"Verkoop";"Sales") geeft een afdeling een nieuwe naam.
  • =SUBSTITUEREN(A2;"-";"/";2) vervangt alleen het tweede streepje.
  • =LENGTE(A2)-LENGTE(SUBSTITUEREN(A2;",";"")) telt hoeveel komma's er in een cel staan, en dus hoeveel items er in een lijst zitten (plus één).

SUBSTITUEREN let op hoofdletters: met "verkoop" wordt Verkoop niet vervangen.

SUBSTITUEREN of VERVANGEN?

SUBSTITUEREN zoekt op de tekst zelf: vervang elk streepje, ongeacht waar het staat. VERVANGEN werkt met een positie: vervang vanaf teken 4 drie tekens. Gebruik VERVANGEN als je weet waar iets staat, en SUBSTITUEREN als je weet wat er staat.

Voor eenmalig vervangen in een heel blad kun je ook Ctrl+H (Zoeken en vervangen) gebruiken. Dat verandert de gegevens zelf; SUBSTITUEREN laat het origineel staan.

Praktijkvoorbeelden met SUBSTITUEREN

  • Een punt als decimaalteken uit een export omzetten: =WAARDE(SUBSTITUEREN(A2;".";",")) maakt van de tekst 12.50 een getal, als je Nederlandse landinstellingen hebt.
  • Spaties in een productcode vervangen door een liggend streepje: =SUBSTITUEREN(A2;" ";"_").
  • Een naam invullen in een standaardtekst: =SUBSTITUEREN("Beste [naam],";"[naam]";B2).
  • BV overal schrijven als B.V.: =SUBSTITUEREN(A2;" BV";" B.V.").

Wil je meerdere vervangingen achter elkaar, zet de functies dan in elkaar. Excel voert eerst de binnenste uit en werkt dan naar buiten.

Veelgemaakte fouten met SUBSTITUEREN

Er gebeurt niets

Waarschijnlijk komt de oude tekst niet precies zo voor. SUBSTITUEREN is hoofdlettergevoelig en een extra spatie maakt ook verschil. Controleer de schrijfwijze in de cel.

Het resultaat is tekst

Ook als er alleen cijfers overblijven, is de uitkomst tekst. Bij telefoonnummers is dat juist goed, want dan blijft de 0 aan het begin staan. Wil je rekenen met het resultaat, zet er dan WAARDE omheen.

Aanhalingstekens vergeten

De oude en nieuwe tekst staan altijd tussen aanhalingstekens. "" (twee aanhalingstekens zonder iets ertussen) betekent: niets.

Ook stukjes van woorden worden vervangen

SUBSTITUEREN kijkt niet naar hele woorden. Vervang je en door &, dan verandert ook het woord keuken. Neem spaties mee in de zoektekst, zoals " en ", om alleen het losse woord te raken.

Vragen over SUBSTITUEREN

Wat is SUBSTITUTE in het Nederlands?

In Nederlandse Excel heet SUBSTITUTE SUBSTITUEREN. REPLACE heet VERVANGEN.

Hoe vervang ik meerdere tekens tegelijk?

Zet SUBSTITUEREN in elkaar, één keer per teken: =SUBSTITUEREN(SUBSTITUEREN(A2;"(";"");")";"") haalt beide haakjes weg.

Hoe haal ik alle spaties uit een cel?

Met =SUBSTITUEREN(A2;" ";""). Wil je alleen de overbodige spaties weg, gebruik dan SPATIES.WISSEN.

Hoe tel ik hoe vaak een woord in een cel staat?

Vergelijk de lengte met en zonder dat woord en deel door de lengte van het woord: =(LENGTE(A2)-LENGTE(SUBSTITUEREN(A2;"koffie";"")))/LENGTE("koffie"). Let op: SUBSTITUEREN is hoofdlettergevoelig.