Excel har et sæt TEKST -funktioner, der kan gøre underværker. Du kan udføre alle former for tekstskiver og terninger ved hjælp af disse funktioner.
En af de almindelige opgaver for folk, der arbejder med tekstdata, er at udtrække en delstreng i Excel (dvs. hente psrt af teksten fra en celle).
Desværre er der ingen understring -funktion i Excel, der let kan gøre dette. Dette kan dog stadig gøres ved hjælp af tekstformler samt nogle andre indbyggede Excel-funktioner.
Lad os først se på nogle af de tekstfunktioner, vi vil bruge i denne vejledning.
Excel TEKST -funktioner
Excel har en række tekstfunktioner, der ville gøre det virkelig let at udtrække en delstreng fra den originale tekst i Excel. Her er Excel -tekstfunktionerne, som vi vil bruge i denne vejledning:
- RIGHT -funktion: Udtrækker det angivne antal tegn fra højre for tekststrengen.
- LEFT -funktion: Udtrækker det angivne antal tegn fra venstre i tekststrengen.
- MID -funktion: Udtrækker det angivne antal tegn fra den angivne startposition i en tekststreng.
- FIND -funktion: Finder startpositionen for den angivne tekst i tekststrengen.
- LEN -funktion: Returnerer antallet af tegn i tekststrengen.
Udtræk en understreng i Excel ved hjælp af funktioner
Antag, at du har et datasæt som vist herunder:
Dette er nogle tilfældige (men superhelte-e-mail) id'er (undtagen mine), og i eksemplerne herunder viser jeg dig, hvordan du udtrækker brugernavnet og domænenavnet ved hjælp af tekstfunktionerne i Excel.
Eksempel 1 - Udtræk af brugernavne fra e -mail -id'er
Når du bruger tekstfunktioner, er det vigtigt at identificere et mønster (hvis det findes). Det gør det virkelig let at konstruere en formel. I ovenstående tilfælde er mønsteret @ -tegnet mellem brugernavnet og domænenavnet, og vi vil bruge det som en reference for at få brugernavne.
Her er formlen for at få brugernavnet:
= VENSTRE (A2, FIND ("@", A2) -1)
Ovenstående formel bruger LEFT -funktionen til at udtrække brugernavnet ved at identificere positionen af @ -tegnet i id'et. Dette gøres ved hjælp af FIND -funktionen, som returnerer @-positionen.
For eksempel i tilfælde af [email protected] ville FIND (“@”, A2) returnere 11, hvilket er dens position i tekststrengen.
Nu bruger vi LEFT -funktionen til at udtrække 10 tegn fra venstre for strengen (et mindre end værdien, der returneres af LEFT -funktionen).
Eksempel 2 - Udtrækning af domænenavnet fra e -mail -id'er
Den samme logik, der bruges i ovenstående eksempel, kan bruges til at hente domænenavnet. En mindre forskel her er, at vi skal udtrække tegnene fra højre på tekststrengen.
Her er formlen, der vil gøre dette:
= HØJRE (A2, LEN (A2) -FIND ("@", A2))
I ovenstående formel bruger vi den samme logik, men justerer den for at sikre, at vi får den korrekte streng.
Lad os igen tage eksemplet [email protected]. FIND -funktionen returnerer positionen af @ -tegnet, som er 11 i dette tilfælde. Nu skal vi udtrække alle tegnene efter @. Så vi identificerer strengens samlede længde og trækker antallet af tegn til @. Det giver os antallet af tegn, der dækker domænenavnet til højre.
Nu kan vi simpelthen bruge den HØJRE funktion til at få domænenavnet.
Eksempel 3 - Udtrækning af domænenavnet fra e -mail -id'er (uden .com)
For at udtrække en delstreng fra midten af en tekststreng, skal du identificere markørens position lige før og efter delstrengen.
For eksempel, i eksemplet herunder, for at få domænenavnet uden .com -delen, ville markøren være @ (hvilket er lige før domænenavnet) og. (som er lige efter det).
Her er formlen, der kun udtrækker domænenavnet:
= MIDD (A2, FIND ("@", A2)+1, FIND (".", A2) -FIND ("@", A2) -1)
Excel MID -funktionen udtrækker det angivne antal tegn fra den angivne startposition. I dette eksempel ovenfor angiver FIND (“@”, A2) +1 startpositionen (som er lige efter@), og FIND (“.”, A2) -FIND (“@”, A2) -1 identificerer antal tegn mellem '@' og '.‘
Opdatering: En af læserne William19 nævnte, at ovenstående formel ikke ville fungere, hvis der er en prik (.) I e -mail -id'et (f.eks. [email protected]). Så her er formlen til at håndtere sådanne tilfælde:
= MIDD (A1, FIND ("@", A1)+1, FIND (".", A1, FIND ("@", A1))-FIND ("@", A1) -1)
Brug af tekst til kolonner til at udtrække en understreng i Excel
Brug af funktioner til at udtrække en understreng i Excel har fordelen ved at være dynamisk. Hvis du ændrer den originale tekst, opdaterer formlen automatisk resultaterne.
Hvis dette er noget, du muligvis ikke har brug for, kan det være en hurtig og let måde at opdele teksten i underlag baseret på angivne markører ved hjælp af funktionen Tekst til kolonner.
Sådan gør du dette:
- Vælg de celler, hvor du har teksten.
- Gå til Data -> Dataværktøjer -> Tekst til kolonner.
- I guiden Tekst til kolonne trin 1 skal du vælge Afgrænset og trykke på Næste.
- I trin 2 skal du markere indstillingen Andet og indtaste @ i feltet lige til den. Dette vil være vores afgrænser, Excel ville bruge til at opdele teksten i underlag. Du kan se forhåndsvisningen af data herunder. Klik på Næste.
- I trin 3 fungerer den generelle indstilling fint i dette tilfælde. Du kan dog vælge et andet format, hvis du opdeler tal/datoer. Som standard er destinationscellen, hvor du har de originale data. Hvis du vil beholde de originale data intakte, skal du ændre dette til en anden celle.
- Klik på Afslut.
Dette vil øjeblikkeligt give dig to sæt substrings for hvert e -mail -id, der bruges i dette eksempel.
Hvis du vil opdele teksten yderligere (f.eks. Opdele batman.com til batman og com), skal du gentage den samme proces med den.
Brug FIND og REPLACE til at udtrække tekst fra en celle i Excel
FIND og REPLACE kan være en kraftfuld teknik, når du arbejder med tekst i Excel. I eksemplerne herunder lærer du, hvordan du bruger FIND og REPLACE med jokertegn til at gøre fantastiske ting i Excel.
Se også: Lær alt om jokertegn i Excel.
Lad os tage de samme eksempler på e -mail -id'er.
Eksempel 1 - Udtræk af brugernavne fra e -mail -id'er
Her er trinene til at udtrække brugernavne fra e -mail -id'er ved hjælp af funktionen Find og erstat:
- Kopier og indsæt de originale data. Da Find and Replace fungerer og ændrer de data, de anvendes på, er det bedst at have en sikkerhedskopi af de originale data.
- Vælg dataene og gå til Hjem -> Redigering -> Find og vælg -> Erstat (eller brug tastaturgenvejen Ctrl + H).
- I dialogboksen Find og erstat skal du indtaste følgende:
- Find hvad: @*
- Erstat med: (lad dette stå tomt)
- Klik på Erstat alle.
Dette fjerner øjeblikkeligt al teksten før @ i e -mail -id'erne. Du får resultatet som vist herunder:
Hvordan fungerer dette ?? - I ovenstående eksempel har vi brugt en kombination af @ og *. En stjerne (*) er et jokertegn, der repræsenterer et vilkårligt antal tegn. Derfor ville @* betyde en tekststreng, der starter med @ og kan have et hvilket som helst antal tegn efter den. For eksempel i [email protected] ville @* være @batman.com. Når vi erstatter @* med blank, fjerner alle tegn efter @(inklusive @).
Eksempel 2 - Udtrækning af domænenavnet fra e -mail -id'er
Ved hjælp af den samme logik kan du ændre 'Find hvad' -kriterierne for at få domænenavnet.
Her er trinene:
- Vælg data.
- Gå til Hjem -> Redigering -> Find og vælg -> Erstat (eller brug tastaturgenvejen Ctrl + H).
- I dialogboksen Find og erstat skal du indtaste følgende:
- Find hvad: *@
- Erstat med: (lad dette stå tomt)
- Klik på Erstat alle.
Dette fjerner øjeblikkeligt al teksten før @ i e -mail -id'erne. Du får resultatet som vist herunder:
