Sådan deles tal og tekst fra streng i Excel

Mange gange får jeg blandede data fra felt og server til analyse. Disse data er normalt beskidte, idet kolonnen er blandet med nummer og tekst. Mens jeg foretager datarensning før analyse, adskiller jeg tal og tekst i separate kolonner. I denne artikel fortæller jeg dig, hvordan du kan gøre det.

Scenarie:
Så en af ​​vores venner på Exceltip.com stillede dette spørgsmål i kommentarfeltet. “Hvordan adskiller jeg tal, der kommer før en tekst og i slutningen af ​​teksten ved hjælp af Excel -formel. For eksempel 125EvenueStreet og LoveYou3000 osv. ”

For at udtrække tekst bruger vi HØJRE, VENSTRE, MID og andre tekstfunktioner. Vi skal bare kende antallet af tekster, der skal udtrækkes. Og her vil vi først gøre det samme.
Udtræk nummer og tekst fra en streng, når tallet er i slutningen af ​​en streng
For ovenstående eksempel har jeg udarbejdet dette ark. I celle A2 har jeg strengen. I celle B2 vil jeg have tekstdelen og i C2 nummerdelen.

Så vi mangler bare at kende positionen, hvorfra tallet starter. Derefter vil vi bruge Venstre og anden funktion. Så for at få placeringen af ​​første nummer bruger vi under den generiske formel:
Generisk formel for at få placeringen af ​​første tal i strengen:

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

Dette vil returnere placeringen af ​​første nummer.
For ovenstående eksempel skriver du denne formel i en hvilken som helst celle.

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

Uddrag tekstdel

Det returnerer 15, da det første tal, der findes, er på 15. position i Tekst. Jeg vil forklare det senere.

Nu, for at få tekst, skal vi bare fra venstre få 15-1 tegn fra strengen. Så vi vil bruge
VENSTRE funktion for at udtrække tekst.
Formel til udtrækning af tekst fra venstre

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

Her har vi lige trukket 1 fra det nogensinde tal returneret af MIN (SØG ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789")).
Uddrag nummer del

Nu for at få tal mangler vi bare at få taltegn fra 1. nummer fundet. Så vi beregner den samlede længde på snor og træk placeringen af ​​det første tal fundet og tilføj 1 til det. Enkel. Ja, det lyder bare komplekst, det er enkelt.
Formel til at udtrække tal fra højre

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

Her fik vi bare den totale længde af strengen ved hjælp af LEN -funktionen og trak derefter placeringen af ​​det første fundne nummer og tilføjede derefter 1 til det. Dette giver os det samlede antal tal. Lær mere her om udtrækning af tekst ved hjælp af VENSTRE og HØJRE funktioner i Excel.

Så funktionen VENSTRE og HØJRE er enkel. Den vanskelige del er MIN og SEARCH -del, der giver os positionen som først fundet nummer. Lad os forstå det.
Hvordan det virker
Vi ved, hvordan VENSTRE og HØJRE funktion fungerer. Vi vil undersøge hoveddelen af ​​denne formel, der får placeringen af ​​første tal fundet, og det er: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
SEARCH -funktionen returnerer placeringen af ​​en tekst i en streng. SEARCH ('tekst', 'streng') -funktionen tager to argumenter, først den tekst, du vil søge efter, den anden streng, som du vil søge i.

• • Her i SØG har vi ved tekstposition en række tal fra 0 til 9. Og i strengposition har vi streng, der er sammenkædet med "0123456789" ved hjælp af & operatør. Hvorfor? Det skal jeg sige dig.
  • Hvert element i arrayet {0,1,2,3,4,5,6,7,8,9} søges i en given streng og returnerer sin position i array -formstreng ved samme indeks i array.
  • Hvis der ikke findes en værdi, forårsager det en fejl. Derfor vil al formel resultere i en fejl. For at undgå dette sammenføjede vi tallene "0123456789" i tekst. Så det altid finder hvert tal i en streng. Disse tal er i sidste ende derfor vil det ikke forårsage noget problem.
  • Nu returnerer MIN -funktionen den mindste værdi fra array returneret af SEARCH -funktionen. Denne mindste værdi vil være det første tal i strengen. Nu ved hjælp af denne NUMBER og VENSTRE og HØJRE funktion, kan vi opdele teksten og strengdelene.

Lad os undersøge vores eksempel. I A5 har vi den streng, der har gadenavn og husnummer. Vi skal adskille dem i forskellige celler.
Lad os først se, hvordan vi fik vores placering som første tal i streng.

• • MIN (SØG ({0,1,2,3,4,5,6,7,8,9}, A5 og "0123456789")): dette oversættes til MIN (SØG ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Nu, som jeg forklarede, vil søgning søge efter hvert tal i array {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 og vil returnere sin position i en matrixform. Det returnerede array vil være {8,9,6,11,12,13,14,7,16,17}. Hvordan?
0 søges i streng. Det findes i 8 position. Derfor er vores første element 8. Bemærk, at vores originale tekst kun er 7 tegn lang. Få det. 0 er ikke en del af Monta27.
Næste 1 vil blive søgt i streng, og den er heller ikke en del af den originale streng, og vi får dens position 9.
De næste 2 søges. Da det er en del af den originale streng, får vi dets indeks som 6.
Tilsvarende findes hvert element på en eller anden position.

• • Nu videregives denne matrix til MIN funktion som MIN ({8,9,6,11,12,13,14,7,16,17}). MIN returnerer den 6, som er placeringen af ​​det første tal, der findes i originalteksten.
    Og historien efter dette er ganske enkel. Vi bruger dette nummer udtræk tekst og tal ved hjælp af VENSTRE og HØJRE funktion.

Udtræk nummer og tekst fra en streng, når tallet er i begyndelsen af ​​en streng
I eksemplet ovenfor var Number i slutningen af ​​strengen. Hvordan udtrækker vi tal og tekst, når tallet er i begyndelsen.

Jeg har forberedt en lignende tabel som ovenfor. Det har bare nummer i begyndelsen.

Her vil vi bruge en anden teknik. Vi tæller længden af ​​numre (hvilket er 2 her) og udtrækker det antal tegn fra venstre for strengen.
Så metoden er = VENSTRE (streng, antal tal)
For at tælle antallet af tegn er dette formlen.
Generisk formel til at tælle antal tal:

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

Her,

• • • SUBSTITUTE -funktionen erstatter hvert nummer, der findes med “” (tomt). Hvis der findes et tal, der er substitueret, og en ny streng vil blive føjet til array, tilføjes anden klog original streng til arrayet. På denne måde har vi en række på 10 strenge.
    • Nu returnerer LEN -funktionen længden af ​​tegn i en række af disse strenge.
    • Derefter vil vi fra længden af ​​de originale strenge trække længden af ​​hver streng returneret af SUBSTITUTE -funktionen. Dette vil igen returnere en matrix.
    • Nu tilføjer SUM alle disse tal. Dette er antallet af tal i streng.

Uddrag nummerdel fra streng

Nu, da vi kender længden af ​​tal i snor, vi erstatter denne funktion i VENSTRE.
Da vi har vores streng en A11 vores:

Formel til at udtrække tal fra VENSTRE

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

Uddrag tekstdel fra streng

Da vi kender antallet af tal, kan vi trække det fra strengens samlede længde for at få talalfabeter i strengen og derefter bruge den rigtige funktion til at udtrække det antal tegn fra højre for strengen.

Formel til at udtrække tekst fra HØJRE

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

Hvordan det virker
Hoveddelen i begge formler er SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," ")))) der beregner den første forekomst af et tal. Først efter at have fundet dette, er vi i stand til at opdele tekst og tal ved hjælp af VENSTRE funktion. Så lad os forstå dette.

• • • SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Denne del returnerer en række strenge i A11 efter at have erstattet disse tal med ingenting/blank (“”). Til 27 Monta den returnerer {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
    • LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Nu er SUBSTITUTE -delen omsluttet af LEN -funktionen. Denne returlængde for tekster i array returneret af SUBSTITUTE -funktionen. Som et resultat har vi {7,7,6,7,7,7,7,6,7,7}.
    • LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9 "}," "))): Her trækker vi hvert tal, der returneres af ovenstående del, fra længden af ​​den faktiske streng. Originaltekstens længde er 7. Derfor har vi {7-7,7-7,7-6,….}. Endelig har vi {0,0,1,0,0,0,0,0,1,0,0}.
    • SUM (LEN (A11) -LEN (SKIFT (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "")))): Her brugte vi SUM til at opsummere den matrix, der returneres af en del af funktionen. Dette giver 2. Hvilket er antallet af tal i strengen.

Nu ved hjælp af dette kan vi udtrække teksterne og nummeret og dele dem i forskellige celler. Denne metode fungerer både med tekst, når tallet er i begyndelsen, og når det er slut. Du skal bare bruge VENSTRE og HØJRE funktion korrekt.
Brug SplitNumText -funktionen til at opdele tal og tekster fra en streng
Ovenstående metoder er lidt komplekse, og de er ikke nyttige, når tekst og tal blandes. Brug denne brugerdefinerede funktion til at opdele tekst og tal.

Syntaks:

= SplitNumText (streng, op)

Snor: Strengen, du vil dele.
Op: dette er boolsk. Bestå 0 eller falsk for at få tekstdel. For nummerdel, bestå sand eller et hvilket som helst tal større end 0.
For eksempel, hvis strengen er i A20,
Formel til udtrækning af tal fra streng er:

= SplitNumText (A20,1)

Og
Formel til udtrækning af tekst fra streng er:

= SplitNumText (A20,0)

Kopier nedenstående kode i VBA -modulet for at få ovenstående formel til at fungere.

Funktion 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 kode kontrollerer ganske enkelt hvert tegn i en streng, hvis det er et tal eller ej. Hvis det er et tal, gemmes det i num -variabel ellers i txt -variabel. Hvis brugeren passerer sand for op, returneres num, ellers returneres txt.

Dette er den bedste måde at opdele nummer og tekst fra en streng efter min mening.
Du kan downloade projektmappen her, hvis du vil.

Så ja fyre, det er måderne at dele tekst og tal på i forskellige celler. Lad mig vide, hvis du er i tvivl eller en bedre løsning i kommentarfeltet herunder. Det er altid sjovt at interagere med fyre.

Klik på nedenstående link for at downloade arbejdsfilen:

Opdel nummer og tekst fra en celle

Populære artikler:
50 Excel -genveje til at øge din produktivitet
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Sådan bruges SUMIF -funktionen i Excel