VERVANG Functie Excel - Tekst zoeken en vervangen

Voorbeeldwerkboek downloaden

Download het voorbeeldwerkboek

Deze zelfstudie laat zien hoe u de Excel VERVANG Functie in Excel om tekst te zoeken en te vervangen.

SUBSTITUTE Functie Overzicht

De SUBSTITUTE-functie Zoekt en vervangt bestaande tekst door een nieuwe tekenreeks. Hoofdlettergevoelig.

Om de SUBSTITUTE Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:

(Let op hoe de formule-invoer verschijnt)

SUBSTITUTE-functie Syntaxis en ingangen:

1	=VERVANGING(tekst,oude_tekst,nieuwe_tekst,instance_num)

tekst - De originele tekstreeks.

oude_tekst - De tekstreeks die u wilt zoeken en vervangen.

nieuwe_test - De vervangende tekst.

instance_num - Optioneel. Het instantienummer dat moet worden vervangen. Indien leeg, worden alle instanties vervangen.

Wat is VERVANGING?

De SUBSTITUTE-functie in Excel wordt gebruikt om een ​​reeks tekens in een tekstreeks te vervangen door een andere opgegeven tekstreeks.

Laten we eens kijken naar het volgende voorbeeld waarin de tekenreeks "snel bruin" wordt vervangen door "langzaam wit".

1	=VERVANGING(C2,C3,C4)

SUBSTITUTE zoekt in de string in C2, vindt "snel bruin" en vervangt dit door "langzaam wit".

Hoe SUBSTITUTE te gebruiken?

De SUBSTITUTE-functie heeft 3 vereiste argumenten en 1 optionele:

• Tekst: de tekst die u tekens wilt vervangen
• Old_text: de tekst die u wilt vervangen
• Nieuwe_tekst: de tekst waarmee u Oude_tekst wilt vervangen
• Instance_num (optioneel): Specificeert de instantie van Old_text die u wilt vervangen door New_text. Standaard worden alle exemplaren van Old_text vervangen als er geen instantie is opgegeven.

Nesten VERVANGING

Wat als we twee delen van dezelfde string moesten vervangen? Laten we eens kijken hoe we twee SUBSTITUTE-functies zouden combineren.

1	=VERVANGING(VERVANGING(C2,C3,C4),C5,C6)

Merk op hoe het resultaat van SUBSTITUTE(C2,C3,C4) wordt gebruikt als het eerste argument voor de tweede SUBSTITUTE. Als we de formule uit elkaar halen:

=VERVANGING(C2,C3,C4) resulteert in “De langzaam wit vos springt over de luie hond”.

Vervolgens SUBSTITUTE ("De langzame witte vos springt over de luie hond", C5,C6) wat resulteert in "De langzame witte vos Leeuw springt over de luie hond”.

Verschil tussen VERVANGEN en VERVANGEN

Een soortgelijke functie als SUBSTITUTE is REPLACE. In de meeste gevallen kunnen deze door elkaar worden gebruikt, maar de volgende vuistregel is van toepassing.

De SUBSTITUTE-functie moet worden gebruikt wanneer de reeks tekst vervangen worden is bekend, of een heel woord of woorden worden vervangen, zoals ons eerder gebruikte voorbeeld van een vos.

1	=VERVANGING(C2,C3,C4)

De REPLACE-functie moet worden gebruikt wanneer de positie van de teksttekens te vervangen in de string zijn bekend, of wanneer een deel van een woord of tekenreeks wordt vervangen. Bijvoorbeeld het verwijderen van een koppelteken uit een reeks cijfers.

De REPLACE-functie kan ook worden genest zoals SUBSTITUTE, maar het is belangrijk op te merken dat de relatieve positie van het startteken kan veranderen op basis van de lengte van de string die moet worden vervangen in de eerste stap van de geneste formule.

Als u bijvoorbeeld 'snel bruin' vervangt door 'langzaam wit', vervangt u 11 tekens door 10 tekens. Daarom zou, indien genest, het startteken voor het tweede REPLACE 1 teken afwijken van de initiële tekenposities.

VERVANG Tips

• SUBSTITUTE kan worden gebruikt op substrings:
• Onthoud dat u een instance_num moet opgeven als u een reeks tekens probeert te vervangen die in de tekenreeks worden herhaald:

Merk op dat de 3030 onjuist is, aangezien zowel "20" als "20" in "2020" zijn vervangen door "30".

Geïnteresseerd in meer tekstfuncties?

Zie onze andere artikelen voor meer informatie over VERVANGEN, of hoe andere tekstfuncties in Excel zoals LEFT, MID en RIGHT worden gebruikt.

VERVANG in Google Spreadsheets

