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ú v hre Excel 2019 a 365.
Excel 2019 a 365 zavádzajú niektoré nové funkcie, ktoré uľahčujú odstránenie nečíselných znakov a načítanie iba čí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,IFERROR(MID(jumbled_text,SEKVENCIA(NumChars), 1) +0, "")) |
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 nečíselné znaky a extrahujte všetky čísla
Takže tu máme nejaký neusporiadaný text. Tento text obsahuje niekoľko čísel a niekoľko nečíselných znakov. Potrebujem sa zbaviť nečíselných znakov a získať číselné hodnoty iba v stĺpci D.
Neočakávam, že celkový počet znakov v spletitom texte bude vyšší ako 20. Takže hodnota NumChars je tu 20. Toto číslo môžete v prípade potreby zvýšiť.
Na vymazanie nečíselných znakov tu použite vyššie uvedený generický vzorec.
| =TEXTJOIN("", PRAVDA,IFERROR(MID(C3,SEKVENCIA(20),1)+0,"")) |
A keď stlačíte tlačidlo Enter. Odstránite všetky nečíselné znaky. Presunutím tohto vzorca nadol odstránite znaky z reťazca zo všetkých buniek v stĺpci C3.
Ako to funguje?
Najprv sa pozrime, ako je tento vzorec vyriešený krok za krokom.
| 1-> TEXTJOIN("", PRAVDA,IFERROR(MID(C3,SEKVENCIA(20),1)+0,"")) |
| 2-> TEXTJOIN("", PRAVDA,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("", PRAVDA,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("", PRAVDA,IFERROR({1; 2; #HODNOTA!;#HODNOTA!;#HODNOTA!; 1; 2;#HODNOTA!; 1; 2; 3;#HODNOTA!;#HODNOTA!; …;#HODNOTA!}+0,"")) |
| 5-> TEXTJOIN("", PRAVDA,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Ako vidíte, vzorec sa začína riešiť zvnútra. Najprv je vyriešená funkcia SEQUENCE. Pretože sme minuli 20. Vráti pole čísel začínajúcich od 1 do 20.
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. Môžete to vidieť v kroku 3.
Ď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. Nečíselné znaky sú teraz preč.
Nasledujúca funkcia IFERROR nahradí všetky chyby #HODNOTY "" (prázdne). Teraz nám zostanú číselné hodnoty a medzery.
Nakoniec je toto pole obsluhované funkciou TEXTJOIN. Funkcia TEXTJOIN ich zreťazí a dostaneme reťazec, ktorý obsahuje iba čísla.
Vylepšenie vzorca
Vyššie uvedený vzorec použil na spracovanie počtu znakov pevne zakódované číslo (zobrali sme dvadsať). Ale možno budete chcieť, aby to bolo dynamické. V tom prípade ste to uhádli správne, použijeme funkciu LEN. Na spracovanie bude potrebný presný počet znakov. Vzorec teda bude.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Tu funkcia LEN automaticky zistí presný počet znakov v alfanumerickom reťazci. Odbremeníte tak od určovania maximálneho počtu znakov.
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, IFERROR (MID (jumbled_text,RIADOK(NEPRIAMY("1:"&LEN(jumbled_text))),1)+0,"")) |
INDIRECT prevedie text ("1:20") na skutočný rozsah a potom funkcia ROW zobrazí zoznam všetkých riadkových čísel od 1 do 20. (20 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í.
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ť struny.
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.