Excel heeft een reeks TEKST-functies die wonderen kunnen doen. Met deze functies kunt u allerlei bewerkingen voor tekstsegmenten en dobbelstenen uitvoeren.
Een van de algemene taken voor mensen die met tekstgegevens werken, is het extraheren van een subtekenreeks in Excel (d.w.z. psrt van de tekst uit een cel halen).
Helaas is er geen substring-functie in Excel die dit gemakkelijk kan doen. Dit kan echter nog steeds worden gedaan met behulp van tekstformules en enkele andere ingebouwde Excel-functies.
Laten we eerst eens kijken naar enkele van de tekstfuncties die we in deze tutorial zullen gebruiken.
Excel TEKST-functies
Excel heeft een reeks tekstfuncties die het heel gemakkelijk zouden maken om een subtekenreeks uit de originele tekst in Excel te extraheren. Dit zijn de Excel-tekstfuncties die we in deze zelfstudie zullen gebruiken:
- RECHTS-functie: extraheert het opgegeven aantal tekens aan de rechterkant van de tekstreeks.
- LEFT-functie: extraheert het opgegeven aantal tekens aan de linkerkant van de tekstreeks.
- MID-functie: haalt het opgegeven aantal tekens uit de opgegeven startpositie in een tekstreeks.
- FIND-functie: Vindt de startpositie van de opgegeven tekst in de tekstreeks.
- LEN-functie: Retourneert het aantal tekens in de tekenreeks.
Extraheer een subtekenreeks in Excel met behulp van functies
Stel dat u een dataset heeft zoals hieronder weergegeven:
Dit zijn enkele willekeurige (maar superheld-achtige) e-mail-ID's (behalve de mijne), en in de onderstaande voorbeelden laat ik u zien hoe u de gebruikersnaam en domeinnaam kunt extraheren met behulp van de tekstfuncties in Excel.
Voorbeeld 1 - Gebruikersnamen extraheren uit e-mail-ID's
Bij het gebruik van tekstfuncties is het belangrijk om een patroon te identificeren (indien aanwezig). Dat maakt het heel eenvoudig om een formule te construeren. In het bovenstaande geval is het patroon het @-teken tussen de gebruikersnaam en de domeinnaam, en we zullen het gebruiken als referentie om de gebruikersnamen te krijgen.
Hier is de formule om de gebruikersnaam te krijgen:
=LINKS(A2,VINDEN("@",A2)-1)
De bovenstaande formule gebruikt de LEFT-functie om de gebruikersnaam te extraheren door de positie van het @-teken in de id te identificeren. Dit wordt gedaan met behulp van de FIND-functie, die de positie van de @ retourneert.
Bijvoorbeeld, in het geval van [email protected], zou FIND(“@”,A2) 11 teruggeven, wat zijn positie in de tekenreeks is.
Nu gebruiken we de LEFT-functie om 10 tekens aan de linkerkant van de tekenreeks te extraheren (één minder dan de waarde die wordt geretourneerd door de LEFT-functie).
Voorbeeld 2 - De domeinnaam extraheren uit e-mail-ID's
Dezelfde logica die in het bovenstaande voorbeeld wordt gebruikt, kan worden gebruikt om de domeinnaam te verkrijgen. Een klein verschil hier is dat we de tekens aan de rechterkant van de tekstreeks moeten extraheren.
Hier is de formule die dit zal doen:
=RIGHT(A2,LEN(A2)-FIND("@",A2))
In de bovenstaande formule gebruiken we dezelfde logica, maar passen deze aan om er zeker van te zijn dat we de juiste string krijgen.
Laten we opnieuw het voorbeeld nemen van [email protected]. De functie FIND retourneert de positie van het @-teken, in dit geval 11. Nu moeten we alle tekens na de @ extraheren. Dus we identificeren de totale lengte van de string en trekken het aantal karakters af tot de @. Het geeft ons het aantal tekens dat de domeinnaam aan de rechterkant dekt.
Nu kunnen we eenvoudig de RECHTS-functie gebruiken om de domeinnaam te krijgen.
Voorbeeld 3 - De domeinnaam extraheren uit e-mail-ID's (zonder .com)
Om een subtekenreeks uit het midden van een tekenreeks te extraheren, moet u de positie van de markering direct voor en na de subtekenreeks identificeren.
Om bijvoorbeeld in het onderstaande voorbeeld de domeinnaam zonder het .com-gedeelte te krijgen, zou de markering @ zijn (die vlak voor de domeinnaam staat) en . (die ernaast staat).
Hier is de formule die alleen de domeinnaam extraheert:
=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)
Excel MID-functie extraheert het opgegeven aantal tekens uit de opgegeven startpositie. In dit voorbeeld hierboven specificeert FIND(“@”,A2)+1 de startpositie (die direct na de @ staat), en FIND(“.”,A2)-FIND(“@”,A2)-1 identificeert de aantal tekens tussen de '@' en de '.‘
Bijwerken: Een van de lezers William19 zei dat de bovenstaande formule niet zou werken als er een punt (.) in de e-mail-ID staat (bijvoorbeeld [email protected]). Dus hier is de formule om met dergelijke gevallen om te gaan:
=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)
Tekst naar kolommen gebruiken om een subtekenreeks in Excel te extraheren
Het gebruik van functies om een subtekenreeks in Excel te extraheren heeft het voordeel dat het dynamisch is. Als u de originele tekst wijzigt, zou de formule automatisch de resultaten bijwerken.
Als dit iets is dat u misschien niet nodig heeft, kan het gebruik van de functie Tekst naar kolommen een snelle en gemakkelijke manier zijn om de tekst in subtekenreeksen te splitsen op basis van opgegeven markeringen.
Hier is hoe dit te doen:
- Selecteer de cellen waar u de tekst hebt.
- Ga naar Gegevens -> Hulpmiddelen voor gegevens -> Tekst naar kolommen.
- Selecteer in de wizard Tekst naar kolom Stap 1 Gescheiden en druk op Volgende.
- Vink in stap 2 de optie Ander aan en voer @ in het vak ernaast in. Dit zal ons scheidingsteken zijn dat Excel zou gebruiken om de tekst in substrings te splitsen. U kunt het gegevensvoorbeeld hieronder bekijken. Klik op Volgende.
- In stap 3 werkt de algemene instelling in dit geval prima. U kunt echter een ander formaat kiezen als u getallen/datums splitst. Standaard is de doelcel waar u de originele gegevens hebt. Als u de originele gegevens intact wilt houden, wijzigt u deze in een andere cel.
- Klik op Voltooien.
Dit geeft u onmiddellijk twee sets substrings voor elke e-mail-ID die in dit voorbeeld wordt gebruikt.
Als u de tekst verder wilt splitsen (bijvoorbeeld batman.com splitsen in batman en com), herhaalt u hetzelfde proces ermee.
VINDEN en VERVANGEN gebruiken om tekst uit een cel in Excel te extraheren
VINDEN en VERVANGEN kan een krachtige techniek zijn wanneer u met tekst in Excel werkt. In de onderstaande voorbeelden leert u hoe u VINDEN en VERVANGEN kunt gebruiken met jokertekens om geweldige dingen te doen in Excel.
Zie ook: Leer alles over jokertekens in Excel.
Laten we dezelfde voorbeelden van e-mail-ID's nemen.
Voorbeeld 1 - Gebruikersnamen extraheren uit e-mail-ID's
Hier zijn de stappen om gebruikersnamen uit e-mail-ID's te extraheren met behulp van de functie Zoeken en vervangen:
- Kopieer en plak de originele gegevens. Aangezien Zoeken en vervangen werkt en de gegevens wijzigt waarop het wordt toegepast, is het het beste om een back-up van de originele gegevens te hebben.
- Selecteer de gegevens en ga naar Home -> Bewerken -> Zoek & Selecteer -> Vervang (of gebruik de sneltoets Ctrl + H).
- Voer in het dialoogvenster Zoeken en vervangen het volgende in:
- Vind wat: @*
- Vervang door: (laat dit leeg)
- Klik op Alles vervangen.
Hiermee wordt onmiddellijk alle tekst vóór de @ in de e-mail-ID's verwijderd. U krijgt het resultaat zoals hieronder weergegeven:
Hoe werkt dit?? - In het bovenstaande voorbeeld hebben we een combinatie van @ en * gebruikt. Een asterisk (*) is een jokerteken dat een willekeurig aantal tekens vertegenwoordigt. Daarom zou @* een tekenreeks betekenen die begint met @ en die een willekeurig aantal tekens kan hebben. In [email protected] zou @* bijvoorbeeld @batman.com zijn. Als we @* vervangen door blanco, worden alle tekens na @ verwijderd (inclusief @).
Voorbeeld 2 - De domeinnaam extraheren uit e-mail-ID's
Met dezelfde logica kunt u de criteria 'Zoeken naar' wijzigen om de domeinnaam te krijgen.
Dit zijn de stappen:
- Selecteer de gegevens.
- Ga naar Home -> Bewerken -> Zoek & Selecteer -> Vervang (of gebruik de sneltoets Ctrl + H).
- Voer in het dialoogvenster Zoeken en vervangen het volgende in:
- Vind wat: *@
- Vervang door: (laat dit leeg)
- Klik op Alles vervangen.
Hiermee wordt onmiddellijk alle tekst vóór de @ in de e-mail-ID's verwijderd. U krijgt het resultaat zoals hieronder weergegeven:
