V Excelu 2016 in starejših smo se naučili, kako iz celice odstraniti številčne vrednosti. Formule, ki smo jih uporabili, so bile nekoliko zapletene, zdaj pa sta v igri Excel 2019 in 365.
Excel 2019 in 365 uvajata nekaj novih funkcij, ki olajšata odstranjevanje neštevilskih znakov in pridobivanje samo številskih vrednosti v novi celici. Uporabili bomo formule, ki nam bodo pri tem lahko pomagale, bolj priročno.
Splošna formula
| =TEXTJOIN("",PRAV,NAPAKA(MID(jumble_text,SEQUENCE(NumChars), 1) +0, ""))) |
Jumbled_text: To je izvorno besedilo, iz katerega želite izvleči vse številske vrednosti.
NumChars: To je skupno število znakov, ki jih želite obdelati. Jubled_text ne sme vsebovati več znakov od te številke (znaki in številke skupaj).
Poglejmo primer, da razjasnimo stvari.
Primer: Odstranite neštevilčne znake in izvlecite vsa števila
Torej imamo nekaj zmedenega besedila. To besedilo vsebuje nekaj številk in nekaj neštevilskih znakov. Znebiti se moram neštevilčnih znakov in dobiti številske vrednosti samo v stolpcu D.
Ne pričakujem, da bo skupno število znakov v zmedenem besedilu večje od 20. Torej je vrednost NumChars 20 tukaj. To število lahko po potrebi povečate.
Tukaj uporabite zgornjo generično formulo, da odstranite neštevilčne znake.
| =TEXTJOIN("",PRAV,NAPAKA(MID(C3,SEQUENCE(20),1)+0,"")) |
In ko pritisnete gumb za vnos. Odstranite vse neštevilčne znake. Povlecite to formulo navzdol, da odstranite znake iz niza iz vseh celic v stolpcu C3.
Kako deluje?
Najprej poglejmo, kako se ta formula po korakih rešuje.
| 1-> TEXTJOIN("",PRAV,NAPAKA(MID(C3,SEQUENCE(20),1)+0,"")) |
| 2-> TEXTJOIN("",PRAV,NAPAKA(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("",PRAV,NAPAKA({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; "" ; ""; ""; ""; ""; ""; ""; ""}}+0,"")) |
| 4-> TEXTJOIN("",PRAV,NAPAKA({1; 2; #VALUE!;#VALUE!;#VALUE!; 1; 2; #VALUE!; 1; 2; 3; #VALUE!;#VALUE!;…; #VALUE!}+0,"")) |
| 5-> TEXTJOIN("",PRAV,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Kot lahko vidite, se formula začne reševati od znotraj. Najprej je rešena funkcija SEQUENCE. Ker smo opravili 20. Vrne niz številk, ki se začnejo od 1 do 20.
Ta matrika je vročena funkciji MID kot začetna številka. Funkcija mid gre za vsak indeks v nizu in razdeli vsak znak. To lahko vidite v 3. koraku.
Nato vsakemu znaku dodamo 0. V Excelu, če poskušate dodati številke neštevilčnim znakom, dobite #VALUE! Napaka. Tako dobimo niz številk in #VALUE! Napake. Neštevilski znaki so zdaj izginili.
Naslednja funkcija IFERROR nadomesti vse napake #VALUE z "" (prazno). Zdaj nam ostanejo številske vrednosti in presledki.
Končno je ta niz vročen funkciji TEXTJOIN. Funkcija TEXTJOIN jih združi in dobimo niz, ki vsebuje samo številke.
Izboljšanje formule
Zgornja formula je uporabila trdo kodirano številko za obdelavo števila znakov (vzeli smo dvajset). Morda pa želite, da je dinamična. V tem primeru ste uganili, uporabili bomo funkcijo LEN. Za obdelavo bo potrebno natančno število znakov. Tako bo formula.
| = TEXTJOIN ("", TRUE, IFERROR (MID (zmešan_tekst, SEQUENCE (LEN(jumble_text)),1)+0,"")) |
Tu bo funkcija LEN samodejno zaznala natančno število znakov v alfanumeričnem nizu. To bo razbremenilo določanje največjega števila znakov.
Alternativa funkcije SEQUENCE
Če ne želite uporabljati funkcije SEQUENCE, lahko za ustvarjanje zaporednih števil uporabite kombinacijo funkcij ROW in INDIRECT.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jubled_text,ROW(POSREDNO("1:"&LEN(zmešan_tekst))),1)+0,"")) |
INDIRECT bo besedilo ("1:20") pretvoril v dejanski obseg, nato pa bo funkcija ROW prikazala vse številke vrstic od 1 do 20. (20 je samo na primer. Lahko je poljubno število).
Torej ja, fantje, tako lahko v Excelu odtrgate neštevilčne znake iz alfanumeričnega niza. Upam, da sem bil dovolj razložen in da vam je ta članek pomagal. Če imate kakršna koli vprašanja o tej temi ali kateri koli drugi temi Excel/VBA. Do takrat nadaljujte z odličnostjo.
Razdelite številke in besedilo iz niza v Excelu 2016 in starejših: Ko nismo imeli funkcije TEXTJOIN, smo uporabili funkcije LEFT in RIGHT z nekaterimi drugimi funkcijami za ločevanje številskih in neštevilskih znakov iz niza.
Izvlecite besedilo iz niza v Excelu z uporabo Excelove funkcije LEVO IN DESNO: Za odstranitev besedila v Excelu iz niza lahko uporabimo Excelove funkcije LEFT in RIGHT. Te funkcije nam pomagajo pri dinamičnem sekanju nizov.
Odstranite začetni in zadnji razmik iz besedila v Excelu: Vodilni in zadnji prostor je vizualno težko prepoznati in lahko pokvari vaše podatke. Odstranitev teh znakov iz niza je osnovna in najpomembnejša naloga pri čiščenju podatkov. Evo, kako lahko to preprosto storite v Excelu.
Odstranite znake z desne: Za odstranitev znakov z desne strani niza v Excelu uporabimo funkcijo LEFT. Da, funkcija LEFT. Funkcija LEFT ohrani dano število znakov iz LEFT in odstrani vse z desne.
Odstranite neželene znake v Excelu: Za odstranitev neželenih znakov iz niza v Excelu uporabimo funkcijo ZAMENJA. Funkcija SUBSTITUTE nadomesti dane znake z drugim znakom in ustvari nov spremenjeni niz.
Kako odstraniti besedilo v Excelu od položaja v Excelu: Za odstranitev besedila z začetnega mesta v nizu uporabljamo funkcijo REPLACE v Excelu. Ta funkcija nam pomaga določiti začetni položaj in število znakov, ki jih moramo odstraniti.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.