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 Excel 2019 a 365 ve hře s novými hračkami, myslím funkcemi.
Excel 2019 a 365 zavádějí některé nové funkce (TEXTJOIN a SEQUENCE), které mohou usnadnit odstraňování číselných znaků a načítání pouze nečíselných hodnot v nové buňce. Použijeme vzorce, které nám v tom mohou pomoci, pohodlněji.
Obecný vzorec
| =TEXTJOIN("",SKUTEČNÝ,LI(ISERROR(STŘEDNÍ(jumbled_text,SEKVENCE(NumChars), 1) +0),STŘEDNÍ(jumbled_text,SEKVENCE(NumChars), 1), ""))) |
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: Odeberte číselné znaky a extrahujte všechny abecedy a nečíselné znaky.
Takže tady máme nějaký alfanumerický text. Tento text obsahuje některá čísla a některé nečíselné znaky. Potřebuji se zbavit číselných znaků a získat pouze abecedy a další hodnoty znaků ve sloupci D.
Nečekám, že by celkový počet znaků v neuspořádaném textu byl větší než 100. Takže hodnota NumChars je zde 100. Toto číslo můžete v případě potřeby zvýšit nebo snížit.
Zde použijte výše uvedený obecný vzorec, abyste odstranili číselné znaky.
| =TEXTJOIN("",SKUTEČNÝ,LI(ISERROR(STŘEDNÍ(B3,SEKVENCE(100),1)+0),STŘEDNÍ(B3,SEKVENCE(100),1),""))) |
Když stisknete tlačítko Enter, odeberete všechny číselné znaky a zbývají pouze abecední hodnoty. Přetažením dolů tento vzorec odeberete čísla z řetězce ze všech buněk ve sloupci B.
Jak to funguje?
Nejprve se podívejme, jak je tento vzorec vyřešen krok za krokem.
| 1-> TEXTJOIN("",SKUTEČNÝ,LI(ISERROR(STŘEDNÍ(B3,SEKVENCE(100),1)+0),STŘEDNÍ(B3,SEKVENCE(100),1),""))) |
| 2-> TEXTJOIN("",SKUTEČNÝ,LI(ISERROR(STŘEDNÍ(„This1 is … site“,{1,2,3,… 100},1)+0),STŘEDNÍ(B3,SEKVENCE(100),1),""))) |
| 3-> TEXTJOIN("",SKUTEČNÝ,LI(ISERROR({"T"; "h"; "i"; "s"; "", "1" … ""; ""}}+0),STŘEDNÍ(B3,SEKVENCE(100),1),""))) |
| 4-> TEXTJOIN("",SKUTEČNÝ,LI(ISERROR({#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!; 1…; #HODNOTA!}),STŘEDNÍ(B3,SEKVENCE(100),1),""))) |
| 5-> TEXTJOIN("",SKUTEČNÝ,LI({TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE},STŘEDNÍ(B3,SEKVENCE(100),1),""))) |
| 6-> TEXTJOIN("",SKUTEČNÝ,{"Tento";""… .;""}) |
| 7-> „Toto je web číslo jedna“ |
Kvůli snadnému čtení jsem nenapsal celé pole. K označení dlouhých polí jsem použil tečky (…).
Jak vidíte, vzorec se začíná řešit zevnitř. Nejprve je vyřešena funkce SEQUENCE. Od té doby, co jsme překročili počet 100 znaků. Vrátí řadu čísel od 1 do 100.
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 v poli. Můžete to vidět v kroku 3. Nezobrazil jsem celé pole, protože to zabere příliš mnoho místa. {"T"; "h"; "i"; "s"; "", "1" … ""; ""}}
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. {#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!; 1…; #HODNOTA!}
Toto pole je obsluhováno ISERROR funkce. Jak víte, funkce ISERROR vrací TRUE pro chyby a FALSE pro hodnoty bez chyb. Proto získáme řadu PRAVDIVÝCH a NEPRAVDIVÝCH. TRUE pro nečíselné znaky a FALSE čísel. {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}.
Totéž se stane s příkazy TRUE oddílu IF (STŘEDNÍ(B3,SEKVENCE(100),1)). Vrací pole všech znaků alfanumerického řetězce v B3.
Nyní pro každý příkaz TRUE vrátí funkce IF odpovídající znak z pole pravdivé sekce. V případě FALSE vrátí IF prázdné (""). Nyní budete mít pole, které neobsahuje žádný číselný znak. {"Tento";""… .;""}.
Nakonec toto pole slouží TEXTJOIN funkce. Tato funkce spojuje dané texty navzájem, přičemž ignoruje prázdné hodnoty, s daným oddělovačem. Proto dostaneme řetězec, který neobsahuje žádné číselné znaky. Toto je web číslo jedna.
Vylepšení vzorce
Výše uvedený vzorec používá pevně kódované číslo pro zpracování počtu znaků (vzali jsme 100). Ale možná budete chtít, aby to bylo dynamické. V tom případě jste to uhodli správně, můžete použít funkci LEN. Zpracování bude vyžadovat přesný počet znaků. Vzorec tedy bude.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jumbled_text, SEQUENCE (LEN(jumbled_text)(1) +0), MID (jumbled_text, SEQUENCE (LEN(jumbled_text),1),""))) |
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, IF (ISERROR (MID (jumbled_text,ŘÁDEK(NEPŘÍMÝ("1:"&LEN(NumChars)))(1) +0), MID (jumbled_text,ŘÁDEK(NEPŘÍMÝ("1:"&LEN(NumChars))),1),""))) |
NEPŘÍMÝ převede text („1: 100“) na skutečný rozsah a poté funkce ROW zobrazí všechna čísla řádků od 1 do 100. (100 je jen 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í.
Jak odebrat nečíselné znaky z buněk v aplikaci Excel 2019: K odstranění nečíselných znaků z alfanumerického řetězce v aplikaci Excel používáme novou funkci TEXTJOIN. Řetězec nečíselných znaků z řetězce nám může pomoci vyčistit naše data pro lepší analýzu dat. Zde je návod, jak to udělat
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.