Wanneer u in Excel met tekstgegevens werkt, moet u mogelijk de tekst voor of na een specifiek teken of een bepaalde tekenreeks verwijderen.
Als u bijvoorbeeld de naam en aanduidingsgegevens van personen heeft, wilt u wellicht de aanduiding na de komma verwijderen en alleen de naam behouden (of omgekeerd waar u de aanduiding behoudt en de naam verwijdert).
Soms kan het worden gedaan met een eenvoudige formule of snel zoeken en vervangen, en soms zijn er complexere formules of tijdelijke oplossingen nodig.
In deze zelfstudie laat ik u zien hoe u de tekst voor of na een specifiek teken in Excel verwijdert (met behulp van verschillende voorbeelden).
Laten we daarom beginnen met enkele eenvoudige voorbeelden.
Tekst verwijderen na een teken met Zoeken en vervangen
Als u snel alle tekst wilt verwijderen na een specifieke tekenreeks (of vóór een tekenreeks), kunt u dat doen met behulp van Zoeken en vervangen en jokertekens.
Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt de aanduiding na de komma verwijderen en de tekst vóór de komma behouden.
Hieronder staan de stappen om dit te doen:
- Kopieer en plak de gegevens van kolom A naar kolom B (dit is om ook de originele gegevens te behouden)
- Klik met de cellen in kolom B geselecteerd op het tabblad Start
- Klik in de groep Bewerken op de optie Zoeken en selecteren
- Klik in de opties die in de vervolgkeuzelijst verschijnen op de optie Vervangen. Dit opent het dialoogvenster Zoeken en vervangen
- Typ in het veld 'Zoeken naar' ,* (d.w.z. komma gevolgd door een sterretje)
- Laat het veld 'Vervangen door' leeg
- Klik op de knop Alles vervangen
De bovenstaande stappen zouden de komma in de dataset vinden en alle tekst na de komma verwijderen (inclusief de komma).
Aangezien dit de tekst uit de geselecteerde cellen vervangt, is het een goed idee om de tekst naar een andere kolom te kopiëren en vervolgens deze zoek- en vervangingsbewerking uit te voeren, of een reservekopie van uw gegevens te maken zodat u de originele gegevens intact heeft.
Hoe werkt dit?
* (sterretje) is een jokerteken dat een willekeurig aantal tekens kan vertegenwoordigen.
Wanneer ik het na de komma gebruik (in het veld 'Zoeken naar') en vervolgens op de knop Alles vervangen klik, vindt het de eerste komma in de cel en beschouwt het als een overeenkomst.
Dit komt omdat het sterretje (*)-teken wordt beschouwd als een overeenkomst met de volledige tekstreeks die volgt op de komma.
Dus wanneer u op de knop Alles vervangen drukt, vervangt deze de komma en alle tekst die erop volgt.
Opmerking: Deze methode werkt goed dan heb je maar één komma in elke cel (zoals in ons voorbeeld). Als je meerdere komma's hebt, zou deze methode altijd de eerste komma vinden en daarna alles verwijderen. U kunt deze methode dus niet gebruiken als u alle tekst na de tweede komma wilt vervangen en de eerste wilt behouden zoals deze is.Als u alle tekens vóór de komma wilt verwijderen, wijzigt u het veld Zoeken naar door het sterretje voor de komma te plaatsen (*, in plaats van ,*)
Tekst verwijderen met formules
Als u meer controle nodig hebt over het zoeken en vervangen van tekst voor of na een specifiek teken, kunt u beter de ingebouwde tekstformules in Excel gebruiken.
Stel dat u de onderstaande gegevensset hebt waar u alle tekst na de komma wilt verwijderen.
Hieronder is de formule om dit te doen:
=LINKS(A2,VINDEN(",",A2)-1)
De bovenstaande formule gebruikt de FIND-functie om de positie van de komma in de cel te vinden.
Dit positienummer wordt vervolgens gebruikt door de LEFT-functie om alle tekens vóór de komma te extraheren. Omdat ik geen komma als onderdeel van het resultaat wil, heb ik 1 afgetrokken van de resulterende waarde van de Find-formule.
Dit was een eenvoudig scenario.
Laten we een enigszins complexe nemen.
Stel dat ik deze onderstaande dataset heb waar ik alle tekst na de tweede komma wil verwijderen.
Hier is de formule die dit zal doen:
=LINKS(A2,VINDEN("!",VERVANGING(A2,",","!",2))-1)
Aangezien deze dataset meerdere komma's heeft, kan ik de FIND-functie niet gebruiken om de positie van de eerste komma te krijgen en alles links ervan te extraheren.
Ik moet op de een of andere manier de positie van de tweede komma achterhalen en dan alles extraheren dat links van de tweede komma staat.
Om dit te doen, heb ik de SUBSTITUTE-functie gebruikt om de tweede komma in een uitroepteken te veranderen. Dit geeft me een uniek karakter in de cel. Ik kan nu de positie van het uitroepteken gebruiken om alles links van de tweede komma te extraheren.
Deze positie van het uitroepteken wordt gebruikt in de LEFT-functie om alles voor de tweede komma te extraheren.
Tot zover goed!
Maar wat als er een inconsistent aantal komma's in de dataset staat?
In de onderstaande gegevensset hebben sommige cellen bijvoorbeeld twee komma's en sommige cellen hebben drie komma's, en ik moet alle tekst extraheren vóór de laatste komma.
In dit geval moet ik op de een of andere manier de positie van de laatste plaats van de komma achterhalen en dan alles links ervan extraheren.
Hieronder is de formule die dat zal doen
=LEFT(A2,FIND("!",VERVANG(A2,",","!",LEN(A2)-LEN(VERVANG(A2,",",""))))-1)
De bovenstaande formule gebruikt de LEN-functie om de totale lengte van de tekst in de cel te vinden, evenals de lengte van de tekst zonder de komma.
Wanneer ik deze twee waarden aftrek, krijg ik het totale aantal komma's in de cel.
Dus het zou me 3 geven voor cel A2 en 2 voor cel A4.
Deze waarde wordt vervolgens gebruikt in de SUBSTITUTE-formule om de laatste komma te vervangen door een uitroepteken. En dan kun je de linkerfunctie gebruiken om alles uit te pakken wat links van het uitroepteken staat (waar de laatste komma stond)
Zoals u in de voorbeelden kunt zien, kunt u met een combinatie van tekstformules veel verschillende situaties aan.
Omdat het resultaat is gekoppeld aan de oorspronkelijke gegevens, wordt het resultaat automatisch bijgewerkt wanneer u de oorspronkelijke gegevens wijzigt.
Tekst verwijderen met Flash Fill
Flash Fill is een tool die in Excel 2013 is geïntroduceerd en daarna in alle versies beschikbaar is.
Het werkt door patronen te identificeren wanneer u de gegevens handmatig invoert en vervolgens geëxtrapoleerd om u de gegevens voor de hele kolom te geven.
Dus als u de tekst voor of na een specifiek teken wilt verwijderen, hoeft u alleen maar Flash Fairy te laten zien hoe het resultaat eruit zou zien (door het een paar keer handmatig in te voeren), en Flash Fill zou automatisch het patroon begrijpen en u de resultaten.
Laat me je dit laten zien met een voorbeeld.
Hieronder heb ik de dataset waar ik alle tekst na de komma wil verwijderen.
Hier zijn de stappen om dit te doen met behulp van Flash Fill:
- Voer in cel B2, de aangrenzende kolom van onze gegevens, handmatig 'Jeffery Haggins' in (wat het verwachte resultaat is)
- Voer in cel B3 'Tim Scott' in (wat het verwachte resultaat is voor de tweede cel)
- Selecteer het bereik B2:B10
- Klik op het tabblad Start
- Klik in de groep Bewerken op de vervolgkeuzelijst Vullen
- Klik op Flash Fill
De bovenstaande stappen geven u het resultaat zoals hieronder weergegeven:
U kunt ook de sneltoets Flash Fill gebruiken Controle + E na het selecteren van de cellen in de resultatenkolom (kolom B in ons voorbeeld)
Flash Fill is een geweldig hulpmiddel en in de meeste gevallen is het in staat om het patroon te herkennen en u het juiste resultaat te geven. maar in sommige gevallen kan het het patroon mogelijk niet correct herkennen en kan het u de verkeerde resultaten geven.
Zorg er dus voor dat u uw Flash Fill-resultaten dubbel controleert
En net zoals we alle tekst na een specifiek teken hebben verwijderd met behulp van Flash Fill, kunt u dezelfde stappen gebruiken om de tekst vóór een specifiek teken te verwijderen. laat gewoon flash-fill zien hoe het resultaat eruit moet zien als mijn internet handmatig in de aangrenzende kolom, en het zou de rest doen.
Tekst verwijderen met VBA (aangepaste functie)
Het hele concept van het verwijderen van de tekst voor of na een bepaald teken is afhankelijk van het vinden van de positie van dat teken.
Zoals hierboven te zien is, betekent het vinden van het laatste voorkomen van dat karakter goed het gebruik van een verzinsel van formules.
Als dit iets is dat u vaak moet doen, kunt u dit proces vereenvoudigen door een aangepaste functie te maken met VBA (door de gebruiker gedefinieerde functies genoemd).
Eenmaal gemaakt, kunt u deze functie steeds opnieuw gebruiken. Het is ook een stuk eenvoudiger en gemakkelijker te gebruiken (omdat het meeste zware werk wordt gedaan door de VBA-code aan de achterkant).
Hieronder de VBA-code die u kunt gebruiken om de aangepaste functie in Excel te maken:
Functie LastPosition(rCell As Range, rChar As String) 'Deze functie geeft de laatste positie van het opgegeven teken 'Deze code is ontwikkeld door Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len(rCell) Voor i = rLen Naar 1 Stap -1 If Mid(rCell, i - 1, 1) = rChar Dan LastPosition = i - 1 Functie verlaten End If Next i Functie beëindigen
Je moet de VBA-code in een reguliere module in de VB Editor plaatsen, of in de Personal Macro Workbook. Zodra u het daar hebt, kunt u het gebruiken als elke andere normale werkbladfunctie in de werkmap.
Deze functie heeft 2 argumenten:
- De celverwijzing waarvoor u het laatste exemplaar van een specifiek teken of tekstreeks wilt vinden
- Het teken of de tekstreeks waarvan u de positie moet vinden
Stel je hebt nu onderstaande dataset en je wilt alle tekst na de laatste komma verwijderen en alleen de tekst voor de laatste komma.
Hieronder is de formule die dat zal doen:
=LINKS(A2,Laatste Positie(A2,",")-1)
In de bovenstaande formule heb ik gespecificeerd om de positie van de laatste komma te vinden. Als u de positie van een ander teken of tekstreeks wilt vinden, moet u dat als het tweede argument in de functie gebruiken.
Zoals je kunt zien, is dit een stuk korter en gemakkelijker te gebruiken, in vergelijking met de lange-tekstformule die we in de vorige sectie hebben gebruikt
Als u de VBA-code in een module in een werkmap plaatst, kunt u deze aangepaste functie alleen in die specifieke werkmap gebruiken. Als u dit in alle werkmappen op uw systeem wilt gebruiken, moet u deze code kopiëren en plakken in de persoonlijke macrowerkmap.
Dit zijn dus enkele van de e-mails die u kunt gebruiken om snel de tekst voor of na een specifiek teken in Excel te verwijderen.
Als het een eenvoudig eenmalig iets is, dan kunt u dat doen met behulp van zoeken en vervangen. wat als het een beetje ingewikkelder is, dan moet je een combinatie van ingebouwde Excel-formules gebruiken, of zelfs je eigen aangepaste formule maken met VBA.
Ik hoop dat je deze tutorial nuttig vond.