De SUBSTITUTE-functie werkt in Google Spreadsheets precies hetzelfde als in Excel:

extra notities

De SUBSTITUTE-functie is hoofdlettergevoelig!. U kunt de LOWER- of UPPER-functies gebruiken om uw tekstreeksen om te zetten in consistente hoofdletters voordat u de SUBSTITUTE-functie gebruikt. Vervolgens kunt u, indien gewenst, de PROPER-functie gebruiken om de eerste letter van elk woord als hoofdletter te gebruiken.

Als alternatief kunt u de functies ZOEKEN en VERVANGEN gebruiken om een ​​niet-hoofdlettergevoelige VERVANGING te simuleren. Gebruik de SEARCH-functie om de startpositie van de tekstreeks te vinden. De SEARCH-functie is niet hoofdlettergevoelig. Gebruik vervolgens de REPLACE-functie om de tekst te vervangen. U moet het resultaat van de SEARCH-functie gebruiken als de start_num-invoer in REPLACE. U moet ook het aantal te vervangen tekens definiëren ( num_chars). U kunt ze handmatig tellen of de LEN-functie gebruiken om het aantal tekens te tellen. Voorbeeld:

Vervangen - Koppeltekens verwijderen

Een veel voorkomende vraag is hoe u koppeltekens uit tekst kunt verwijderen. De truc is om het koppelteken door niets te vervangen.

Laten we voor dit voorbeeld aannemen dat cel A1 tekst bevat met koppeltekens erin. Hier leest u hoe u ze kunt verwijderen

1. Typ in een lege helpercel =VERVANGING(
2. Klik op de cel waarin u tekens wilt vervangen of A1 (dit vult automatisch uw formule)
3. Typ, (komma)
4. Typ "-"
5. Typ, (komma)
6. Typ "" en druk op enter
Je eindresultaat in je helpercel zou er als volgt uit moeten zien:
=VERVANGING(A1,”-“,””)

3 Meer vervangende voorbeelden:

Als we naar cel B3 kijken, willen we het woord 'ziek' vervangen door 'groot'. We kunnen de volgende uitdrukking gebruiken:

1	=VERVANGING(B3,"ziek","groot",1)

Als de tekst die we willen vervangen meer dan eens voorkomt, moeten we de instantie specificeren die we bedoelen. In het tweede voorbeeld hebben we twee keer dat het woord "oud" voorkomt. Dus als we de tweede instantie willen veranderen in het woord "grijs", dan moeten we gebruiken:

1	=VERVANGING(B4,"oud","grijs",2)

Houd er rekening mee dat als het voorkomen niet is opgegeven, alle exemplaren worden vervangen. Dus:

1	=VERVANGING(B4,"oud","grijs")

Zou alle gevallen van het woord "oud" verwijderen en ze vervangen door het woord "grijs". Merk op dat als de oude tekst niet kan worden gevonden, de string ongewijzigd blijft. Dus kijkend naar het laatste voorbeeld:

1	=VERVANGING(B5,"zwart","grijs",1)

Betekent dat we proberen het woord "zwart" te vervangen door het woord "grijs". Omdat "zwart" echter niet voorkomt, bleef de originele snaar levitra verzonden in de Verenigde Staten ongewijzigd:

Klik hier om het .XLSX-bestand uit dit artikel te downloaden

VERVANGENDE Voorbeelden in VBA

U kunt ook de SUBSTITUTE-functie in VBA gebruiken. Type:
application.worksheetfunction.substitute(tekst,oude_tekst,nieuwe_tekst,instance_num)
Voor de functieargumenten (tekst, enz.), kunt u ze ofwel rechtstreeks in de functie invoeren, of variabelen definiëren om in plaats daarvan te gebruiken.

Wanneer de volgende code wordt uitgevoerd

1	Bereik ("A1") = Werkbladfunctie. Vervang (Bereik ("A1"), "gebruiken", "", 1)

De inhoud van cel A1 wordt:

Dus het woord "gebruiken" werd vervangen door een lege tekenreeks. De laatste parameter (1) geeft aan welk exemplaar van de tekst moet worden vervangen, als de tekst meer dan één keer voorkomt. Dus als we bijvoorbeeld de volgende instructie uitvoeren:

1	Bereik ("A1") = Werkbladfunctie. Vervang (Bereik ("A1"), "de", "een", 1)

De A1-cel ziet er als volgt uit:

Merk op dat het eerste voorkomen van "de" werd vervangen door "an", terwijl het tweede "de" niet werd beïnvloed. Als we de laatste parameter hadden weggelaten, zouden beide "de" woorden zijn vervangen

Keer terug naar de lijst met alle functies in Excel