Kaip padalinti skaičius ir tekstą iš eilutės „Excel“

Anonim

Daug kartų gaunu mišrius duomenis iš lauko ir serverio analizei. Šie duomenys paprastai yra nešvarūs, stulpelis sumaišytas su skaičiumi ir tekstu. Valydamas duomenis prieš analizę, atskiriu skaičius ir tekstą atskiruose stulpeliuose. Šiame straipsnyje aš jums pasakysiu, kaip tai padaryti.

Scenarijus:
Taigi vienas mūsų draugas iš „Exceltip.com“ uždavė šį klausimą komentarų skiltyje. „Kaip atskirti prieš tekstą ir jo pabaigoje esančius skaičius naudojant„ Excel “formulę. Pavyzdžiui, 125EvenueStreet ir LoveYou3000 ir kt. “

Norėdami išgauti tekstą, naudojame dešinę, kairę, vidurinę ir kitas teksto funkcijas. Mums tereikia žinoti išimamų tekstų skaičių. Ir čia pirmiausia padarysime tą patį.
Ištraukite skaičių ir tekstą iš eilutės, kai skaičius yra eilutės pabaigoje
Aukščiau pateiktame pavyzdyje aš paruošiau šį lapą. A2 langelyje turiu eilutę. B2 langelyje noriu tekstinės dalies, o C2 - skaičių dalies.

Taigi mes tiesiog turime žinoti poziciją, nuo kurios prasideda skaičius. Tada mes naudosime kairę ir kitą funkciją. Taigi, norėdami gauti pirmojo skaičiaus poziciją, naudojame žemiau pateiktą bendrąją formulę:
Bendroji formulė pirmojo skaičiaus pozicijai gauti eilutėje:

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

Tai grąžins pirmojo numerio poziciją.
Pirmiau pateiktame pavyzdyje parašykite šią formulę bet kuriame langelyje.

= MIN (PAIEŠKA ({0,1,2,3,4,5,6,7,8,9}, A5 ir "0123456789"))

Ištraukti teksto dalį

Jis grąžins 15, nes pirmasis rastas skaičius yra 15 -oje teksto vietoje. Vėliau paaiškinsiu.

Dabar, norėdami gauti tekstą, iš kairės mums tiesiog reikia gauti 15-1 simbolį iš eilutės. Taigi mes naudosime
Funkcija LEFT tekstui išgauti.
Formulė tekstui ištraukti iš kairės

= LEFT (A5, MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)


Čia mes tiesiog atėmėme 1 iš MIN grąžinto skaičiaus (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 ir „0123456789“).
Ištraukite numerio dalį

Dabar, norėdami gauti skaičius, turime gauti skaičių simbolius iš pirmojo rasto skaičiaus. Taigi mes apskaičiuojame bendrą ilgį eilutė ir atimkite pirmojo rasto skaičiaus padėtį ir pridėkite 1 į jį. Paprasta. Taip, tai skamba sudėtingai, tai paprasta.
Skaičių ištraukimo iš dešinės formulė

= DEŠINĖ (A5, LEN (A5) -MIN (PAIEŠKA ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))+1)

Čia mes ką tik gavome bendrą eilutės ilgį naudodami LEN funkciją, tada atėmėme pirmojo rasto skaičiaus vietą ir tada pridėjome prie jos 1. Tai suteikia mums bendrą skaičių skaičių. Sužinokite daugiau čia apie teksto išgavimą naudojant „Excel“ kairės ir dešinės funkcijas.

Taigi LEFT ir RIGHT funkcijos dalis yra paprasta. Sudėtinga dalis yra MIN ir SEARCH dalis, kuri suteikia mums pirmojo rasto skaičiaus poziciją. Supraskime tai.
Kaip tai veikia
Mes žinome, kaip veikia kairė ir dešinė. Išnagrinėsime pagrindinę šios formulės dalį, kuri gauna pirmojo rasto skaičiaus poziciją ir yra: MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, eilutė & "0123456789 ")
Funkcija SEARCH grąžina teksto vietą eilutėje. Funkcija SEARCH („tekstas“, „eilutė“) apima du argumentus: pirmiausia tekstą, kurio norite ieškoti, antrą eilutę, kurioje norite ieškoti.

    • Čia, SEARCH, teksto padėtyje turime skaičių masyvą nuo 0 iki 9. O eilutės pozicijoje turime eilutę, kuri yra sujungta su „0123456789“ naudojant & operatorius. Kodėl? Aš tau pasakysiu.
    • Kiekvienas masyvo {0,1,2,3,4,5,6,7,8,9} elementas bus ieškomas pagal nurodytą eilutę ir grąžins savo vietą masyvo formos eilutėje tuo pačiu masyvo indeksu.
    • Jei nerandate jokios vertės, tai sukels klaidą. Taigi visos formulės sukels klaidą. Norėdami to išvengti, tekste sujungėme skaičius „0123456789“. Taigi jis visada randa kiekvieną skaičių eilutėje. Šie skaičiai yra galų gale, todėl nesukels jokių problemų.
    • Dabar funkcija MIN grąžina mažiausią reikšmę iš masyvo, kurį grąžina SEARCH funkcija. Ši mažiausia vertė bus pirmasis skaičius eilutėje. Dabar, naudodami šią funkciją NUMBER ir LEFT bei RIGHT, galime padalyti teksto ir eilutės dalis.

