Naučili sme sa, ako odstrániť číselné hodnoty z bunky v programe Excel 2016 a staršom. Vzorce, ktoré sme použili, boli trochu zložité, ale teraz sú Excel 2019 a 365 v hre s novými hračkami, myslím funkciami.
Excel 2019 a 365 zavádzajú niektoré nové funkcie (TEXTJOIN a SEQUENCE), ktoré môžu uľahčiť odstránenie numerických znakov a načítanie iba nečíselných hodnôt do novej bunky. Budeme používať vzorce, ktoré nám v tom môžu pomôcť, pohodlnejšie.
Generický vzorec
| =TEXTJOIN("", PRAVDA,AK(ISERROR(MID(jumbled_text,SEKVENCIA(NumChars), 1) +0),MID(jumbled_text,SEKVENCIA(NumChars), 1), ""))) |
Jumbled_text: Toto je zdrojový text, z ktorého chcete extrahovať všetky číselné hodnoty.
NumChars: Toto je celkový počet znakov, ktoré chcete spracovať. Text jumbled_text nesmie mať viac znakov ako toto číslo (znaky a číslica dohromady).
Pozrime sa na príklad, aby bolo všetko jasné.
Príklad: Odstráňte číselné znaky a extrahujte všetky abecedy a nečíselné znaky.
Takže tu máme nejaký alfanumerický text. Tento text obsahuje niekoľko čísel a niekoľko nečíselných znakov. Potrebujem sa zbaviť číselných znakov a získať iba abecedy a ďalšie hodnoty znakov v stĺpci D.
Neočakávam, že by celkový počet znakov v zmiešanom texte bol viac ako 100. Takže hodnota NumChars je tu 100. Toto číslo môžete v prípade potreby zvýšiť alebo znížiť.
Na odstránenie numerických znakov použite vyššie uvedený generický vzorec.
| =TEXTJOIN("", PRAVDA,AK(ISERROR(MID(B3,SEKVENCIA(100),1)+0),MID(B3,SEKVENCIA(100),1),""))) |
Keď stlačíte tlačidlo Enter, odstránia sa všetky číselné znaky a zostanú iba abecedné hodnoty. Presunutím tohto vzorca nadol odstránite čísla z reťazca zo všetkých buniek v stĺpci B.
Ako to funguje?
Najprv sa pozrime, ako je tento vzorec vyriešený krok za krokom.
| 1-> TEXTJOIN("", PRAVDA,AK(ISERROR(MID(B3,SEKVENCIA(100),1)+0),MID(B3,SEKVENCIA(100),1),""))) |
| 2-> TEXTJOIN("", PRAVDA,AK(ISERROR(MID(„This1 is … site“,{1,2,3,… 100},1)+0),MID(B3,SEKVENCIA(100),1),""))) |
| 3-> TEXTJOIN("", PRAVDA,AK(ISERROR({"T"; "h"; "i"; "s"; "", "1" … ""; ""}}+0),MID(B3,SEKVENCIA(100),1),""))) |
| 4-> TEXTJOIN("", PRAVDA,AK(ISERROR({#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!; 1…; #HODNOTA!}),MID(B3,SEKVENCIA(100),1),""))) |
| 5-> TEXTJOIN("", PRAVDA,AK({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},MID(B3,SEKVENCIA(100),1),""))) |
| 6-> TEXTJOIN("", PRAVDA,{"T"; "h"; "i"; "s"; "" ….; ""}}) |
| 7-> „Toto je webová stránka číslo jedna“ |
Kvôli uľahčeniu čítania som nenapísal celé pole. Na označenie dlhých polí som použil bodky (…).
Ako vidíte, vzorec sa začína riešiť zvnútra. Najprv je vyriešená funkcia SEQUENCE. Pretože sme prekročili počet znakov 100. Vráti pole čísel začínajúcich od 1 do 100.
Toto pole je obsluhované funkciou MID ako počiatočné číslo. Stredná funkcia prejde na každý index v reťazci a rozdelí každý znak v poli. Môžete to vidieť v kroku 3. Neukázal som celé pole, pretože zaberie príliš veľa miesta. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}}
Ďalej ku každému znaku pridáme 0. Ak sa v Exceli pokúsite pridať číslo k nečíselným znakom, výsledkom bude #HODNOTA! Chyba. Získame tak rad čísel a #HODNOTU! Chyby. {#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!; 1…; #HODNOTA!}
Toto pole je obsluhované súborom ISERROR funkciu. Ako viete, funkcia ISERROR vracia hodnotu TRUE pre chyby a FALSE pre hodnoty bez chýb. Preto dostaneme rad PRAVDIVÝCH a NEPRAVDIVÝCH. TRUE pre nečíselné znaky a FALSE čísel. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
To isté sa stane s vyhláseniami TRUE sekcie IF (MID(B3,SEKVENCIA(100),1)). Vracia pole všetkých znakov alfanumerického reťazca v B3.
Teraz pre každý príkaz TRUE funkcia IF vráti zodpovedajúci znak z poľa pravdivej sekcie. V prípade FALSE, IF vráti prázdne (""). Teraz budete mať pole, ktoré neobsahuje žiadny číselný znak. {"T"; "h"; "i"; "s"; "" ….; ""}.
Nakoniec toto pole slúži TEXTJOIN funkciu. Táto funkcia spája dané texty navzájom, pričom ignoruje prázdne hodnoty, s daným oddeľovačom. Preto dostaneme reťazec, ktorý neobsahuje žiadne číselné znaky. Toto je webová stránka číslo jedna.
Vylepšenie vzorca
Vyššie uvedený vzorec používa na spracovanie počtu znakov pevne zakódované číslo (zobrali sme 100). Ale možno budete chcieť, aby to bolo dynamické. V tom prípade ste to uhádli správne, môžete použiť funkciu LEN. Na spracovanie bude potrebný presný počet znakov. Vzorec teda bude.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text), 1) +0), MID (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
Tu funkcia LEN automaticky zistí presný počet znakov v alfanumerickom reťazci. Odbremeníte tak od bremena určovania maximálneho počtu postáv.
Alternatíva funkcie SEQUENCE
Ak nechcete používať funkciu SEQUENCE, môžete na generovanie sekvenčných čísel použiť kombináciu funkcie ROW a INDIRECT.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text,RIADOK(NEPRIAMY("1:"&LEN(NumChars)))(1) +0), MID (jumbled_text,RIADOK(NEPRIAMY("1:"&LEN(NumChars))),1),""))) |
INDIRECT prevedie text („1: 100“) na skutočný rozsah a potom funkcia ROW zobrazí zoznam všetkých čísel riadkov od 1 do 100. (100 je len príklad. Môže to byť ľubovoľné číslo).
Takže áno, chlapci, takto môžete odtrhnúť nečíselné znaky z alfanumerického reťazca v programe Excel. Dúfam, že som bol dostatočne vysvetľujúci a tento článok vám pomohol. Ak máte akékoľvek otázky týkajúce sa tejto témy alebo inej témy programu Excel/VBA. Do tej doby pokračujte v excelovaní.
Ako odstrániť nečíselné znaky z buniek v programe Excel 2019: Na odstránenie nečíselných znakov z alfanumerického reťazca v programe Excel používame novú funkciu TEXTJOIN. Pás nečíselných znakov z reťazca nám môže pomôcť vyčistiť naše údaje pre lepšiu analýzu údajov. Takto to teda robíte
Rozdeľte čísla a text z reťazca v programe Excel 2016 a staršom: Keď sme nemali funkciu TEXTJOIN, použili sme funkcie VĽAVO a VPRAVO s niektorými ďalšími funkciami na oddelenie číselných a nečíselných znakov z reťazca.
Extrahujte text z reťazca v programe Excel pomocou ľavej a pravej funkcie programu Excel: Na odstránenie textu v Exceli z reťazca môžeme použiť funkciu LEFT a RIGHT v Exceli. Tieto funkcie nám pomáhajú dynamicky sekať reťazce. \
Odstráňte úvodné a koncové medzery z textu v programe Excel: Úvodné a koncové medzery sú vizuálne ťažko rozpoznateľné a môžu poškodiť vaše údaje. Odstránenie týchto znakov z reťazca je základnou a najdôležitejšou úlohou pri čistení údajov. V Exceli to môžete jednoducho urobiť.
Odstráňte znaky sprava: Na odstránenie znakov napravo od reťazca v programe Excel používame funkciu VĽAVO. Áno, funkcia DOLEVA. Funkcia LEFT zachová daný počet znakov z LEFT a odstráni všetko z jej pravej strany.
Odstráňte nechcené znaky v programe Excel: Na odstránenie nechcených znakov z reťazca v Exceli používame funkciu SUBSTITUTE. Funkcia SUBSTITUTE nahradí dané znaky iným daným znakom a vytvorí nový zmenený reťazec.
Ako odstrániť text v programe Excel z pozície v programe Excel: Na odstránenie textu z počiatočnej pozície v reťazci používame funkciu REPLACE v programe Excel. Táto funkcia nám pomáha určiť začiatočnú pozíciu a počet znakov na vyzlečenie.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.