Hvordan dele tall og tekst fra streng i Excel

Mange ganger får jeg blandede data fra felt og server for analyse. Disse dataene er vanligvis skitne, med kolonne blandet med tall og tekst. Mens jeg gjør datarensing før analyse, skiller jeg tall og tekst i separate kolonner. I denne artikkelen skal jeg fortelle deg hvordan du kan gjøre det.

Scenario:
Så en av våre venner på Exceltip.com stilte dette spørsmålet i kommentarfeltet. “Hvordan skiller jeg tall som kommer foran en tekst og i slutten av teksten ved hjelp av Excel Formula. For eksempel 125EvenueStreet og LoveYou3000 etc. ”

For å trekke ut tekst bruker vi RIGHT, LEFT, MID og andre tekstfunksjoner. Vi trenger bare å vite antall tekster vi skal trekke ut. Og her vil vi gjøre det samme først.
Trekk ut tall og tekst fra en streng når tallet er i slutten av strengen
For eksemplet ovenfor har jeg utarbeidet dette arket. I celle A2 har jeg strengen. I celle B2 vil jeg ha tekstdelen og i C2 nummerdelen.

Så vi trenger bare å vite posisjonen hvor tallet starter. Deretter bruker vi Venstre og annen funksjon. Så for å få posisjonen til første nummer bruker vi under generisk formel:
Generisk formel for å få posisjonen til første nummer i strengen:

= MIN (SØK ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Dette vil returnere posisjonen til det første nummeret.
For eksempelet ovenfor, skriv denne formelen i en hvilken som helst celle.

= MIN (SØK ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789"))

Trekk ut tekstdel

Det vil returnere 15 som det første tallet som blir funnet, er på 15. plass i Tekst. Jeg vil forklare det senere.

Nå, for å få tekst, fra venstre trenger vi bare å få 15-1 tegn fra strengen. Så vi vil bruke
VENSTRE -funksjon for å trekke ut tekst.
Formel for å trekke ut tekst fra venstre

= VENSTRE (A5, MIN (SØK ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789"))-1)

Her har vi nettopp trukket fra 1 fra hvilket tall som er returnert av MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789")).
Extract Number Part

Nå for å få tall trenger vi bare å få talltegn fra første nummer funnet. Så vi beregner den totale lengden på streng og trekk fra posisjonen til det første tallet som er funnet, og legg til 1 til det. Enkel. Ja, det høres bare komplekst ut, det er enkelt.
Formel for å trekke ut tall fra høyre

= HØYRE (A5, LEN (A5) -MIN (SØK ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789"))+1)

Her fikk vi bare total lengde på strengen ved å bruke LEN -funksjonen og trakk deretter posisjonen til det første funnet nummeret og la deretter 1 til det. Dette gir oss totalt antall tall. Lær mer her om å trekke ut tekst ved hjelp av VENSTRE og HØYRE funksjoner i Excel.

Så funksjonen VENSTRE og HØYRE er enkel. Den vanskelige delen er MIN og SEARCH Part som gir oss posisjonen til først funnet nummer. La oss forstå det.
Hvordan det fungerer
Vi vet hvordan funksjonen VENSTRE og HØYRE fungerer. Vi vil utforske hoveddelen av denne formelen som får posisjonen til det første tallet funnet, og det er: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
SEARCH -funksjonen returnerer plasseringen til en tekst i en streng. SEARCH ('tekst', 'streng') -funksjonen tar to argumenter, først teksten du vil søke etter, den andre strengen du vil søke i.

- Her i SØK, ved tekstposisjon har vi en rekke tall fra 0 til 9. Og i strengposisjon har vi streng som er sammenkoblet med "0123456789" ved hjelp av & operatør. Hvorfor? Jeg skal fortelle deg.
- Hvert element i matrisen {0,1,2,3,4,5,6,7,8,9} vil bli søkt i en gitt streng og vil returnere sin posisjon i matriseformstrengen ved samme indeks i matrisen.
- Hvis noen verdi ikke blir funnet, vil det forårsake en feil. Derfor vil all formel resultere i en feil. For å unngå dette sammenføyte vi tallene "0123456789" i tekst. Slik at det alltid finner hvert tall i strengen. Disse tallene er til slutt, og vil derfor ikke forårsake noe problem.
- Nå returnerer MIN -funksjonen den minste verdien fra matrisen returnert av SEARCH -funksjonen. Denne minste verdien vil være det første tallet i strengen. Når vi bruker denne NUMBER- og VENSTRE- og HØYRE -funksjonen, kan vi dele teksten og strengdelene.

La oss undersøke vårt eksempel. I A5 har vi strengen som har gatenavn og husnummer. Vi må skille dem i forskjellige celler.
La oss først se hvordan vi fikk vår posisjon som første nummer i strengen.

- MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789")): dette vil oversette til MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Nå, som jeg forklarte, vil søket søke etter hvert tall i matrisen {0,1,2,3,4,5,6,7,8,9} tommer Monta270123456789 og vil returnere sin posisjon i en matriseform. Den returnerte gruppen vil være {8,9,6,11,12,13,14,7,16,17}. Hvordan?
0 vil bli søkt i streng. Det er funnet på 8 posisjon. Derfor er vårt første element 8. Merk at originalteksten bare er 7 tegn lang. Skjønner. 0 er ikke en del av Monta27.
Neste 1 vil bli søkt i streng, og den er heller ikke en del av originalstrengen, og vi får posisjon 9.
De neste 2 vil bli søkt. Siden det er delen av den originale strengen, får vi indeksen som 6.
På samme måte finnes hvert element i en bestemt posisjon.

- Nå overføres denne matrisen til MIN -funksjonen som MIN ({8,9,6,11,12,13,14,7,16,17}). MIN returnerer 6 -tallet som er posisjonen til det første tallet som ble funnet i originalteksten.
  Og historien etter dette er ganske enkel. Vi bruker dette nummeret og trekker ut tekst og tall ved hjelp av VENSTRE og HØYRE funksjon.

Trekk ut tall og tekst fra en streng når tallet er i begynnelsen av en streng
I eksemplet ovenfor var Number i enden av strengen. Hvordan trekker vi ut tall og tekst når tallet er i begynnelsen.

Jeg har forberedt et lignende bord som ovenfor. Den har bare tall i begynnelsen.

Her vil vi bruke en annen teknikk. Vi teller lengden på tallene (som er 2 her) og trekker ut det antallet tegn fra venstre på strengen.
Så metoden er = VENSTRE (streng, antall tall)
For å telle antall tegn er dette formelen.
Generisk formel for å telle antall tall:

= SUMME (LEN (streng) -LEN (SUBSTITUTE (streng, {"0", "1", "2", "3", "4", "5", "6", "7", "8") , "9"}, ""))

Her,

- - SUBSTITUTE -funksjonen erstatter hvert nummer som er funnet med “” (tomt). Hvis et tall blir funnet erstattet og ny streng vil bli lagt til matrisen, vil en annen klok originalstreng bli lagt til i matrisen. På denne måten vil vi ha en rekke på 10 strenger.
  - Nå vil LEN -funksjonen returnere lengden på tegn i en rekke av disse strengene.
  - Deretter vil vi fra lengden på de originale strengene trekke fra lengden på hver streng som returneres av SUBSTITUTE -funksjonen. Dette vil igjen returnere en matrise.
  - Nå vil SUM legge til alle disse tallene. Dette er antall tall i strengen.

Pakk ut nummerdel fra streng

Nå siden vi vet lengden på tallene i streng, vi erstatter denne funksjonen til VENSTRE.
Siden vi har strengen vår en A11 vår:

Formel for å trekke ut tall fra VENSTRE

= VENSTRE (A11, SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7") , "8", "9"}, "")))))

Pakk ut tekstdel fra streng

Siden vi vet antall tall, kan vi trekke det fra strengens totale lengde for å få tallalfabeter i strengen og deretter bruke høyre funksjon for å trekke ut det antallet tegn fra høyre på strengen.

Formel for å trekke ut tekst fra HØYRE

= HØYRE (A11, LEN (A2) -SUM (LEN (A11) -LEN (ERSTATNING (A11, {"0", "1", "2", "3", "4", "5", "6) "," 7 "," 8 "," 9 "}," ")))))

Hvordan det fungerer
Hoveddelen i begge formlene er SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," ")))) som beregner den første forekomsten av et tall. Først etter å ha funnet dette, kan vi dele tekst og tall ved å bruke VENSTRE -funksjonen. Så la oss forstå dette.

- - ERSTATNING (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Denne delen returnerer en rekke strenger i A11 etter at disse tallene er erstattet med ingenting/tomt (“”). Til 27 Monta den returnerer {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
  - LENNE (ERSTATNING (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Nå er SUBSTITUTE -delen pakket inn med LEN -funksjonen. Denne returlengden på tekster i array returnert av SUBSTITUTE -funksjonen. Som et resultat vil vi ha {7,7,6,7,7,7,7,6,7,7}.
  - LEN (A11) -LEN (ERSTATNING (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9) "}," "))): Her trekker vi hvert tall som returneres av delen ovenfor fra lengden på den faktiske strengen. Lengden på originalteksten er 7. Derfor vil vi ha {7-7,7-7,7-6,….}. Til slutt vil vi ha {0,0,1,0,0,0,0,1,0,0}.
  - SUM (LENGE (A11) -LENG (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "")))): Her brukte vi SUM for å summere matrisen som returneres av en del av funksjonen. Dette vil gi 2. Som er antall tall i strengen.

Ved å bruke dette kan vi trekke ut tekstene og nummeret og dele dem i forskjellige celler. Denne metoden fungerer både med tekst, når tallet er i begynnelsen og når det er slutt. Du trenger bare å bruke funksjonen VENSTRE og HØYRE på riktig måte.
Bruk SplitNumText -funksjonen til å dele tall og tekster fra en streng
Metodene ovenfor er litt komplekse, og de er ikke nyttige når tekst og tall blandes. Bruk denne brukerdefinerte funksjonen for å dele tekst og tall.

Syntaks:

= SplitNumText (streng, op)

Streng: Strengen du vil dele.
Op: dette er boolsk. Pass 0 eller falsk for å få tekstdel. For nummerdel, pass ekte eller et tall større enn 0.
For eksempel, hvis strengen er i A20,
Formelen for å trekke ut tall fra strengen er:

= SplitNumText (A20,1)

Og
Formelen for å trekke ut tekst fra strengen er:

= SplitNumText (A20,0)

Kopier koden nedenfor i VBA -modulen for å få formelen ovenfor til å fungere.

Funksjon SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Then num = num & Mid (str, i , 1) Else txt = txt & Mid (str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function

Denne koden sjekker ganske enkelt hvert tegn i strengen, om det er et tall eller ikke. Hvis det er et tall, lagres det i num -variabel ellers i txt -variabel. Hvis brukeren passerer true for op, blir num returnert ellers blir txt returnert.

Dette er den beste måten å dele nummer og tekst fra en streng etter min mening.
Du kan laste ned arbeidsboken her hvis du vil.

Så ja gutta, dette er måtene å dele tekst og tall i forskjellige celler. Gi meg beskjed hvis du er i tvil eller har en bedre løsning i kommentarfeltet nedenfor. Det er alltid gøy å ha kontakt med gutta.

Klikk på lenken nedenfor for å laste ned arbeidsfilen:

Del tall og tekst fra en celle

Populære artikler:
50 Excel -snarveier for å øke produktiviteten
VLOOKUP -funksjonen i Excel
COUNTIF i Excel 2016
Slik bruker du SUMIF -funksjonen i Excel