Panagrinėkime mūsų pavyzdį. A5 turime eilutę su gatvės pavadinimu ir namo numeriu. Turime juos atskirti skirtingose ​​ląstelėse.
Pirmiausia pažiūrėkime, kaip gavome pirmojo skaičiaus poziciją eilutėje.

    • MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): tai bus išversta į MIN (SEARCH ({0,1,2,3, 4,5,6,7,8,9} “Monta270123456789”))

Dabar, kaip paaiškinau, paieška ieškos kiekvieno skaičiaus masyve {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 ir grąžins savo poziciją masyvo forma. Grąžintas masyvas bus {8,9,6,11,12,13,14,7,16,17}. Kaip?
0 bus ieškoma eilutėje. Jis randamas 8 padėtyje. Taigi pirmasis mūsų elementas yra 8. Atminkite, kad mūsų originalus tekstas yra tik 7 simbolių. Gaukite. 0 nėra dalis Monta27.
Kitas 1 bus ieškomas eilutėje ir jis taip pat nėra originalios eilutės dalis, ir mes gauname 9 poziciją.
Kiti 2 bus ieškomi. Kadangi tai yra pradinės eilutės dalis, jos indeksą gauname kaip 6.
Panašiai kiekvienas elementas yra tam tikroje vietoje.

    • Dabar šis masyvas perduodamas MIN funkcijai kaip MIN ({8,9,6,11,12,13,14,7,16,17}). MIN grąžina 6, kuris yra pirmojo originalaus teksto skaičiaus vieta.
      Ir istorija po to yra gana paprasta. Mes naudojame šį skaičių ištraukos tekstą ir skaičius naudodami kairę ir dešinę funkcijas.

Ištraukite skaičių ir tekstą iš eilutės, kai skaičius yra eilutės pradžioje
Aukščiau pateiktame pavyzdyje skaičius buvo eilutės pabaigoje. Kaip išgauti skaičių ir tekstą, kai skaičius yra pradžioje.

Aš paruošiau panašią lentelę, kaip nurodyta aukščiau. Pradžioje yra tik numeris.

Čia mes naudosime kitokią techniką. Mes suskaičiuosime skaičių ilgį (čia yra 2) ir ištrauksime tą simbolių skaičių iš kairės eilutės.
Taigi metodas yra = LEFT (eilutė, skaičių skaičius)
Norėdami suskaičiuoti simbolių skaičių, tai yra formulė.
Bendroji formulė skaičių skaičiavimui:

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

Čia,

      • Funkcija „PAKEITIMAS“ kiekvieną rastą skaičių pakeis „“ (tuščia). Jei randamas pakeistas skaičius ir į masyvą įtraukiama nauja eilutė, į masyvą bus įtraukta kita išmintinga originali eilutė. Tokiu būdu turėsime 10 eilučių masyvą.
      • Dabar LEN funkcija grąžins simbolių ilgį tų eilučių masyve.
      • Tada iš originalių eilučių ilgio atimsime kiekvienos eilutės, kurią grąžino funkcija SUBSTITUTE, ilgį. Tai vėl grąžins masyvą.
      • Dabar SUM pridės visus šiuos skaičius. Tai skaičių skaičius eilutėje.

Ištraukite skaičių dalį iš eilutės

Dabar, kai žinome skaičių ilgį eilutė, šią funkciją pakeisime kairėje.
Kadangi mes turime savo eilutę A11, mūsų:

Skaičių ištraukimo iš kairės formulė

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


Ištraukite teksto dalį iš eilutės

Kadangi mes žinome skaičių skaičių, galime jį atimti iš viso eilutės ilgio, kad gautume skaičių abėcėlę eilutėje, o tada naudokite dešinę funkciją, kad išgautumėte tą simbolių skaičių iš dešinės eilutės.

Teksto ištraukimo iš dešinės formulė

= DEŠINĖ (A11, LEN (A2) -SUM (LEN (A11) -LEN (PAKAITA (A11, {"0", "1", "2", "3", "4", "5", "6) "," 7 "," 8 "," 9 "}," "))))


Kaip tai veikia
Pagrindinė abiejų formulių dalis yra SUM (LEN (A11) -LEN (PAKAITA (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))), kuris apskaičiuoja pirmąjį skaičiaus įvykį. Tik radę tai, mes galime padalinti tekstą ir skaičių naudodami LEFT funkciją. Taigi supraskime tai.

      • PAKAITA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Ši dalis grąžina eilutės masyvą A11, pakeisdama šiuos skaičius nieko/tuščia („“). Dėl 27Monta ji grąžins {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
      • LEN (PAKAITA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Dabar PAKEITIMO dalis yra apvyniota LEN funkcija. Šis grąžinamas masyvo tekstų ilgis grąžintas funkcija SUBSTITUTE. Dėl to turėsime {7,7,6,7,7,7,7,6,7,7}.
      • LEN (A11) -LEN (PAKAITA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9" "}," ")): Čia mes atimame kiekvieną skaičių, kurį grąžina aukščiau esanti dalis, iš faktinės eilutės ilgio. Originalo teksto ilgis yra 7. Taigi turėsime {7-7,7-7,7-6,….}. Galiausiai turėsime {0,0,1,0,0,0,0,1,0,0}.
      • SUM (LEN (A11) -LEN (PAKAITA (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "")))): čia mes panaudojome SUM, kad susumuotume masyvą, pateiktą aukščiau funkcijos dalies. Tai duos 2. Kuris yra skaičių skaičius eilutėje.

