Excel har en uppsättning TEXT -funktioner som kan göra underverk. Du kan utföra alla typer av textskivor och tärningar med hjälp av dessa funktioner.
En av de vanliga uppgifterna för personer som arbetar med textdata är att extrahera en delsträng i Excel (det vill säga få psrt av texten från en cell).
Tyvärr finns det ingen delsträngsfunktion i Excel som enkelt kan göra detta. Detta kan dock fortfarande göras med hjälp av textformler samt några andra inbyggda Excel-funktioner.
Låt oss först titta på några av de textfunktioner vi kommer att använda i den här självstudien.
Excel TEXT -funktioner
Excel har en rad textfunktioner som skulle göra det väldigt enkelt att extrahera en delsträng från originaltexten i Excel. Här är Excel -textfunktionerna som vi kommer att använda i den här självstudien:
- RIGHT -funktion: Extraherar det angivna antalet tecken från höger om textsträngen.
- VÄNSTER -funktion: Extraherar det angivna antalet tecken från vänster om textsträngen.
- MID -funktion: Extraherar det angivna antalet tecken från den angivna startpositionen i en textsträng.
- FIND -funktion: Hittar startpositionen för den angivna texten i textsträngen.
- LEN -funktion: Returnerar antalet tecken i textsträngen.
Extrahera en delsträng i Excel med hjälp av funktioner
Anta att du har en dataset som visas nedan:
Det här är några slumpmässiga (men superhjält-ish) e-post-id (utom mina), och i exemplen nedan visar jag dig hur du extraherar användarnamnet och domännamnet med hjälp av textfunktionerna i Excel.
Exempel 1 - Extrahera användarnamn från e -post -id: n
När du använder textfunktioner är det viktigt att identifiera ett mönster (om sådant finns). Det gör det väldigt enkelt att konstruera en formel. I ovanstående fall är mönstret @ -tecknet mellan användarnamnet och domännamnet, och vi kommer att använda det som en referens för att få användarnamnen.
Här är formeln för att få användarnamnet:
= VÄNSTER (A2, HITTA ("@", A2) -1)
Ovanstående formel använder LEFT -funktionen för att extrahera användarnamnet genom att identifiera positionen för @ -tecknet i id: t. Detta görs med hjälp av FIND -funktionen, som returnerar @-positionen.
Till exempel, när det gäller [email protected], skulle FIND (“@”, A2) returnera 11, vilket är dess position i textsträngen.
Nu använder vi VÄNSTER -funktionen för att extrahera 10 tecken från vänster om strängen (ett mindre än värdet som returneras av VÄNSTER -funktionen).
Exempel 2 - Extrahera domännamnet från e -post -id: n
Samma logik som används i exemplet ovan kan användas för att få domännamnet. En mindre skillnad här är att vi måste extrahera tecknen från höger om textsträngen.
Här är formeln som gör detta:
= HÖGER (A2, LEN (A2) -FIND ("@", A2))
I formeln ovan använder vi samma logik, men justerar den för att se till att vi får rätt sträng.
Låt oss återigen ta exemplet [email protected]. FIND -funktionen returnerar positionen för @ -tecknet, vilket är 11 i detta fall. Nu måste vi extrahera alla tecken efter @. Så vi identifierar strängens totala längd och subtraherar antalet tecken till @. Det ger oss antalet tecken som täcker domännamnet till höger.
Nu kan vi helt enkelt använda RIGHT -funktionen för att få domännamnet.
Exempel 3 - Extrahera domännamnet från e -post -ID (utan .com)
För att extrahera en delsträng från mitten av en textsträng måste du identifiera markörens position precis före och efter delsträngen.
Till exempel, i exemplet nedan, för att få domännamnet utan .com -delen, skulle markören vara @ (som ligger precis före domännamnet) och. (som är direkt efter det).
Här är formeln som bara kommer att extrahera domännamnet:
= MID (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Excel MID -funktion extraherar det angivna antalet tecken från den angivna startpositionen. I det här exemplet ovan anger FIND (“@”, A2) +1 startpositionen (som ligger strax efter@), och FIND (“.”, A2) -FIND (“@”, A2) -1 identifierar antal tecken mellan '@' och den '.‘
Uppdatering: En av läsarna William19 nämnde att ovanstående formel inte skulle fungera om det finns en punkt (.) I e -post -id (till exempel [email protected]). Så här är formeln för att hantera sådana fall:
= MID (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Använda text till kolumner för att extrahera en delsträng i Excel
Att använda funktioner för att extrahera en delsträng i Excel har fördelen av att vara dynamisk. Om du ändrar originaltexten uppdateras resultaten automatiskt i formeln.
Om det här är något du kanske inte behöver kan det vara ett snabbt och enkelt sätt att dela upp texten i underlag baserat på angivna markörer med hjälp av funktionen Text till kolumner.
Så här gör du:
- Markera cellerna där du har texten.
- Gå till Data -> Dataverktyg -> Text till kolumner.
- I guiden Text till kolumn Steg 1, välj Avgränsad och tryck på Nästa.
- I steg 2, markera alternativet Annat och ange @ i rutan direkt till det. Detta kommer att vara vår avgränsare som Excel skulle använda för att dela upp texten i underlag. Du kan se förhandsgranskningen av data nedan. Klicka på Nästa.
- I steg 3 fungerar den allmänna inställningen bra i det här fallet. Du kan dock välja ett annat format om du delar nummer/datum. Som standard är målcellen där du har originaldata. Om du vill behålla originaldata intakta ändrar du detta till någon annan cell.
- Klicka på Slutför.
Detta ger dig omedelbart två uppsättningar av underlag för varje e -post -id som används i detta exempel.
Om du vill dela upp texten ytterligare (till exempel dela batman.com till batman och com), upprepa samma process med den.
Använda FIND och REPLACE för att extrahera text från en cell i Excel
HITTA och ERSÄTTA kan vara en kraftfull teknik när du arbetar med text i Excel. I exemplen nedan lär du dig hur du använder FIND och REPLACE med jokertecken för att göra fantastiska saker i Excel.
Se även: Lär dig allt om jokertecken i Excel.
Låt oss ta samma exempel på e -post -id: er.
Exempel 1 - Extrahera användarnamn från e -post -id: n
Här är stegen för att extrahera användarnamn från e -post -ID med hjälp av funktionen Sök och ersätt:
- Kopiera och klistra in originaldata. Eftersom Hitta och ersätt fungerar och ändrar data som den tillämpas på, är det bäst att ha en säkerhetskopia av originaldata.
- Välj data och gå till Hem -> Redigering -> Hitta och välj -> Ersätt (eller använd tangentbordsgenvägen Ctrl + H).
- I dialogrutan Sök och ersätt anger du följande:
- Hitta vad: @*
- Ersätt med: (lämna detta tomt)
- Klicka på Ersätt alla.
Detta kommer omedelbart att ta bort all text före @ i e -post -id: erna. Du får resultatet enligt nedan:
Hur fungerar detta ?? - I exemplet ovan har vi använt en kombination av @ och *. En asterisk (*) är ett jokertecken som representerar valfritt antal tecken. Därför skulle @* innebära en textsträng som börjar med @ och kan ha valfritt antal tecken efter den. Till exempel i [email protected] skulle @* vara @batman.com. När vi ersätter @* med blank tar det bort alla tecken efter @(inklusive @).
Exempel 2 - Extrahera domännamnet från e -post -id: n
Med samma logik kan du ändra kriterierna "Hitta vad" för att få domännamnet.
Här är stegen:
- Välj data.
- Gå till Hem -> Redigering -> Hitta och välj -> Ersätt (eller använd tangentbordsgenvägen Ctrl + H).
- I dialogrutan Sök och ersätt anger du följande:
- Hitta vad: *@
- Ersätt med: (lämna detta tomt)
- Klicka på Ersätt alla.
Detta kommer att ta bort all text före @ i e -post -id: erna. Du får resultatet enligt nedan:
