Mes išmokome pašalinti „Excel 2016“ ir naujesnių versijų langelio skaitines reikšmes. Mūsų naudojamos formulės buvo šiek tiek sudėtingos, tačiau dabar „Excel 2019“ ir „365“ yra žaidime.
„Excel 2019“ ir „365“ pristato kai kurias naujas funkcijas, kurios palengvina užduotį pašalinti neskaitinius simbolius ir iš naujo gauti tik skaitines reikšmes. Mes naudosime formules, kurios gali mums padėti tai padaryti patogiau.
Bendra formulė
| =TEXTJOIN("",TIESA,IFERROR(MID(sumaišytas_tekstas,SEKA(NumChars), 1) +0, "")) |
Sumišęs_tekstas: Tai yra šaltinio tekstas, iš kurio norite išgauti visas skaitines reikšmes.
„NumChars“: Tai yra bendras simbolių skaičius, kurį norite apdoroti. „Jumbled_text“ neturėtų būti daugiau simbolių nei šis skaičius (simboliai ir skaičiai kartu).
Pažiūrėkime pavyzdį, kad viskas būtų aišku.
Pavyzdys: pašalinkite neskaitinius simbolius ir ištraukite visus skaičius
Taigi, mes turime šiek tiek sumišusio teksto. Šiame tekste yra keletas skaičių ir kai kurie ne skaitiniai simboliai. Man reikia atsikratyti neskaitinių simbolių ir gauti skaitines reikšmes tik stulpelyje D.
Nemanau, kad bendras simbolių skaičius sumaišytame tekste bus didesnis nei 20. Taigi „NumChars“ vertė čia yra 20. Jei reikia, galite padidinti šį skaičių.
Čia naudokite aukščiau pateiktą bendrąją formulę, kad pašalintumėte ne skaitinius simbolius.
| =TEXTJOIN("",TIESA,IFERROR(MID(C3,SEKA(20),1)+0,"")) |
Ir kai paspausite įvesties mygtuką. Jūs pašalinsite visus ne skaitinius simbolius. Vilkite žemyn šią formulę, kad pašalintumėte simbolius iš eilutės iš visų C3 stulpelio langelių.
Kaip tai veikia?
Pirmiausia pažiūrėkime, kaip ši formulė išspręsta žingsnis po žingsnio.
| 1-> TEXTJOIN("",TIESA,IFERROR(MID(C3,SEKA(20),1)+0,"")) |
| 2-> TEXTJOIN("",TIESA,IFERROR(MID("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",TIESA,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; "" ; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("",TIESA,IFERROR({1; 2; #VALUE!;#VALUE!;#VALUE!; 1; 2; #VALUE!; 1; 2; 3; #VALUE!;#VALUE!;…; #VALUE!}+0,"")) |
| 5-> TEXTJOIN("",TIESA,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Kaip matote, formulė pradedama spręsti iš vidaus. Iš pradžių išsprendžiama SEQUENCE funkcija. Kadangi praėjome 20. Tai grąžina skaičių masyvą, prasidedantį nuo 1 iki 20.
Šis masyvas yra naudojamas MID funkcijai kaip pradinis numeris. Funkcija vidurys eina į kiekvieną eilutės indeksą ir padalija kiekvieną simbolį. Tai galite pamatyti atlikdami 3 veiksmą.
Toliau prie kiekvieno simbolio pridedame 0. „Excel“, jei bandote pridėti skaičių prie neskaitinių simbolių, gaunamas #VALUE! Klaida. Taigi gauname skaičių masyvą ir #VALUE! Klaidos. Ne skaitiniai simboliai dingo.
Kita funkcija IFERROR pakeičia visas #VALUE klaidas „“ (tuščia). Dabar mums lieka skaitinės vertės ir tarpai.
Galiausiai šis masyvas pateikiamas funkcijai TEXTJOIN. Funkcija TEXTJOIN juos sujungia ir mes gauname eilutę, kurioje yra tik skaičiai.
Formulės tobulinimas
Aukščiau pateiktoje formulėje simbolių skaičiui apdoroti buvo naudojamas koduotas skaičius (mes paėmėme dvidešimt). Bet galbūt norėsite, kad jis būtų dinamiškas. Tokiu atveju jūs atspėjote teisingai, mes naudosime LEN funkciją. Apdorojimui reikės tikslaus simbolių skaičiaus. Taigi formulė bus.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(sumaišytas_tekstas)),1)+0,"")) |
Čia LEN funkcija automatiškai aptiks tikslius skaičių simbolius raidinėje ir skaitinėje eilutėje. Tai palengvins didžiausios simbolių skaičiaus nustatymo naštą.
Funkcijos SEQUENTA alternatyva
Jei nenorite naudoti SEQUENCE funkcijos, eilės numeriams generuoti galite naudoti ROW ir INDIRECT funkcijos derinį.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,EILUTĖ(NETIESIOGINIS("1:"&LEN(sumaišytas_tekstas))),1)+0,"")) |
INDIRECT konvertuos tekstą ("1:20") į faktinį diapazoną, o tada ROW funkcija išvardys visus eilutės numerius nuo 1 iki 20. (20 yra tik, pavyzdžiui, tai gali būti bet koks skaičius).
Taigi taip, vaikinai, „Excel“ galite nuplėšti ne skaitinius simbolius iš raidinės ir skaitinės eilutės. Tikiuosi, kad buvau pakankamai aiškus ir šis straipsnis jums padėjo. Jei turite klausimų šia ar kita „Excel“/VBA tema. Iki tol išlaikykite „Excelling“.
Padalinkite skaičius ir tekstą iš eilutės „Excel 2016“ ir senesnėse versijose: Kai neturėjome funkcijos TEXTJOIN, mes naudojome LEFT ir RIGHT funkcijas su kai kuriomis kitomis funkcijomis, norėdami atskirti skaitinius ir neskaitinius simbolius iš eilutės.
Ištraukite tekstą iš eilutės „Excel“ naudodami „Excel“ kairės ir dešinės funkciją: Norėdami pašalinti tekstą „Excel“ iš eilutės, galime naudoti „Excel“ funkciją LEFT and RIGHT. Šios funkcijos padeda mums dinamiškai pjaustyti eilutes.
„Excel“ pašalinkite iš teksto priekines ir galines tarpus: Pirmaujančias ir galines erdves sunku atpažinti vizualiai ir jos gali sujaukti jūsų duomenis. Šių simbolių pašalinimas iš eilutės yra pagrindinė ir svarbiausia duomenų valymo užduotis. Štai kaip galite tai padaryti lengvai „Excel“.
Pašalinkite simbolius iš dešinės: Norėdami pašalinti simbolius iš dešinės eilutės „Excel“, naudojame funkciją LEFT. Taip, funkcija LEFT. Funkcija LEFT išsaugo nurodytą skaičių simbolių iš LEFT ir pašalina viską iš dešinės.
Pašalinkite nepageidaujamus simbolius „Excel“: Norėdami pašalinti nepageidaujamus simbolius iš „Excel“ eilutės, naudojame funkciją SUBSTITUTE. Funkcija SUBSTITUTE pakeičia nurodytus simbolius kitu simboliu ir sukuria naują pakeistą eilutę.
Kaip pašalinti tekstą „Excel“, pradedant nuo „Excel“ pozicijos: Norėdami pašalinti tekstą iš pradinės eilutės vietos, naudojame „Excel“ funkciją REPLACE. Ši funkcija padeda mums nustatyti pradinę poziciją ir išimamų simbolių skaičių.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis „Excel VLOOKUP“ funkcija| Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
Kaip naudotis „Excel“ Funkcija COUNTIF| Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.