Hvordan å splitte tekststreng i Excel med komma, mellomrom, tegn eller maske
veiledningen forklarer hvordan å splitte celler i Excel ved hjelp av formler og Split Tekst-funksjonen. Du vil lære hvordan å skille tekst av komma, mellomrom eller andre skilletegn, og hvordan å splitte strenger inn tekst og tall.
Splitting tekst fra en celle i flere celler er en oppgave alle Excel-brukere har å gjøre med en gang i en stund., I en av våre tidligere artikler, har vi diskutert hvordan å splitte celler i Excel ved hjelp av Tekst til Kolonne funksjon, Flash Fyll og Delt Navn add-in. I dag er vi kommer til å ta en grundig titt på hvordan du kan dele opp strenger ved hjelp av formler og Split Tekst-funksjonen.,
- Hvordan å splitte celler i Excel ved hjelp av formler
- Split streng av komma, kolon, skråstrek, dash eller andre skilletegn
- Split streng av linjeskift
- Formler til å dele tekst og tall
- Hvordan å separate celler med Delt Tekst-funksjon
- Delt celle med tegnet
- Delt celle med string
- Split cellen ved maske (mønster)
Hvordan å splitte tekst i Excel ved hjelp av formler
for Å split streng i Excel, kan du vanligvis bruke den til VENSTRE, HØYRE eller MIDTEN av funksjon i kombinasjon med enten FINNE eller SØK., Ved første blikk, noen av formlene kan se komplisert, men logikken er faktisk ganske enkelt, og følgende eksempler vil gi deg noen ledetråder.
Split streng av komma, kolon, skråstrek, bindestrek eller andre skilletegn
Når splitte celler i Excel, er nøkkelen til å finne posisjonen til den skilletegn i tekststrengen. Avhengig av oppgaven din, dette kan gjøres ved hjelp av enten store eller små bokstaver SØK eller mellom store og små bokstaver SØK. Når du har skilletegn»s posisjon, bruk HØYRE, VENSTRE eller MIDT-funksjonen til å trekke ut den tilsvarende delen av tekststrengen., For bedre forståelse, let ‘ s vurdere følgende eksempel.
Når du har en liste over Skuer av Element-Farge-Size-mønster, og du ønsker å dele kolonnen inn i 3 separate kolonner:
Hvordan å splitte streng med linjeskift i Excel
for Å dele tekst med plass, bruke formler samme som vist i forrige eksempel. Den eneste forskjellen er at du må RØYE funksjonen til å levere linjeskift karakter siden du ikke kan skrive den direkte inn i en formel.,
Trodde, cellene som du ønsker å dele ligner på denne:
Ta formler fra forrige eksempel og erstatte en bindestrek («-«) med RØYE(10) hvor 10 er ASCII-koden for linjeskift.
Og dette er hvordan resultatet ser ut som:
Hvordan du kan dele tekst og tall i Excel
til Å begynne med, det er ingen universell løsning som ville fungere for alle alfanumeriske strenger. Som formel til å bruke, avhenger av den aktuelle strengen mønster. Nedenfor vil du finne formler for to vanlige scenarier.,
Split string «text + tall» mønster
Når du har en kolonne av strenger med tekst og tall kombinert, der et antall alltid følger teksten. Du ønsker å bryte den opprinnelige strenger, slik at tekst og tall vises i separate celler, som dette:
resultatet kan oppnås på to ulike måter.,
Metode 1: Teller tall og ekstrakt som mange tegn
Den enkleste måten å splitte tekststreng der antallet kommer etter tekst er dette:
for Å trekke ut numre, kan du søke på strengen for alle mulige tall fra 0 til 9, får tallene totalt, og avkastning som mange tegn fra slutten av strengen.,
Med den opprinnelige streng i A2, formelen går som følger:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
for Å trekke ut tekst, du beregne hvor mange tekst strengen inneholder ved å trekke fra antall uttrukne tall (C2) fra den totale lengden på den opprinnelige streng i A2. Etter det, kan du bruke VENSTRE-funksjonen for å gå tilbake som mange tegn fra begynnelsen av strengen.,
=LEFT(A2,LEN(A2)-LEN(C2))
Hvor A2 er den opprinnelige streng, og C2 er det hentet tall, som vist i skjermbildet:
Metode 2: Finne ut plasseringen av 1. siffer i en string
En alternativ løsning ville være å bruke følgende formel for å bestemme posisjonen til det første sifferet i streng:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
detaljert forklaring av formelen»s logikk kan bli funnet her.,
Når posisjonen til den første siffer er funnet, kan du dele tekst og tall ved hjelp av svært enkle VENSTRE og HØYRE formler.
for Å trekke ut tekst:
=LEFT(A2, B2-1)
for Å trekke ut nummer:
=RIGHT(A2, LEN(A2)-B2+1)
Hvor A2 er den opprinnelige streng, og B2 er posisjonen til den første nummeret., det første sifferet, kan du legge inn MIN formel i VENSTRE og HØYRE funksjoner:
Formel for å trekke ut tekst:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
Formel for å trekke ut numre:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
Split streng av «antall + tekst» mønster
Hvis du er å splitte celler der teksten vises etter nummer, kan du trekke ut numre med følgende formel:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
formelen er lik den som ble diskutert i forrige eksempel, bortsett fra at du bruker den VENSTRE funksjon i stedet for til HØYRE for å få nummeret fra venstre side av strengen.,
Når du har tallene, trekke ut tekst ved å trekke fra antall sifre fra den totale lengden på den opprinnelige streng:
=RIGHT(A2,LEN(A2)-LEN(B2))
Hvor A2 er den opprinnelige streng og B2 er det hentet tall, som vist i skjermbildet under:
Dette er hvordan du kan dele opp strenger i Excel ved hjelp av ulike kombinasjoner av de ulike funksjonene., Som du ser, formler er langt fra opplagt, så det kan hende du ønsker å laste ned eksempel Excel Split Cells arbeidsbok for å undersøke dem nærmere.
Om å finne ut av det okkulte vendinger av Excel formler er ikke din favoritt yrke, du kan gjerne den visuelle metoden for å dele celler i Excel, som er vist i neste del av denne opplæringen.,
Hvordan å splitte celler i Excel med Delt Tekst-funksjon
En alternative måte å dele en kolonne i Excel ved hjelp av Split Tekst-funksjon som er inkludert i vår Ultimate Suite for Excel, noe som gir følgende valg:
- Delt celle med tegnet
- Delt celle med string
- Split cellen ved maske (mønster)
for Å gjøre ting klarere, let ‘ s har en nærmere titt på hvert alternativ, ett av gangen.
Split celler med tegnet
Velg dette alternativet når du ønsker å dele celleinnhold på hver forekomst av den angitte tegn.,
For eksempel, la»s ta strenger av Element-Farge-Størrelse mønster som vi brukte i den første delen av denne opplæringen. Som du kanskje husker, vi delte dem i 3 forskjellige kolonner ved hjelp av 3 ulike formler. Og her»s hvordan du kan oppnå samme resultat i 2 rask fremgangsmåte:
- dersom du har Ultimate Suite er installert, velger du cellene å dele, og klikk på Delt Tekst-ikonet på Ablebits Data-fanen.,
- Split Tekst-panelet åpnes på høyre side av Excel-vinduet, og du gjør følgende:
- Utvide Delt av karakter gruppen, og velg en av de forhåndsdefinerte skilletegn eller skriv noen annen karakter i den Egendefinerte boksen.
- Velg enten å splitte celler, kolonner eller rader.
- Vurdering resultatet under Forhåndsvisning, og klikk på del-knapp.
Ferdig! Oppgaven som kreves 3 formler og 5 forskjellige funksjoner nå tar bare et par sekunder og et klikk på en knapp.
Split celler ved string
Dette alternativet kan du splitte strenger ved hjelp av en kombinasjon av tegn som skilletegn. Teknisk, deler du en streng inn deler ved hjelp av én eller flere forskjellige substrings som grensene for hver enkelt del.,
For eksempel, for å splitte en setning av konjunksjoner «og» og «eller», utvide Delt av strenger gruppe og angi skilletegn strenger, ett per linje:
Som et resultat, kilde uttrykket er adskilt på hver forekomst av hver skilletegn:
Og her en annen, real-life eksempel., Anta at du»har importert en kolonne med datoer fra en ekstern kilde, som ser ut som følger:
5.1.2016 12:20
5.2.2016 14:50
Dette formatet er ikke vanlig for Excel, og derfor er ingen av Dato funksjoner vil kjenne igjen noen av dato eller klokkeslett elementer. For å split dag, måned, år, timer og minutter i separate celler, skriver du inn følgende tegn i Split med strenger boksen:
- Dot (.,) til å skille dag, måned og år
- Kolon (:) til å skille timer og minutter
- Mellomrom for å skille dato og tid
trykk på del-knapp, og du vil umiddelbart få resultatet:
Split celler ved maske (mønster)
som Skiller en celle ved maske betyr å splitte en streng basert på et mønster.
Dette alternativet kommer i hendig når du trenger å dele en liste over homogen strenger inn noen elementer, eller substrings., Den komplikasjon er at teksten kan ikke være delt på hver forekomst av en gitt skilletegn, bare på noen bestemt forekomst(s). Følgende eksempel vil gjøre ting enklere å forstå.
Når du har en liste over strenger som er hentet ut fra noen logg-fil:
Hva du vil er å ha dato og klokkeslett, hvis noen, feil kode og unntak detaljer i 3 separate kolonner., Du kan ikke bruke mellomrom som skilletegn, fordi det ikke er mellomrom mellom dato og klokkeslett, som skal vises i en kolonne, og det er områder innenfor unntak tekst, som også skal vises i en kolonne.
løsningen er å splitte en streng av følgende maske: *FEIL:*Unntak:*
Hvor stjerne (*) står for et hvilket som helst antall tegn.
kolon (:), er inkludert i skilletegn fordi vi don»t vil vise dem i den resulterende celler.,
Og nå, utvide Delt av maske delen av Split Tekst-ruten, skriver the mask i Inn skilletegn-boksen, og klikk Split:
resultatet vil se ut som dette:
En stor fordel med denne metoden er fleksibilitet.,du vil de skal vises i forskjellige kolonner, kan du bruke denne maske:
* *FEIL:*Unntak:*
Oversatt til vanlig engelsk, maske instruerer add-i å dele opprinnelige strenger inn i 4 deler:
- Alle tegnene før 1. plass funnet i løpet av strengen (dato)
- Tegn mellom 1. plass og ordet FEIL: (time)
- Tekst mellom FEIL og Unntak: (feilkode)
- Alt som kommer etter Unntak: (unntak tekst)
jeg håper du likte denne rask og grei måte å splitte strenger i Excel., Hvis du er nysgjerrig på å gi den en prøve, en evaluering versjon er tilgjengelig for nedlasting nedenfor. Jeg takker for at du leser og håper å se deg på bloggen vår neste uke!
Tilgjengelige nedlastinger
Excel Split Cells formler (.xlsx fil)
Ultimate Suite 14-dagers fullt funksjonell versjon (.,zip-fil)
- Hvordan å splitte celler i Excel
- Hvordan for å skille navn i Excel: split første og siste navn i forskjellige kolonner
- Hvordan å splitte dato og klokkeslett i Excel
- Hvordan å fusjonere to kolonner i Excel uten å miste data