Excel har et sett med TEKST -funksjoner som kan gjøre underverker. Du kan utføre alle slags tekstskiver og terninger ved å bruke disse funksjonene.
En av de vanlige oppgavene for folk som jobber med tekstdata er å trekke ut en delstreng i Excel (dvs. hente psrt av teksten fra en celle).
Dessverre er det ingen delstrengfunksjon i Excel som enkelt kan gjøre dette. Dette kan imidlertid fortsatt gjøres ved hjelp av tekstformler, så vel som noen andre innebygde Excel-funksjoner.
La oss først se på noen av tekstfunksjonene vi skal bruke i denne opplæringen.
Excel TEKST -funksjoner
Excel har en rekke tekstfunksjoner som ville gjøre det veldig enkelt å trekke ut en delstreng fra originalteksten i Excel. Her er Excel -tekstfunksjonene som vi skal bruke i denne opplæringen:
- RIGHT -funksjon: Trekker ut det angitte antallet tegn fra høyre på tekststrengen.
- VENSTRE -funksjon: Trekker ut det angitte antall tegn fra venstre i tekststrengen.
- MID -funksjon: Trekker ut det angitte antall tegn fra den angitte startposisjonen i en tekststreng.
- FIND -funksjon: Finner startposisjonen til den angitte teksten i tekststrengen.
- LEN -funksjon: Returnerer antall tegn i tekststrengen.
Pakk ut en understring i Excel ved hjelp av funksjoner
Anta at du har et datasett som vist nedenfor:
Dette er noen tilfeldige (men superhelt-ish) e-post-ID-er (unntatt mine), og i eksemplene nedenfor viser jeg deg hvordan du trekker ut brukernavn og domenenavn ved hjelp av tekstfunksjonene i Excel.
Eksempel 1 - Pakk ut brukernavn fra e -post -ID -er
Når du bruker tekstfunksjoner, er det viktig å identifisere et mønster (hvis det er noe). Det gjør det veldig enkelt å konstruere en formel. I tilfellet ovenfor er mønsteret @ -tegnet mellom brukernavnet og domenenavnet, og vi vil bruke det som en referanse for å få brukernavnene.
Her er formelen for å få brukernavnet:
= VENSTRE (A2, FINN ("@", A2) -1)
Formelen ovenfor bruker VENSTRE -funksjonen til å trekke ut brukernavnet ved å identifisere posisjonen til @ -tegnet i ID -en. Dette gjøres ved å bruke FIND -funksjonen, som returnerer posisjonen til @.
For eksempel, for [email protected], ville FIND (“@”, A2) returnere 11, som er posisjonen i tekststrengen.
Nå bruker vi LEFT -funksjonen til å trekke ut 10 tegn fra venstre på strengen (ett mindre enn verdien returnert av LEFT -funksjonen).
Eksempel 2 - Pakk ut domenenavnet fra e -post -ID -er
Den samme logikken som ble brukt i eksemplet ovenfor kan brukes for å få domenenavnet. En liten forskjell her er at vi må trekke ut tegnene fra høyre på tekststrengen.
Her er formelen som vil gjøre dette:
= HØYRE (A2, LENG (A2) -FIND ("@", A2))
I formelen ovenfor bruker vi den samme logikken, men justerer den for å sikre at vi får riktig streng.
La oss igjen ta eksemplet [email protected]. FIND -funksjonen returnerer posisjonen til @ -tegnet, som er 11 i dette tilfellet. Nå må vi trekke ut alle tegnene etter @. Så vi identifiserer den totale lengden på strengen og trekker fra antall tegn til @. Det gir oss antall tegn som dekker domenenavnet til høyre.
Nå kan vi ganske enkelt bruke HØYRE -funksjonen for å få domenenavnet.
Eksempel 3 - Pakk ut domenenavnet fra e -post -ID -er (uten .com)
For å trekke ut en delstreng fra midten av en tekststreng, må du identifisere posisjonen til markøren rett før og etter delstrengen.
For eksempel, i eksemplet nedenfor, for å få domenenavnet uten .com -delen, vil markøren være @ (som er rett foran domenenavnet) og. (som er rett etter det).
Her er formelen som bare trekker ut domenenavnet:
= MIDD (A2, FINN ("@", A2)+1, FINN (".", A2) -FIND ("@", A2) -1)
Excel MID -funksjonen trekker ut det angitte antallet tegn fra den angitte startposisjonen. I dette eksemplet ovenfor spesifiserer FIND (“@”, A2) +1 startposisjonen (som er rett etter@), og FIND (“.”, A2) -FIND (“@”, A2) -1 identifiserer antall tegn mellom '@' og '.‘
Oppdater: En av leserne William19 nevnte at formelen ovenfor ikke ville fungere i tilfelle det er en prikk (.) I e -post -ID -en (for eksempel [email protected]). Så her er formelen for å håndtere slike tilfeller:
= MIDT (A1, FINN ("@", A1)+1, FINN (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Bruke tekst til kolonner for å trekke ut en understring i Excel
Å bruke funksjoner til å trekke ut en delstreng i Excel har fordelen av å være dynamisk. Hvis du endrer originalteksten, vil formelen automatisk oppdatere resultatene.
Hvis dette er noe du kanskje ikke trenger, kan bruk av tekst til kolonner -funksjonen være en rask og enkel måte å dele teksten inn i underlag basert på spesifiserte markører.
Slik gjør du dette:
- Velg cellene der du har teksten.
- Gå til Data -> Dataverktøy -> Tekst til kolonner.
- I veiviseren for tekst til kolonne trinn 1, velg Avgrenset og trykk på Neste.
- I trinn 2, sjekk alternativet Annet og skriv @ i boksen rett til det. Dette vil være vår skilletegn som Excel ville bruke for å dele teksten i underlag. Du kan se forhåndsvisningen av data nedenfor. Klikk på Neste.
- I trinn 3 fungerer den generelle innstillingen fint i dette tilfellet. Du kan imidlertid velge et annet format hvis du deler tall/datoer. Som standard er målcellen der du har de originale dataene. Hvis du vil beholde de originale dataene intakte, kan du endre dette til en annen celle.
- Klikk på Fullfør.
Dette vil umiddelbart gi deg to sett med underlag for hver e -post -ID som brukes i dette eksemplet.
Hvis du vil dele teksten ytterligere (for eksempel dele batman.com til batman og com), gjenta den samme prosessen med den.
Bruk FIND og REPLACE til å trekke ut tekst fra en celle i Excel
FINN og ERSTAT kan være en kraftig teknikk når du jobber med tekst i Excel. I eksemplene nedenfor lærer du hvordan du bruker FIND og REPLACE med jokertegn til å gjøre fantastiske ting i Excel.
Se også: Lær alt om jokertegn i Excel.
La oss ta de samme eksemplene på e -post -ID -er.
Eksempel 1 - Pakk ut brukernavn fra e -post -ID -er
Her er trinnene for å trekke ut brukernavn fra e -post -ID -er ved hjelp av funksjonen Finn og erstatt:
- Kopier og lim inn de originale dataene. Siden Find and Replace fungerer og endrer dataene de brukes på, er det best å ta en sikkerhetskopi av de originale dataene.
- Velg dataene og gå til Hjem -> Redigering -> Finn og velg -> Erstatt (eller bruk hurtigtasten Ctrl + H).
- Skriv inn følgende i dialogboksen Finn og erstatt:
- Finne hva: @*
- Erstatt med: (la dette stå tomt)
- Klikk på Erstatt alle.
Dette vil umiddelbart fjerne all tekst før @ i e -post -ID -ene. Du får resultatet som vist nedenfor:
Hvordan fungerer dette ?? - I eksemplet ovenfor har vi brukt en kombinasjon av @ og *. En stjerne (*) er et jokertegn som representerer et hvilket som helst antall tegn. Derfor vil @* bety en tekststreng som starter med @ og kan ha et hvilket som helst antall tegn etter den. For eksempel i [email protected] vil @* være @batman.com. Når vi erstatter @* med tomt, fjerner det alle tegnene etter @(inkludert @).
Eksempel 2 - Pakk ut domenenavnet fra e -post -ID -er
Ved å bruke den samme logikken kan du endre "Finn hva" -kriteriene for å få domenenavnet.
Her er trinnene:
- Velg dataene.
- Gå til Hjem -> Redigering -> Finn og velg -> Erstatt (eller bruk hurtigtasten Ctrl + H).
- Skriv inn følgende i dialogboksen Finn og erstatt:
- Finne hva: *@
- Erstatt med: (la dette stå tomt)
- Klikk på Erstatt alle.
Dette vil umiddelbart fjerne all teksten før @ i e -post -ID -ene. Du får resultatet som vist nedenfor:
