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 Excel 2019 in 365 v igri z novimi igračami, mislim s funkcijami.
Excel 2019 in 365 uvajata nekaj novih funkcij (TEXTJOIN in SEQUENCE), ki lahko olajšata odstranjevanje številskih znakov in pridobivanje samo neštevilskih vrednosti v novi celici. Uporabili bomo formule, ki nam bodo pri tem lahko pomagale, bolj priročno.
Splošna formula
| =TEXTJOIN("",PRAV,ČE(NAPAKA(MID(jumble_text,SEQUENCE(NumChars), 1) +0),MID(jumble_text,SEQUENCE(NumChars), 1), ""))) |
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 številske znake in izvlecite vse abecede in neštevilčne znake.
Torej imamo tukaj nekaj alfanumeričnega besedila. To besedilo vsebuje nekaj številk in nekaj neštevilskih znakov. Znebiti se moram številskih znakov in v stolpcu D dobiti samo abecede in druge vrednosti znakov.
Ne pričakujem, da bo skupno število znakov v zmedenem besedilu večje od 100. Torej je vrednost NumChars tukaj 100. To število lahko po potrebi povečate ali zmanjšate.
Tukaj uporabite zgornjo generično formulo, da odstranite številske znake.
| =TEXTJOIN("",PRAV,ČE(NAPAKA(MID(B3,SEQUENCE(100),1)+0),MID(B3,SEQUENCE(100),1),""))) |
Ko pritisnete gumb za vnos, odstranite vse številske znake in ostanejo samo abecedne vrednosti. Povlecite to formulo navzdol, da odstranite številke iz niza iz vseh celic v stolpcu B.
Kako deluje?
Najprej poglejmo, kako se ta formula po korakih rešuje.
| 1-> TEXTJOIN("",PRAV,ČE(NAPAKA(MID(B3,SEQUENCE(100),1)+0),MID(B3,SEQUENCE(100),1),""))) |
| 2-> TEXTJOIN("",PRAV,ČE(NAPAKA(MID("To1 je … spletno mesto",{1,2,3,… 100},1)+0),MID(B3,SEQUENCE(100),1),""))) |
| 3-> TEXTJOIN("",PRAV,ČE(NAPAKA({"T"; "h"; "i"; "s"; "", "1"… ""; ""}+0),MID(B3,SEQUENCE(100),1),""))) |
| 4-> TEXTJOIN("",PRAV,ČE(NAPAKA({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VREDNOST!}),MID(B3,SEQUENCE(100),1),""))) |
| 5-> TEXTJOIN("",PRAV,ČE({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MID(B3,SEQUENCE(100),1),""))) |
| 6-> TEXTJOIN("",PRAV,{"T"; "h"; "i"; "s"; ""….; ""}) |
| 7-> "To je spletno mesto številka ena" |
Zaradi lažjega branja nisem napisal celotnega niza. Uporabil sem pike (…) za označevanje dolgih nizov.
Kot lahko vidite, se formula začne reševati od znotraj. Najprej je rešena funkcija SEQUENCE. Odkar smo prešli 100 kot število znakov. Vrne niz številk od 1 do 100.
Ta matrika je vročena funkciji MID kot začetna številka. Funkcija mid gre za vsak indeks v nizu in razdeli vsak znak v matriki. To lahko vidite v 3. koraku. Nisem prikazal celotnega niza, saj bo zavzel preveč prostora. {"T"; "h"; "i"; "s"; "", "1"… ""; ""}
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. {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VREDNOST!}
Ta matrika je vročena v NAPAKA funkcijo. Kot veste, funkcija ISERROR vrne TRUE za napake in FALSE za vrednosti brez napak. Tako dobimo niz TRUE in FALSE. TRUE za neštevilske znake in FALSE številk. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Enako se zgodi z izjavami razdelka TRUE IF (MID(B3,SEQUENCE(100),1)). Vrne niz vseh znakov alfanumeričnega niza v B3.
Zdaj za vsak stavek TRUE funkcija IF vrne ustrezen znak iz matrike resničnega odseka. Za vrednost FALSE IF vrne prazno (""). Zdaj boste imeli matriko, ki ne vsebuje nobenega številskega znaka. {"T"; "h"; "i"; "s"; ""….; ""}.
Končno ta niz služi storitvi TEXTJOIN funkcijo. Ta funkcija med seboj združuje dana besedila, pri čemer zanemarja prazne vrednosti, z dano ločnico. Tako dobimo niz, ki ne vsebuje nobenih številčnih znakov. To je spletno mesto številka ena.
Izboljšanje formule
Zgornja formula uporablja trdo kodirano številko za obdelavo števila znakov (vzeli smo 100). Morda pa želite, da je dinamična. V tem primeru, če ste prav uganili, lahko uporabite funkcijo LEN. Za obdelavo bo potrebno natančno število znakov. Tako bo formula.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jubled_text, SEQUENCE)LEN(jumble_text), 1) +0), MID (zmešan_tekst, SEQUENCE (LEN(jumble_text),1),""))) |
Tu bo funkcija LEN samodejno zaznala natančno število znakov v alfanumeričnem nizu. To bo razbremenilo breme določanja 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, IF (ISERROR (MID (jubled_text,ROW(POSREDNO("1:"&LEN(NumChars))), 1) +0), MID (zmešan_tekst,ROW(POSREDNO("1:"&LEN(NumChars))),1),""))) |
INDIRECT bo besedilo ("1: 100") pretvoril v dejanski obseg, nato pa bo funkcija ROW prikazala vse številke vrstic od 1 do 100. (100 je le 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.
Kako odstraniti neštevilčne znake iz celic v Excelu 2019: Za odstranitev neštevilskih znakov iz alfanumeričnega niza v Excelu uporabimo novo funkcijo TEXTJOIN. Odstranitev neštevilskih znakov iz niza nam lahko pomaga pri čiščenju podatkov za boljšo analizo podatkov. Torej, kako to storite
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.