Dabar naudodami tai galime išgauti tekstus ir skaičių bei padalyti juos į skirtingas ląsteles. Šis metodas veiks su abiejų tipų tekstais, kai skaičius yra pradžioje ir kai jis baigiasi. Jums tiesiog reikia tinkamai naudoti kairę ir dešinę funkcijas.
Naudokite „SplitNumText“ funkciją, norėdami atskirti skaičius ir tekstus iš eilutės
Pirmiau aprašyti metodai yra šiek tiek sudėtingi ir nėra naudingi, kai tekstas ir skaičiai yra sumaišyti. Norėdami padalyti tekstą ir skaičius, naudokite šią vartotojo nustatytą funkciją.

Sintaksė:

= SplitNumText (eilutė, op)

Styga: Styga, kurią norite suskaidyti.
Op: tai yra loginis. Praeiti 0 arba klaidinga gauti teksto dalį. Dėl numerio dalies, praeikite tiesa arba bet kuris skaičius didesnis už 0.
Pavyzdžiui, jei eilutė yra A20,
Skaičių ištraukimo iš eilutės formulė yra tokia:

= SplitNumText (A20,1)

Ir
Teksto ištraukimo iš eilutės formulė yra tokia:

= SplitNumText (A20,0)

Nukopijuokite žemiau esantį kodą VBA modulyje, kad aukščiau pateikta formulė veiktų.

Funkcija SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Tada num = num & Mid (str, i , 1) Kita txt = txt ir vidurys (str, i, 1) Pabaiga, jei kita i Jei op = Tiesa, tada SplitNumText = num Kita SplitNumText = txt Pabaiga, jei pabaiga

Šis kodas tiesiog patikrina kiekvieną simbolį eilutėje, ar jis yra skaičius, ar ne. Jei tai yra skaičius, jis saugomas kintamajame num kitame txt kintamajame. Jei vartotojas nurodo „true“ op, tada grąžinamas numeris, kitaip - txt.

Mano nuomone, tai yra geriausias būdas atskirti skaičių ir tekstą iš eilutės.
Jei norite, galite atsisiųsti darbaknygę čia.

Taigi taip, vaikinai, tai yra būdai, kaip padalinti tekstą ir skaičius į skirtingas ląsteles. Praneškite man, jei turite kokių nors abejonių ar geresnio sprendimo žemiau esančiame komentarų skyriuje. Visada smagu bendrauti su vaikinais.

Norėdami atsisiųsti darbo failą, spustelėkite žemiau esančią nuorodą:

Skaidyti skaičių ir tekstą iš langelio

Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti
„VLOOKUP“ funkcija „Excel“
COUNTIF „Excel 2016“
Kaip naudoti „SUMIF“ funkciją „Excel“