Mēs esam iemācījušies noņemt skaitliskās vērtības no šūnas programmā Excel 2016 un vecākiem. Mūsu izmantotās formulas bija nedaudz sarežģītas, bet tagad Excel 2019 un 365 ir spēlē ar jaunām rotaļlietām, es domāju funkcijas.
Programmas Excel 2019 un 365 ievieš dažas jaunas funkcijas (TEXTJOIN un SEQUENCE), kas var atvieglot uzdevumu noņemt ciparu rakstzīmes un jaunā šūnā izgūt tikai skaitliskas vērtības. Mēs izmantosim formulas, kas mums palīdzēs to izdarīt ērtāk.
Vispārīgā formula
| =TEXTJOIN("", TRUE,IF(ISERROR(MID(jumbled_text,SEKENCE(NumChars), 1) +0),MID(jumbled_text,SEKENCE(NumChars), 1), ""))) |
Jumbled_text: Šis ir avota teksts, no kura vēlaties iegūt visas skaitliskās vērtības.
NumChars: Šis ir kopējais apstrādājamo rakstzīmju skaits. Jumbled_text nedrīkst saturēt vairāk rakstzīmju nekā šis skaitlis (kopā rakstzīmes un cipari).
Apskatīsim piemēru, lai lietas būtu skaidras.
Piemērs: noņemiet ciparu rakstzīmes un izvelciet visus alfabētus un ne ciparu rakstzīmes.
Tātad šeit ir daži burtciparu teksti. Šajā tekstā ir daži cipari un dažas neskaitliskas rakstzīmes. Man jāatbrīvojas no ciparu rakstzīmēm un kolonnā D jāiegūst tikai alfabēts un citas rakstzīmju vērtības.
Es negaidu, ka sajauktajā tekstā kopējais rakstzīmju skaits būs lielāks par 100. Tātad NumChars vērtība šeit ir 100. Ja nepieciešams, šo skaitu var palielināt vai samazināt.
Šeit izmantojiet iepriekš minēto vispārīgo formulu, lai noņemtu ciparu rakstzīmes.
| =TEXTJOIN("", TRUE,IF(ISERROR(MID(B3,SEKENCE(100),1)+0),MID(B3,SEKENCE(100),1),""))) |
Nospiežot ievadīšanas pogu, visas ciparu rakstzīmes tiek noņemtas, un ir palikušas tikai alfabēta vērtības. Velciet uz leju šo formulu, lai noņemtu skaitļus no virknes no visām B slejas šūnām.
Kā tas darbojas?
Vispirms redzēsim, kā šī formula tiek soli pa solim atrisināta.
| 1-> TEXTJOIN("", TRUE,IF(ISERROR(MID(B3,SEKENCE(100),1)+0),MID(B3,SEKENCE(100),1),""))) |
| 2-> TEXTJOIN("", TRUE,IF(ISERROR(MID("Šī ir… vietne",{1,2,3,… 100},1)+0),MID(B3,SEKENCE(100),1),""))) |
| 3-> TEXTJOIN("", TRUE,IF(ISERROR({"T"; "h"; "i"; "s"; "", "1" … ""; ""}+0),MID(B3,SEKENCE(100),1),""))) |
| 4-> TEXTJOIN("", TRUE,IF(ISERROR({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}),MID(B3,SEKENCE(100),1),""))) |
| 5-> TEXTJOIN("", TRUE,IF({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MID(B3,SEKENCE(100),1),""))) |
| 6-> TEXTJOIN("", TRUE,{"T"; "h"; "i"; "s"; ""….; ""}) |
| 7-> "Šī ir vietne numur viens" |
Lasīšanas ērtībai es neesmu uzrakstījis visu masīvu. Es izmantoju punktus (…), lai norādītu garus masīvus.
Kā redzat, formula sāk risināt no iekšpuses. Sākumā tiek atrisināta SEQUENCE funkcija. Tā kā esam pagājuši 100 kā rakstzīmju skaits. Tas atgriež skaitļu masīvu, sākot no 1 līdz 100.
Šis masīvs tiek pasniegts MID funkcijai kā sākuma numurs. Funkcija vidus iet uz katru indeksu virknē un sadala katru masīva rakstzīmi. To var redzēt 3. solī. Es neesmu parādījis visu masīvu, jo tas aizņems pārāk daudz vietas. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}
Tālāk katrai rakstzīmei pievienojam 0. Ja programmā Excel mēģināt pievienot ciparus neskaitliskām rakstzīmēm, rezultāts ir #VALUE! Kļūda. Tātad mēs iegūstam skaitļu masīvu un #VALUE! Kļūdas. {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}
Šis masīvs tiek pasniegts ISERROR funkciju. Kā jūs zināt, funkcija ISERROR atgriež TRUE kļūdām un FALSE vērtībām, kas nav kļūdas. Tādējādi mēs iegūstam masīvu PATIESAS un nepatiesas. TRUE, ja nav ciparu rakstzīmes, un FALSE skaitļiem. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Tas pats notiek ar IF patiesiem sadaļas paziņojumiem (MID(B3,SEKENCE(100),1)). Tas atgriež visu burtciparu virknes rakstzīmju masīvu B3.
Tagad katram TRUE apgalvojumam funkcija IF atgriež atbilstošo rakstzīmi no patiesās sadaļas masīva. FALSE gadījumā IF atgriež tukšu (""). Tagad jums būs masīvs, kas nesatur ciparu rakstzīmes. {"T"; "h"; "i"; "s"; ""….; ""}.
Visbeidzot, šo masīvu apkalpo TEXTJOIN funkciju. Šī funkcija savieno norādītos tekstus, ignorējot tukšās vērtības, ar noteiktu norobežotāju. Tādējādi mēs iegūstam virkni, kas nesatur ciparu rakstzīmes. Šī ir vietne numur viens.
Formulas uzlabošana
Iepriekšminētajā formulā rakstzīmju skaita apstrādei tiek izmantots kodēts skaitlis (mēs paņēmām 100). Bet jūs varat vēlēties, lai tas būtu dinamisks. Tādā gadījumā jūs uzminējāt pareizi, varat izmantot funkciju LEN. Apstrādei būs nepieciešams precīzs rakstzīmju skaits. Tātad formula būs.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE (LEN(jaukts_teksts), 1) +0), MID (jaukts_teksts, SEQUENCE (LEN(jaukts_teksts),1),""))) |
Šeit LEN funkcija automātiski noteiks precīzas ciparu rakstzīmes burtciparu virknē. Tas atbrīvos no pienākuma noteikt maksimālo rakstzīmju skaitu.
Funkcijas SEQUENCE alternatīva
Ja nevēlaties izmantot SEQUENCE funkciju, secīgu numuru ģenerēšanai varat izmantot ROW un INDIRECT funkcijas kombināciju.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text,Rinda(NETIEŠI("1:"&LEN(NumChars))), 1) +0), MID (jaukts_teksts,Rinda(NETIEŠI("1:"&LEN(NumChars))),1),""))) |
INDIRECT pārveidos tekstu ("1: 100") faktiskajā diapazonā, un pēc tam ROW funkcija uzskaitīs visus rindu numurus no 1 līdz 100. (100 ir tikai piemērs. Tas var būt jebkurš skaitlis).
Tātad, jā, puiši, šādā veidā programmā Excel varat norakstīt rakstzīmes, kas nav ciparu virkne. Es ceru, ka es biju pietiekami izskaidrojošs un šis raksts jums palīdzēja. Ja jums ir kādi jautājumi par šo tēmu vai kādu citu Excel/VBA tēmu. Līdz tam turpiniet izcelties.
Kā no cipariem noņemt ciparus, kas nav cipari programmā Excel 2019: Lai programmā Excel no burtciparu virknes noņemtu ne ciparu rakstzīmes, mēs izmantojam jauno funkciju TEXTJOIN. Ja virknē nav ciparu rakstzīmju joslas, mēs varam iztīrīt datus, lai labāk analizētu datus. Tātad, lūk, kā jūs to darāt
Sadaliet ciparus un tekstu no virknes programmā Excel 2016 un vecākās versijās: Kad mums nebija funkcijas TEXTJOIN, mēs izmantojām LEFT un RIGHT funkcijas ar dažām citām funkcijām, lai no virknes sadalītu ciparu un neskaitlisko rakstzīmes.
Izņemiet tekstu no virknes programmā Excel, izmantojot Excel kreiso un labo funkciju: Lai noņemtu tekstu programmā Excel no virknes, mēs varam izmantot Excel LEFT un RIGHT funkciju. Šīs funkcijas palīdz mums dinamiski sagriezt virknes. \
Noņemiet teksta sākumā un beigās atstarpes programmā Excel: Iepriekšējās un beigu vietas ir grūti atpazīt vizuāli, un tās var sajaukt jūsu datus. Šo rakstzīmju noņemšana no virknes ir pamata un vissvarīgākais datu tīrīšanas uzdevums. Lūk, kā to viegli izdarīt programmā Excel.
Noņemiet rakstzīmes no labās puses: Lai programmā Excel noņemtu rakstzīmes no virknes labās puses, mēs izmantojam funkciju LEFT. Jā, LEFT funkcija. Funkcija LEFT saglabā doto rakstzīmju skaitu no LEFT un noņem visu no labās puses.
Noņemiet nevēlamās rakstzīmes programmā Excel: Lai noņemtu nevēlamās rakstzīmes no virknes programmā Excel, mēs izmantojam funkciju SUBSTITUTE. Funkcija SUBSTITUTE aizvieto dotās rakstzīmes ar citu doto rakstzīmi un rada jaunu mainītu virkni.
Kā noņemt tekstu programmā Excel, sākot no pozīcijas programmā Excel: Lai noņemtu tekstu no virknes sākuma pozīcijas, mēs izmantojam Excel funkciju REPLACE. Šī funkcija palīdz mums noteikt sākuma pozīciju un noņemamo rakstzīmju skaitu.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā izmantot Excel VLOOKUP funkciju| Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot Excel Funkcija COUNTIF| Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.