Når du arbejder med tekstdata i Excel, har du muligvis behov for at fjerne teksten før eller efter et bestemt tegn eller en tekststreng.
Hvis du f.eks. Har navn og betegnelsesdata for personer, kan du fjerne betegnelsen efter kommaet og kun beholde navnet (eller omvendt, hvor du beholder betegnelsen og fjerner navnet).
Nogle gange kan det gøres med en simpel formel eller en hurtig Find og erstat, og nogle gange har det brug for mere komplekse formler eller løsninger.
I denne vejledning viser jeg dig, hvordan du fjerner teksten før eller efter et bestemt tegn i Excel (ved hjælp af forskellige eksempler).
Så lad os komme i gang med nogle enkle eksempler.
Fjern tekst efter et tegn ved hjælp af Find og erstat
Hvis du hurtigt vil fjerne al teksten efter en bestemt tekststreng (eller før en tekststreng), kan du gøre det ved hjælp af Find og erstat og jokertegn.
Antag, at du har et datasæt som vist nedenfor, og du vil fjerne betegnelsen efter komma -tegnet og beholde teksten foran kommaet.
Nedenfor er trinene for at gøre dette:
- Kopier og indsæt dataene fra kolonne A til kolonne B (dette er også for at beholde de originale data)
- Når cellerne i kolonne B er markeret, skal du klikke på fanen Startside
- I gruppen Redigering skal du klikke på indstillingen Find og vælg
- I de muligheder, der vises i rullemenuen, skal du klikke på Udskift. Dette åbner dialogboksen Find og erstat
- I feltet 'Find hvad' skal du indtaste ,* (dvs. komma efterfulgt af et stjerne tegn)
- Lad feltet 'Erstat med' være tomt
- Klik på knappen Erstat alle
Ovenstående trin ville finde kommaet i datasættet og fjerne al teksten efter kommaet (inklusive kommaet).
Da dette erstatter teksten fra de markerede celler, er det en god idé at kopiere teksten til en anden kolonne og derefter udføre denne søgning og udskiftning eller oprette en sikkerhedskopi af dine data, så du har de originale data intakte
Hvordan virker det?
* (stjerne tegn) er et jokertegn, der kan repræsentere et vilkårligt antal tegn.
Når jeg bruger det efter kommaet (i feltet 'Find hvad') og derefter klikker på Erstat alle -knappen, finder det det første komma i cellen og anser det for et match.
Dette skyldes, at stjernetegnet (*) betragtes som en matchning til hele tekststrengen, der følger kommaet.
Så når du trykker på knappen erstat alle, erstatter det kommaet og al den tekst, der følger.
Bemærk: Denne metode fungerer godt, så har du kun et komma i hver celle (som i vores eksempel). Hvis du har flere kommaer, vil denne metode altid finde det første komma og derefter fjerne alt efter det. Så du kan ikke bruge denne metode, hvis du vil erstatte al teksten efter det andet komma og beholde den første som den er.Hvis du vil fjerne alle tegnene før kommaet, skal du ændre indtastningen Find hvad feltet ved at have stjernetegnet foran kommaet (*, i stedet for,*)
Fjern tekst ved hjælp af formler
Hvis du har brug for mere kontrol over, hvordan du finder og erstatter tekst før eller efter et bestemt tegn, er det bedre at bruge de indbyggede tekstformler i Excel.
Antag, at du har nedenstående datasæt, hvor du vil fjerne al teksten efter kommaet.
Nedenfor er formlen til at gøre dette:
= VENSTRE (A2, FIND (",", A2) -1)
Ovenstående formel bruger FIND -funktionen til at finde kommaens position i cellen.
Dette positionsnummer bruges derefter af LEFT -funktionen til at udtrække alle tegnene før kommaet. Da jeg ikke vil have komma som en del af resultatet, har jeg trukket 1 fra Find -formelens resulterende værdi.
Dette var et simpelt scenario.
Lad os tage en lidt kompleks.
Antag, at jeg har dette datasæt nedenfor, hvor jeg vil fjerne al teksten efter det andet komma.
Her er formlen, der vil gøre dette:
= VENSTRE (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Da dette datasæt har flere kommaer, kan jeg ikke bruge FIND -funktionen til at få placeringen af det første komma og udtrække alt til venstre for det.
Jeg skal på en eller anden måde finde ud af placeringen af det andet komma og derefter udtrække alt, hvad der er til venstre for det andet komma.
For at gøre dette har jeg brugt SUBSTITUTE -funktionen til at ændre det andet komma til et udråbstegn. Nu giver dette mig en unik karakter i cellen. Jeg kan nu bruge positionen af udråbstegnet til at udtrække alt til venstre for det andet komma.
Denne position af udråbstegnet bruges i VENSTRE -funktionen til at udtrække alt før det andet komma.
Så langt så godt!
Men hvad nu hvis der er et inkonsekvent antal kommaer i datasættet.
For eksempel i nedenstående datasæt har nogle celler to kommaer, og nogle celler har tre kommaer, og jeg skal udtrække al teksten før det sidste komma.
I dette tilfælde skal jeg på en eller anden måde finde ud af positionen for den sidste forekomst af kommaet og derefter udtrække alt til venstre for det.
Nedenfor er formlen, der vil gøre det
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Ovenstående formel bruger LEN -funktionen til at finde tekstens samlede længde i cellen samt længden af teksten uden komma.
Når jeg trækker disse to værdier fra, giver det mig det samlede antal kommaer i cellen.
Så det ville give mig 3 for celle A2 og 2 for celle A4.
Denne værdi bruges derefter inden for SUBSTITUTE -formlen til at erstatte det sidste komma med et udråbstegn. Og så kan du bruge den venstre funktion til at udtrække alt, hvad der er til venstre for udråbstegnet (hvor det sidste komma plejede at være)
Som du kan se i eksemplerne, kan du håndtere mange forskellige situationer ved hjælp af en kombination af tekstformler.
Da resultatet også er knyttet til de originale data, opdateres resultatet automatisk, når du ændrer de originale data.
Fjern tekst ved hjælp af Flash Fill
Flash Fill er et værktøj, der blev introduceret i Excel 2013 og er tilgængeligt i alle versionerne derefter.
Det fungerer ved at identificere mønstre, når du manuelt indtaster dataene og derefter ekstrapoleres for at give dig dataene for hele kolonnen.
Så hvis du vil fjerne teksten før eller efter et bestemt tegn, skal du bare vise flash -fe, hvordan resultatet ville se ud (ved at indtaste det manuelt et par gange), og flashfyld ville automatisk forstå mønsteret og give dig resultaterne.
Lad mig vise dig dette med et eksempel.
Nedenfor har jeg det datasæt, hvor jeg vil fjerne al teksten efter kommaet.
Her er trinene til at gøre dette ved hjælp af Flash Fill:
- I celle B2, som er den tilstødende kolonne i vores data, skal du indtaste 'Jeffery Haggins' manuelt (hvilket er det forventede resultat)
- I celle B3 skal du indtaste 'Tim Scott' (hvilket er det forventede resultat for den anden celle)
- Vælg området B2: B10
- Klik på fanen Startside
- I gruppen Redigering skal du klikke på rullemenuen Udfyld
- Klik på Flash Fill
Ovenstående trin ville give dig resultatet som vist herunder:
Du kan også bruge tastaturgenvejen Flash Fill Control + E efter markering af cellerne i resultatkolonnen (kolonne B i vores eksempel)
Flash Fill er et vidunderligt værktøj, og i de fleste tilfælde er det i stand til at genkende mønsteret og give dig det rigtige resultat. men i nogle tilfælde kan det muligvis ikke genkende mønsteret korrekt og kan give dig de forkerte resultater.
Så sørg for at dobbelttjekke dine Flash Fill-resultater
Og ligesom vi har fjernet al teksten efter et bestemt tegn ved hjælp af flash fill, kan du bruge de samme trin til at fjerne teksten før et bestemt tegn. bare vis flash fyld, hvordan resultatet skal ligne mit internet manuelt i den tilstødende kolonne, og det ville gøre resten.
Fjern tekst ved hjælp af VBA (brugerdefineret funktion)
Hele konceptet med at fjerne teksten før eller efter en bestemt karakter er afhængig af at finde karakterens position.
Som om det er set ovenfor, betyder det at finde den sidste forekomst af denne karakter god at bruge en sammensætning af formler.
Hvis dette er noget, du skal gøre ganske ofte, kan du forenkle denne proces ved at oprette en brugerdefineret funktion ved hjælp af VBA (kaldet brugerdefinerede funktioner).
Når den er oprettet, kan du genbruge denne funktion igen og igen. Det er også meget enklere og lettere at bruge (da det meste af de tunge løft udføres af VBA -koden i bagenden).
Under VBA -koden, som du kan bruge til at oprette den tilpassede funktion i Excel:
Funktion LastPosition (rCell As Range, rChar As String) 'Denne funktion giver den sidste position af det angivne tegn' Denne kode er udviklet af Sumit Bansal (https://trumpexcel.com) Dim rLen som heltal rLen = Len (rCell) For i = rLen Til 1 Trin -1 Hvis Midt (rCell, i - 1, 1) = rChar Så er LastPosition = i - 1 Afslut funktion Afslut hvis næste i Afslut funktion
Du skal placere VBA -koden i et almindeligt modul i VB -editoren eller i den personlige makro -projektmappe. Når du har det der, kan du bruge det som enhver anden almindelig regnearksfunktion i projektmappen.
Denne funktion tager 2 argumenter:
- Cellehenvisningen, som du vil finde den sidste forekomst af et bestemt tegn eller en tekststreng til
- Tegnet eller tekststrengen, hvis position du skal finde
Antag, at du nu har nedenstående datasæt, og du vil fjerne al teksten efter det sidste komma og kun have teksten før det sidste komma.
Nedenfor er formlen, der gør det:
= LEFT (A2, LastPosition (A2, ",")-1)
I ovenstående formel har jeg angivet at finde placeringen af det sidste komma. Hvis du vil finde placeringen af et andet tegn eller en tekststreng, skal du bruge det som det andet argument i funktionen.
Som du kan se, er dette meget kortere og lettere at bruge i forhold til den lange tekstformel, som vi brugte det foregående afsnit
Hvis du lægger VBA -koden i et modul i en projektmappe, ville du kun kunne bruge denne brugerdefinerede funktion i den specifikke projektmappe. Hvis du vil bruge dette i alle projektmapper på dit system, skal du kopiere og indsætte denne kode i den personlige makro -projektmappe.
Så det er nogle af de mails, du kan bruge til hurtigt at fjerne teksten før eller efter et bestemt tegn i Excel.
Hvis det er en enkelt engangs ting, kan du gøre det ved at finde og erstatte. hvad hvis det er lidt mere komplekst, så skal du bruge en kombination af indbyggede Excel -formler eller endda oprette din egen tilpassede formel ved hjælp af VBA.
Jeg håber, at du fandt denne vejledning nyttig.
