Všeobecný vzorec
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Zhrnutie
Ak chcete z číselného reťazca odstrániť číselné znaky, môžete použiť vzorec založený na funkcii TEXTJOIN. V zobrazenom príklade je vzorec v C5:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Vysvetlenie
Excel nemá spôsob, ako vkladať písmená v textovom reťazci do poľa priamo vo vzorci. Ako náhradné riešenie používa tento vzorec funkciu MID s pomocou funkcií ROW a INDIRECT na dosiahnutie rovnakého výsledku. Vzorec v C5, skopírovaný nadol, je:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Vyzerá to dosť komplikovane, ale podstata spočíva v tom, že v B5 vytvoríme pole všetkých znakov a každý znak otestujeme, či ide o číslo. Ak je to tak, hodnotu zahodíme a nahradíme ju prázdnym reťazcom („“). Ak nie, pridáme nečíselný znak do „spracovaného“ poľa. Nakoniec použijeme funkciu TEXTJOIN (nová v programe Excel 2019) na zreťazenie všetkých znakov dohromady, pričom ignorujeme prázdne hodnoty.
Pri práci zvnútra smerom von sa funkcia MID používa na extrakciu textu v kóde B5 po jednom znaku. Kľúčom je úryvok ROW a INDIRECT tu:
ROW(INDIRECT("1:100"))
ktorý roztáča pole obsahujúce 100 čísel, ako je tento:
(1,2,3,4,5,6,7,8 … 0,99,100)
Poznámka: 100 predstavuje maximálny počet znakov na spracovanie. Zmeňte ich tak, aby vyhovovali vašim údajom, alebo použite funkciu LEN, ako je vysvetlené nižšie.
Toto pole vstupuje do funkcie MID ako argument start_num . Pre num_chars používame 1.
Funkcia MID vráti pole podobné tomuto:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Poznámka: z dôvodu čitateľnosti boli v poli odstránené ďalšie položky.
Do tohto poľa pridáme nulu. Toto je jednoduchý trik, ktorý núti program Excel nútiť text k číslu. Číselné textové hodnoty ako „1“, „2“, „3“, „4“ atď. Sa prevádzajú bez chýb, ale nečíselné hodnoty zlyhajú a spôsobí chybu #HODNOTA. Na zachytenie týchto chýb používame funkciu IF s funkciou ISERR. Keď vidíme chybu, vieme, že máme nečíselný znak, takže tento znak prenesieme do spracovaného poľa pomocou inej funkcie MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Ak sa chyba nezobrazí, vieme, že máme číslo, a tak do poľa namiesto čísla vložíme prázdny reťazec („“).
Konečný výsledok poľa ide do funkcie TEXTJOIN ako argument text1. Pre oddeľovač používame prázdny reťazec ("") a pre ignore_empty dodávame TRUE. TEXTJOIN potom zreťazí všetky neprázdne hodnoty v poli a vráti výsledok.
Presná dĺžka poľa
Namiesto pevného kódovania čísla ako 100 do INDIRECT môžete pomocou funkcie LEN vytvoriť pole so skutočným počtom znakov v bunke, ako je toto:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
Funkcia LEN vráti počet znakov v bunke ako číslo, ktoré sa použije namiesto 100. To umožňuje vzorec automaticky zväčšiť na ľubovoľný počet znakov.
Odstraňuje sa ďalší priestor
Pri odstraňovaní číselných znakov vám môžu zostať ďalšie medzery. Ak chcete odstrániť medzery na začiatku a na konci a normalizovať medzery medzi slovami, môžete zabaliť vzorec zobrazený na tejto stránke do funkcie TRIM:
=TRIM(formula)
S POSTUPNOSŤOU
V programe Excel 365 môže nová funkcia SEQUENCE nahradiť vyššie uvedený kód ROW + INDIRECT:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Tu použijeme SEQUENCE + LEN na vytvorenie poľa správnej dĺžky v jednom kroku.
S LET
Tento vzorec môžeme ďalej zefektívniť pomocou funkcie LET. Pretože je pole vytvorené dvakrát vyššie pomocou SEQUENCE a LEN, môžeme pole definovať ako premennú a vytvoriť ju iba raz:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Tu je hodnota poľa nastavená iba raz a potom použitá dvakrát vo vnútri funkcie MID.
