Excel heeft een set tekstfuncties die wonderen kunnen doen. U kunt allerlei tekst slice en dobbelstenen operaties met behulp van deze functies.

een van de veelvoorkomende taken voor mensen die met tekstgegevens werken is het extraheren van een substring in Excel (dat wil zeggen, 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 een kijkje nemen op enkele van de tekstfuncties die we in deze tutorial zullen gebruiken.

Excel-tekstfuncties

Excel heeft een reeks tekstfuncties die het heel gemakkelijk maken om een substring uit de originele tekst in Excel te extraheren. Hier zijn de Excel-Tekstfuncties die we in deze tutorial zullen gebruiken:

• RECHTSFUNCTIE: haalt de opgegeven aantallen tekens uit de rechterkant van de tekstreeks.
• LEFT-functie: extraheert de opgegeven aantallen tekens aan de linkerkant van de tekstreeks.,
• MID-functie: extraheert de opgegeven aantallen tekens uit de opgegeven beginpositie in een tekstreeks.
• FIND-functie: vindt de beginpositie van de opgegeven tekst in de tekstreeks.
• LEN-functie: geeft het aantal tekens in de tekstreeks terug.,

extraheer een Substring in Excel met behulp van functies

stel dat u een dataset hebt zoals hieronder getoond:

Dit zijn enkele willekeurige (maar superhero-achtige) e-mail-id ‘ s (behalve de mijne), en in de voorbeelden hieronder zal ik u laten 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

tijdens het gebruik van Tekstfuncties is het belangrijk om een patroon te identificeren (indien aanwezig). Dat maakt het heel gemakkelijk 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 een referentie om de gebruikersnamen te krijgen.

Hier is de formule om de Gebruikersnaam te krijgen:

=LEFT(A2,FIND("@",A2)-1)

de bovenstaande formule gebruikt de functie links 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], FIND (“@ ” , A2) zou 11 retourneren, wat zijn positie is in de tekstreeks.,

nu gebruiken we de functie links om 10 tekens uit de linkerkant van de tekenreeks te extraheren (één minder dan de waarde die wordt geretourneerd door de functie links).

Voorbeeld 2-de domeinnaam extraheren uit e-mail id ‘ s

dezelfde logica gebruikt in het bovenstaande voorbeeld kan worden gebruikt om de domeinnaam te krijgen. Een klein verschil hier is dat we de tekens moeten extraheren aan de rechterkant van de tekenreeks.,

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 ervoor te zorgen dat we de juiste tekenreeks krijgen.

laten we nogmaals het voorbeeld nemen van [email protected]. de functie Zoeken geeft de positie van het @ – teken terug, wat in dit geval 11 is. Nu moeten we alle karakters extraheren na de @. Dus we identificeren de totale lengte van de string en trekken het aantal tekens af tot de @. Het geeft ons het aantal tekens dat betrekking heeft op de domeinnaam aan de rechterkant.,

nu kunnen we gewoon de juiste functie gebruiken om de domeinnaam te krijgen.

Voorbeeld 3-het extraheren van de domeinnaam uit e-mail id ‘ s (zonder .com)

om een substring uit het midden van een tekenreeks te extraheren, moet u de positie van de marker vlak voor en na de substring identificeren.

bijvoorbeeld, in het voorbeeld hieronder, om de domeinnaam te krijgen zonder het. com deel, zou de marker @ zijn (wat vlak voor de domeinnaam staat) en . (dat is direct na het).,

Hier is de formule die alleen de domeinnaam zal extraheren:

=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1) 

Excel MID-functie haalt het opgegeven aantal tekens uit de opgegeven startpositie. In dit voorbeeld hierboven, FIND(“@”,A2)+1 specificeert de startpositie (die direct na de@), EN FIND (“.”, A2) – FIND(“@”,A2) -1 identificeert het aantal tekens tussen de ‘@’ en de ‘.’

Update: een van de lezers William19 zei dat de bovenstaande formule niet zou werken in het geval er een punt (.) in de e-mail id (bijvoorbeeld, bruce.wayne@batman.,com). 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 Substring in Excel te extraheren

het gebruik van functies om een substring in Excel te extraheren heeft het voordeel dynamisch te zijn. Als u de oorspronkelijke tekst wijzigt, werkt de formule de resultaten automatisch bij.

als dit iets is dat u misschien niet nodig hebt, dan kan het gebruik van de functie Tekst naar kolommen een snelle en eenvoudige manier zijn om de tekst op te splitsen in substrings op basis van opgegeven markeringen.

Hier is hoe dit te doen:

• Selecteer de cellen waar u de tekst hebt.,
• ga naar Data –> Data Tools –> tekst naar kolommen.
• in de Wizard Tekst naar kolom Stap 1, Selecteer gescheiden en druk op Volgende.
• in Stap 2, vink de andere optie aan en voer @ in het vak er recht op in. Dit zal ons scheidingsteken zijn dat Excel zou gebruiken om de tekst in substrings te splitsen. U kunt de data preview hieronder te zien. Klik op Volgende.
• in Stap 3 werkt de Algemene instelling prima in dit geval., 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 dit in een andere cel.
• klik op Voltooien.

Dit geeft u direct twee sets substrings voor elke e-mail-id die in dit voorbeeld wordt gebruikt.

Als u de tekst verder wilt splitsen (bijvoorbeeld, splitsen batman.com naar batman en com), herhaal hetzelfde proces met het.,

zoeken en vervangen gebruiken om tekst uit een cel in Excel te extraheren

zoeken en vervangen kan een krachtige techniek zijn wanneer u met tekst in Excel werkt. In de onderstaande voorbeelden leert u hoe u zoeken en vervangen door jokertekens kunt gebruiken om verbazingwekkende dingen in Excel te doen.

See Also: Learn All about Wildcard Characters in Excel.

laten we dezelfde e-mail id ‘ s voorbeelden nemen.

Voorbeeld 1-Gebruikersnamen extraheren uit e-mail-id ’s

Hier volgen 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 verandert de gegevens waarop het wordt toegepast, is het het beste om een back-up van de oorspronkelijke gegevens.
• Selecteer de gegevens en ga naar Home –> Editing –> Find & Select –> Replace (or use the keyboard sneltoets Ctrl + H).,
• in het dialoogvenster Zoeken en vervangen voert u het volgende in:
  • zoeken wat: @*
  • vervangen door: (laat dit leeg)
• klik op Alles vervangen.

Dit zal onmiddellijk alle tekst voor de @ in de e-mail id ‘ s verwijderen. U krijgt het resultaat zoals hieronder getoond:

Voorbeeld 2 – de domeinnaam extraheren uit e-mail id ’s

met dezelfde logica kunt u de criteria’ zoeken wat ‘ wijzigen om de domeinnaam te verkrijgen.,

Hier zijn de stappen:

Dit zal onmiddellijk alle tekst voor de @ in de e-mail-id ‘ s verwijderen. U krijgt het resultaat zoals hieronder getoond:

U kunt ook de volgende Excel-Tutorials leuk vinden:

• Hoe cellen te tellen die tekstreeksen bevatten.
• extraheer Gebruikersnamen uit e-mail-id ‘ s in Excel .
• Excel-functies (voorbeelden + video’s).
• Haal meer uit zoeken en vervangen in Excel.
• Hoe de eerste Letter van een tekenreeks in Excel te kapitaliseren
• Hoe het eerste woord uit een tekenreeks in Excel te extraheren