Obecný vzorec
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
souhrn
Chcete-li odebrat číselné znaky z textového řetězce, můžete použít vzorec založený na funkci TEXTJOIN. V zobrazeném příkladu 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 maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, kromě Excel 365.
Vysvětlení
Excel nemá způsob, jak vložit písmena v textovém řetězci do pole přímo ve vzorci. Jako řešení tento vzorec používá funkci MID s pomocí funkcí ROW a INDIRECT k dosažení stejného výsledku. Zkopírovaný vzorec v C5 je:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Vypadá to docela komplikovaně, ale podstata spočívá v tom, že v B5 vytvoříme řadu všech znaků a každý znak otestujeme, abychom zjistili, zda jde o číslo. Pokud ano, hodnotu zahodíme a nahradíme ji prázdným řetězcem (""). Pokud ne, přidáme nečíselný znak do "zpracovaného" pole. Nakonec použijeme funkci TEXTJOIN (nová v aplikaci Excel 2019) ke zřetězení všech znaků dohromady, ignorujeme prázdné hodnoty.
Při práci zevnitř ven se funkce MID používá k extrakci textu v B5 po jednom znaku. Klíčem je úryvek ROW and INDIRECT zde:
ROW(INDIRECT("1:100"))
který roztočí pole obsahující 100 čísel, jako je tento:
(1,2,3,4,5,6,7,8… 0,99,100)
Poznámka: 100 představuje maximální počet znaků ke zpracování. Změňte je tak, aby vyhovovaly vašim datům, nebo použijte funkci LEN, jak je vysvětleno níže.
Toto pole jde do funkce MID jako argument start_num . Pro num_chars používáme 1.
Funkce MID vrací pole takto:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Poznámka: další položky v poli byly odstraněny kvůli čitelnosti.
Do tohoto pole přidáme nulu. Jedná se o jednoduchý trik, který nutí Excel vynucovat text na číslo. Číselné textové hodnoty jako „1“, „2“, „3“, „4“ atd. Jsou převedeny bez chyb, ale nečíselné hodnoty selžou a způsobí chybu #HODNOTA. K zachycení těchto chyb používáme funkci IF s funkcí ISERR. Když vidíme chybu, víme, že máme nečíselný znak, takže tento znak přivedeme do zpracovaného pole s jinou funkcí MID:
MID(B5,ROW(INDIRECT("1:100")),1)
Pokud se nezobrazí chyba, víme, že máme číslo, a tak do pole místo čísla vložíme prázdný řetězec ("").
Konečný výsledek pole přejde do funkce TEXTJOIN jako argument text1. Pro oddělovač používáme prázdný řetězec ("") a pro ignore_empty dodáváme TRUE. TEXTJOIN poté zřetězí všechny neprázdné hodnoty v poli a vrátí výsledek.
Přesná délka pole
Místo pevného kódování čísla jako 100 do INDIRECT můžete pomocí funkce LEN vytvořit pole se skutečným počtem znaků v buňce takto:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN vrací počet znaků v buňce jako číslo, které se používá místo 100. To umožňuje vzorec automaticky škálovat až na libovolný počet znaků.
Odstranění nadbytečného prostoru
Když odstraníte číselné znaky, mohou vám zůstat mezery navíc. Chcete-li odstranit úvodní a zadní mezery a normalizovat mezery mezi slovy, můžete zabalit vzorec zobrazený na této stránce uvnitř funkce TRIM:
=TRIM(formula)
SE SEKVENCÍ
V aplikaci Excel 365 může nová funkce SEQUENCE nahradit výše uvedený ROW + INDIRECT kód:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Zde používáme SEQUENCE + LEN k vytvoření pole správné délky v jednom kroku.
S LET
Tento vzorec můžeme dále zefektivnit pomocí funkce LET. Vzhledem k tomu, že pole je vytvořeno dvakrát výše pomocí SEQUENCE a LEN, můžeme definovat pole jako proměnnou a vytvořit ji pouze jednou:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Zde je hodnota pole nastavena pouze jednou a poté použita dvakrát uvnitř funkce MID.
