Når du arbeider med tekstdata i Excel, kan det hende du må fjerne teksten før eller etter et bestemt tegn eller en tekststreng.
For eksempel, hvis du har navn og betegnelsesdata til personer, kan det være lurt å fjerne betegnelsen etter kommaet og bare beholde navnet (eller omvendt der du beholder betegnelsen og fjerner navnet).
Noen ganger kan det gjøres med en enkel formel eller en rask Finn og erstatt, og noen ganger trenger den mer komplekse formler eller løsninger.
I denne opplæringen vil jeg vise deg hvordan du fjerner teksten før eller etter et bestemt tegn i Excel (ved hjelp av forskjellige eksempler).
Så la oss komme i gang med noen enkle eksempler.
Fjern tekst etter et tegn ved å bruke Finn og erstatt
Hvis du raskt vil fjerne all teksten etter en bestemt tekststreng (eller før en tekststreng), kan du gjøre det ved hjelp av Finn og erstatt og jokertegn.
Anta at du har et datasett som vist nedenfor, og du vil fjerne betegnelsen etter komma -tegnet og beholde teksten foran kommaet.
Nedenfor er trinnene for å gjøre dette:
- Kopier og lim inn dataene fra kolonne A til kolonne B (dette er også for å beholde de originale dataene)
- Når cellene i kolonne B er valgt, klikker du på kategorien Hjem
- I redigeringsgruppen klikker du på alternativet Finn og velg
- Klikk på alternativet Erstatt i alternativene som vises i rullegardinlisten. Dette åpner dialogboksen Finn og erstatt
- Skriv inn i "Finn hva" -feltet ,* (dvs. komma etterfulgt av et stjernetegn)
- La feltet «Erstatt med» stå tomt
- Klikk på knappen Erstatt alle
Trinnene ovenfor vil finne kommaet i datasettet og fjerne all teksten etter kommaet (inkludert kommaet).
Siden dette erstatter teksten fra de valgte cellene, er det en god idé å kopiere teksten til en annen kolonne og deretter utføre denne søke- og erstatningsoperasjonen, eller lage en sikkerhetskopi av dataene dine slik at du har de originale dataene intakte
Hvordan virker dette?
* (stjerne tegn) er et jokertegn som kan representere et hvilket som helst antall tegn.
Når jeg bruker det etter kommaet (i "Finn hva" -feltet) og klikker på Erstatt alle -knappen, finner det det første kommaet i cellen og anser det som et treff.
Dette er fordi stjernetegnet (*) regnes som en samsvar med hele tekststrengen som følger kommaet.
Så når du trykker på erstatt alle -knappen, erstatter den kommaet og all teksten som følger.
Merk: Denne metoden fungerer bra, da har du bare ett komma i hver celle (som i vårt eksempel). Hvis du har flere kommaer, vil denne metoden alltid finne det første kommaet og deretter fjerne alt etter det. Så du kan ikke bruke denne metoden hvis du vil erstatte all teksten etter det andre kommaet og beholde den første som den er.Hvis du vil fjerne alle tegnene før kommaet, kan du endre oppføringen for å finne hvilket felt ved å ha stjernetegnet foran kommaet (*, i stedet for,*)
Fjern tekst ved hjelp av formler
Hvis du trenger mer kontroll over hvordan du finner og erstatter tekst før eller etter et bestemt tegn, er det bedre å bruke de innebygde tekstformlene i Excel.
Anta at du har datasettet nedenfor der du vil fjerne all teksten etter kommaet.
Nedenfor er formelen for å gjøre dette:
= VENSTRE (A2, FIND (",", A2) -1)
Formelen ovenfor bruker FIND -funksjonen for å finne posisjonen til kommaet i cellen.
Dette posisjonsnummeret brukes deretter av VENSTRE -funksjonen til å trekke ut alle tegnene før kommaet. Siden jeg ikke vil ha komma som en del av resultatet, har jeg trukket 1 fra Finn -formelens resulterende verdi.
Dette var et enkelt scenario.
La oss ta en litt kompleks.
Anta at jeg har dette datasettet nedenfor der jeg vil fjerne all teksten etter det andre kommaet.
Her er formelen som vil gjøre dette:
= VENSTRE (A2, FINN ("!", SUBSTITUTE (A2, ",", "!", 2))-1)
Siden dette datasettet har flere kommaer, kan jeg ikke bruke FIND -funksjonen til å få posisjonen til det første kommaet og trekke ut alt til venstre for det.
Jeg må på en eller annen måte finne ut posisjonen til det andre kommaet og deretter trekke ut alt som er til venstre for det andre kommaet.
For å gjøre dette har jeg brukt SUBSTITUTE -funksjonen til å endre det andre kommaet til et utropstegn. Nå gir dette meg en unik karakter i cellen. Jeg kan nå bruke posisjonen til utropstegnet for å trekke ut alt til venstre for det andre kommaet.
Denne plasseringen av utropstegnet brukes i VENSTRE -funksjonen for å trekke ut alt før det andre kommaet.
Så langt så bra!
Men hva om det er et inkonsekvent antall kommaer i datasettet.
For eksempel i datasettet nedenfor har noen celler to kommaer og noen celler har tre kommaer, og jeg må trekke ut all teksten før det siste kommaet.
I dette tilfellet må jeg på en eller annen måte finne ut posisjonen til den siste forekomsten av kommaet og deretter trekke ut alt til venstre for det.
Nedenfor er formelen som vil gjøre det
= VENSTRE (A2, FINN ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Formelen ovenfor bruker LEN -funksjonen til å finne den totale lengden på teksten i cellen, så vel som lengden på teksten uten komma.
Når jeg trekker fra disse to verdiene, gir det meg det totale antallet kommaer i cellen.
Så det ville gi meg 3 for celle A2 og 2 for celle A4.
Denne verdien brukes deretter i SUBSTITUTE -formelen for å erstatte det siste kommaet med et utropstegn. Og så kan du bruke funksjonen til venstre for å trekke ut alt som er til venstre for utropstegnet (det er der det siste komma pleide å være)
Som du kan se i eksemplene, kan du håndtere mange forskjellige situasjoner ved å bruke en kombinasjon av tekstformler.
Siden resultatet også er koblet til de originale dataene, vil resultatet automatisk oppdateres når du endrer de originale dataene.
Fjern tekst ved hjelp av Flash Fill
Flash Fill er et verktøy som ble introdusert i Excel 2013 og er tilgjengelig i alle versjonene etter det.
Det fungerer ved å identifisere mønstre når du legger inn dataene manuelt og deretter ekstrapoleres for å gi deg dataene for hele kolonnen.
Så hvis du vil fjerne teksten før eller etter et bestemt tegn, trenger du bare å vise flash fairy hvordan resultatet ville se ut (ved å skrive det inn et par ganger manuelt), og flash fill ville automatisk forstå mønsteret og gi deg resultatene.
La meg vise deg dette med et eksempel.
Nedenfor har jeg datasettet der jeg vil fjerne all teksten etter kommaet.
Her er trinnene for å gjøre dette ved hjelp av Flash Fill:
- I celle B2, som er den tilstøtende kolonnen til dataene våre, angir du manuelt ‘Jeffery Haggins’ (som er det forventede resultatet)
- I celle B3 skriver du inn ‘Tim Scott’ (som er det forventede resultatet for den andre cellen)
- Velg området B2: B10
- Klikk på kategorien Hjem
- Klikk på rullegardinmenyen Fyll i redigeringsgruppen
- Klikk på Flash Fill
Trinnene ovenfor vil gi deg resultatet som vist nedenfor:
Du kan også bruke hurtigtasten Flash Fill Kontroll + E etter å ha valgt cellene i resultatkolonnen (kolonne B i vårt eksempel)
Flash Fill er et fantastisk verktøy, og i de fleste tilfeller kan den gjenkjenne mønsteret og gi deg det riktige resultatet. men i noen tilfeller kan det hende at den ikke kan gjenkjenne mønsteret riktig og kan gi deg feil resultat.
Så sørg for å dobbeltsjekke Flash Fill-resultatene
Og akkurat som vi har fjernet all teksten etter et bestemt tegn ved hjelp av blitsfyll, kan du bruke de samme trinnene for å fjerne teksten før et bestemt tegn. bare vis flash fyll hvordan resultatet skal se ut som Internett mitt manuelt i den tilstøtende kolonnen, og det ville gjøre resten.
Fjern tekst ved hjelp av VBA (egendefinert funksjon)
Hele konseptet med å fjerne teksten før eller etter en bestemt karakter er avhengig av å finne posisjonen til den karakteren.
Som om det er sett ovenfor, betyr det at du finner den siste forekomsten av den karakteren god å bruke en sammensetning av formler.
Hvis dette er noe du må gjøre ganske ofte, kan du forenkle denne prosessen ved å opprette en egendefinert funksjon ved hjelp av VBA (kalt User Defined Functions).
Når den er opprettet, kan du bruke denne funksjonen igjen og igjen. Det er også mye enklere og enklere å bruke (ettersom det meste av tunge løft utføres av VBA -koden i bakenden).
Under VBA -koden som du kan bruke til å lage den egendefinerte funksjonen i Excel:
Funksjon LastPosition (rCell As Range, rChar As String) 'Denne funksjonen gir den siste posisjonen til det angitte tegnet' Denne koden er utviklet av Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) For i = rLen Til 1 Trinn -1 Hvis Midt (rCell, i - 1, 1) = rChar Da er LastPosition = i - 1 Avslutt funksjon Slutt Hvis Neste i Avslutt funksjon
Du må plassere VBA -koden i en vanlig modul i VB Editor, eller i den personlige makro -arbeidsboken. Når du har den der, kan du bruke den som en hvilken som helst annen vanlig regnearkfunksjon i arbeidsboken.
Denne funksjonen tar to argumenter:
- Cellehenvisningen du vil finne den siste forekomsten av et bestemt tegn eller en tekststreng for
- Tegnet eller tekststrengen hvis posisjon du må finne
Anta at du nå har datasettet nedenfor, og at du vil fjerne all teksten etter det siste kommaet og bare ha teksten før det siste kommaet.
Nedenfor er formelen som vil gjøre det:
= LEFT (A2, LastPosition (A2, ",")-1)
I formelen ovenfor har jeg spesifisert for å finne posisjonen til det siste kommaet. Hvis du vil finne plasseringen til et annet tegn eller en tekststreng, bør du bruke det som det andre argumentet i funksjonen.
Som du kan se er dette mye kortere og enklere å bruke, sammenlignet med den lange tekstformelen som vi brukte den forrige delen
Hvis du legger VBA -koden i en modul i en arbeidsbok, vil du bare kunne bruke denne egendefinerte funksjonen i den spesifikke arbeidsboken. Hvis du vil bruke dette i alle arbeidsbøkene på systemet ditt, må du kopiere og lime inn denne koden i Personal Macro Workbook.
Så dette er noen av postene du kan bruke til å raskt fjerne teksten før eller etter et bestemt tegn i Excel.
Hvis det er en enkel engangs ting, kan du gjøre det ved å finne og erstatte. hva om det er litt mer komplekst, så må du bruke en kombinasjon av innebygde Excel -formler, eller til og med lage din egen tilpassede formel ved hjelp av VBA.
Jeg håper du synes denne opplæringen var nyttig.
