Tekst vervangen met SUBSTITUTE

Een veelgebruikte truc is iets vervangen door niets om het te verwijderen. Dat doe je door new_text in te stellen op een lege tekenreeks "".

Als A2 555-123-4567 bevat, verwijdert de onderstaande formule elk streepje en levert deze 5551234567 op.

Met dit patroon maak je telefoonnummers, ID's en andere waarden met leestekens schoon.

SUBSTITUTE werkt ook met volledige woorden, niet alleen met losse tekens.

Als A2 Jaarverslag 2023 bevat, kun je het jaar bijwerken door het woord te vervangen:

Het resultaat is Jaarverslag 2024. Elk overeenkomend voorkomen in de cel wordt gewijzigd.

Belangrijk: SUBSTITUTE vergelijkt tekst exact, inclusief het gebruik van hoofdletters.

Als A2 Kat kat KAT bevat, wijzigt =SUBSTITUTE(A2, "cat", "dog") alleen de kleine letters kat, met als resultaat Kat hond KAT.

Als je tekst wilt vervangen zonder onderscheid tussen hoofdletters en kleine letters, normaliseer je eerst het hoofdlettergebruik met LOWER of UPPER, of gebruik je de regex-functies uit de volgende les in Sheets.

Met het optionele vierde argument, instance_num, kun je één specifiek voorkomen kiezen in plaats van alle voorkomens.

Als A2 a-b-c-d bevat en je alleen het tweede streepje wilt wijzigen, stel je instance_num in op 2.

Het resultaat is a-b/c-d: alleen het tweede streepje is een schuine streep geworden.

Stel dat een geïmporteerd bedrag als tekst is opgeslagen: $1,250.00. Om er een bruikbaar getal van te maken, moet je het dollarteken en de komma verwijderen.

Nest twee aanroepen van SUBSTITUTE, de ene binnen de andere, om beide tekens te verwijderen.

De binnenste aanroep verwijdert de komma; de buitenste aanroep verwijdert het dollarteken, zodat 1250.00 overblijft.

Je kunt zoveel aanroepen van SUBSTITUTE nesten als je nodig hebt, waarbij elke aanroep een ander teken opschoont.

Om rommelige code met spaties, punten en schuine strepen om te zetten in nette streepjes, koppel je drie vervangingen aan elkaar.

Elke laag geeft het resultaat door aan de volgende en bouwt zo de uiteindelijke opgeschoonde tekenreeks op.

Verwar SUBSTITUTE niet met de vergelijkbare functie REPLACE.

• SUBSTITUTE vervangt tekst door de inhoud ervan te vergelijken (zoek dit woord en vervang het).
• REPLACE vervangt tekst op basis van de positie (vervang 4 tekens vanaf positie 5).

Gebruik SUBSTITUTE wanneer je weet wat je wilt vinden, en REPLACE wanneer je weet waar het staat.

Een slimme extra mogelijkheid: combineer SUBSTITUTE met LEN om te tellen hoe vaak iets voorkomt.

Trek de lengte na het verwijderen van een teken af van de oorspronkelijke lengte. Als A2 a,b,c,d bevat, tel je hiermee de komma's en krijg je 3.

Deze truc is handig om items in een lijst met scheidingstekens te tellen.

Stel dat A2 rommelige getallen met punten en spaties bevat, zoals 555.123 4567. Je wilt een consistente notatie met streepjes: 555-123-4567.

Verwijder eerst de punten en spaties en voeg daarna eventueel opnieuw streepjes toe; voorlopig maak je de scheidingstekens eenvoudig allemaal gelijk aan streepjes.

Door vervangingen te nesten, maak je alle afwijkende scheidingstekens standaard.

Je hebt geleerd hoe je tekst in formules zoekt en vervangt:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Gebruik "" als new_text om tekens te verwijderen.
• De functie is hoofdlettergevoelig; normaliseer het hoofdlettergebruik eerst als dat nodig is.
• Met het optionele instance_num kies je één specifiek voorkomen.
• Nest aanroepen om meerdere tekens op te schonen en combineer de functie met LEN om voorkomens te tellen.

Je hebt Tekst samenvoegen en wijzigen afgerond. Goed gedaan met het opbouwen van schone, consistente gegevens!