Naučili jsme se, jak z buňky v Excelu 2016 a starších odebrat číselné hodnoty. Vzorce, které jsme použili, byly trochu složité, ale nyní jsou ve hře Excel 2019 a 365.
Excel 2019 a 365 zavádějí některé nové funkce, které usnadňují odstraňování nečíselných znaků a načítání pouze číselných hodnot v nové buňce. Použijeme vzorce, které nám v tom mohou pomoci, pohodlněji.
Obecný vzorec
| =TEXTJOIN("",SKUTEČNÝ,IFERROR(STŘEDNÍ(jumbled_text,SEKVENCE(NumChars), 1) +0, "")) |
Jumbled_text: Toto je zdrojový text, ze kterého chcete extrahovat všechny číselné hodnoty.
NumChars: Toto je celkový počet znaků, které chcete zpracovat. Jumbled_text by neměl mít více znaků než toto číslo (znaky a číslice dohromady).
Podívejme se na příklad, aby bylo vše jasné.
Příklad: Odstraňte nečíselné znaky a extrahujte všechna čísla
Takže tady máme nějaký neuspořádaný text. Tento text obsahuje některá čísla a některé nečíselné znaky. Potřebuji se zbavit nečíselných znaků a získat číselné hodnoty pouze ve sloupci D.
Neočekávám, že by celkový počet znaků v neuspořádaném textu byl větší než 20. Takže hodnota NumChars je zde 20. Toto číslo můžete v případě potřeby zvýšit.
Zde použijte výše uvedený obecný vzorec, abyste odstranili nečíselné znaky.
| =TEXTJOIN("",SKUTEČNÝ,IFERROR(STŘEDNÍ(C3,SEKVENCE(20),1)+0,"")) |
A když stisknete tlačítko Enter. Odstraníte všechny nečíselné znaky. Přetažením dolů tento vzorec odeberete znaky z řetězce ze všech buněk ve sloupci C3.
Jak to funguje?
Nejprve se podívejme, jak je tento vzorec vyřešen krok za krokem.
| 1-> TEXTJOIN("",SKUTEČNÝ,IFERROR(STŘEDNÍ(C3,SEKVENCE(20),1)+0,"")) |
| 2-> TEXTJOIN("",SKUTEČNÝ,IFERROR(STŘEDNÍ("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("",SKUTEČNÝ,IFERROR({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; ""; ""; ""; ""; ""; ""; ""; ""}+0,"")) |
| 4-> TEXTJOIN("",SKUTEČNÝ,IFERROR({1; 2; #HODNOTA!;#HODNOTA!;#HODNOTA!; 1; 2; #HODNOTA!; 1; 2; 3; #HODNOTA!;#HODNOTA!;…; #HODNOTA!}+0,"")) |
| 5-> TEXTJOIN("",SKUTEČNÝ,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Jak vidíte, vzorec se začíná řešit zevnitř. Nejprve je vyřešena funkce SEQUENCE. Protože jsme minuli 20. Vrací řadu čísel od 1 do 20.
Toto pole je obsluhováno funkcí MID jako počáteční číslo. Střední funkce přejde na každý index v řetězci a rozdělí každý znak. Můžete to vidět v kroku 3.
Dále přidáme 0 ke každému znaku. Pokud se v aplikaci Excel pokusíte přidat číslo k nečíselným znakům, výsledkem bude #HODNOTA! Chyba. Takže získáme řadu čísel a #HODNOTU! Chyby. Nečíselné znaky jsou nyní pryč.
Další funkce IFERROR nahradí všechny chyby #HODNOTY "" (prázdné). Nyní nám zbývají číselné hodnoty a mezery.
Nakonec je toto pole obsluhováno funkcí TEXTJOIN. Funkce TEXTJOIN je zřetězí a dostaneme řetězec, který obsahuje pouze čísla.
Vylepšení vzorce
Výše uvedený vzorec používal pevně zpracované číslo pro zpracování počtu znaků (vzali jsme dvacet). Ale možná budete chtít, aby to bylo dynamické. V tom případě jste uhodli správně, použijeme funkci LEN. Zpracování bude vyžadovat přesný počet znaků. Vzorec tedy bude.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)),1)+0,"")) |
Zde funkce LEN automaticky detekuje přesný počet znaků v alfanumerickém řetězci. Odlehčí vám to břemeno při určování maximálního počtu postav.
Alternativa funkce SEQUENCE
Pokud nechcete používat funkci SEQUENCE, můžete pro generování pořadových čísel použít kombinaci funkce ROW a INDIRECT.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jumbled_text,ŘÁDEK(NEPŘÍMÝ("1:"&LEN(jumbled_text))),1)+0,"")) |
NEPŘÍMÝ převede text ("1:20") na skutečný rozsah a poté funkce ROW zobrazí všechna čísla řádků od 1 do 20. (20 je pouze příklad. Může to být libovolné číslo).
Takže jo, lidi, takhle můžete v Excelu odtrhnout nečíselné znaky z alfanumerického řetězce. Doufám, že jsem byl dostatečně vysvětlující a tento článek vám pomohl. Máte -li jakékoli dotazy týkající se tohoto tématu nebo jiného tématu aplikace Excel/VBA. Do té doby pokračujte v excelování.
Rozdělit čísla a text z řetězce v aplikaci Excel 2016 a starší: Když jsme neměli funkci TEXTJOIN, použili jsme funkce LEFT a RIGHT s některými dalšími funkcemi k rozdělení numerických a nečíselných znaků z řetězce.
Extrahujte text z řetězce v aplikaci Excel pomocí levé a pravé funkce aplikace Excel: K odstranění textu v Excelu z řetězce můžeme použít funkci LEFT a RIGHT v Excelu. Tyto funkce nám pomáhají dynamicky sekat řetězce.
Odeberte z textu v aplikaci Excel úvodní a koncové mezery: Přední a koncové mezery jsou vizuálně těžko rozpoznatelné a mohou poškodit vaše data. Odstranění těchto znaků z řetězce je základní a nejdůležitější úkol při čištění dat. V aplikaci Excel to můžete snadno provést.
Odeberte znaky zprava: K odstranění znaků zprava od řetězce v Excelu používáme funkci VLEVO. Ano, funkce DOLEVA. Funkce LEFT zachová daný počet znaků z LEFT a odstraní vše z jeho pravé strany.
Odstraňte nežádoucí znaky v aplikaci Excel: K odstranění nežádoucích znaků z řetězce v Excelu používáme funkci SUBSTITUTE. Funkce SUBSTITUTE nahradí dané znaky jiným daným znakem a vytvoří nový změněný řetězec.
Jak odebrat text v aplikaci Excel od pozice v aplikaci Excel: K odstranění textu z počáteční pozice v řetězci používáme funkci VYMĚNIT v Excelu. Tato funkce nám pomáhá určit počáteční pozici a počet znaků, které se mají odstranit.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci Excel VLOOKUP| Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
Jak používat Excel Funkce COUNTIF| Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.