✅ Extrahujte čísla z řetězce v aplikaci Excel (pomocí vzorců nebo VBA)

Obsah

Extrahovat čísla z řetězce v Excelu (Vzorec pro Excel 2016)
Extrahovat čísla z řetězce v Excelu (pro Excel 2013/2010/2007)
Oddělte text a čísla v aplikaci Excel pomocí VBA

Podívejte se na video - Jak extrahovat čísla z textových řetězců v aplikaci Excel (pomocí vzorců a VBA)

V aplikaci Excel neexistuje žádná vestavěná funkce, která by extrahovala čísla z řetězce v buňce (nebo naopak - odeberte číselnou část a extrahujte textovou část z alfanumerického řetězce).

To však lze provést pomocí koktejlu funkcí aplikace Excel nebo nějakého jednoduchého kódu VBA.

Nejprve vám ukážu, o čem mluvím.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete čísla extrahovat z řetězce (jak je uvedeno níže):

Metoda, kterou zvolíte, bude také záviset na verzi aplikace Excel, kterou používáte:

U verzí starších než Excel 2016 musíte použít mírně delší vzorce
Pro Excel 2016 můžete použít nově zavedenou funkci TEXTJOIN
Metodu VBA lze použít ve všech verzích aplikace Excel

Kliknutím sem stáhnete ukázkový soubor

Extrahovat čísla z řetězce v Excelu (Vzorec pro Excel 2016)

Tento vzorec bude fungovat pouze v aplikaci Excel 2016, protože používá nově zavedenou funkci TEXTJOIN.

Tento vzorec také může extrahovat čísla, která jsou na začátku, na konci nebo uprostřed textového řetězce.

Všimněte si, že vzorec TEXTJOIN popsaný v této části vám poskytne všechny číselné znaky dohromady. Pokud je například text „Cena 10 lístků je 200 USD“, výsledkem bude 10200.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete extrahovat čísla z řetězců v každé buňce:

Níže je vzorec, který vám poskytne číselnou část z řetězce v aplikaci Excel.

= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), ""))

Toto je maticový vzorec, takže musíte použít „Ctrl + Shift + Enter‘Namísto použití Enter.

V případě, že v textovém řetězci nejsou žádná čísla, tento vzorec vrátí prázdný (prázdný řetězec).

Jak tento vzorec funguje?

Dovolte mi rozbít tento vzorec a zkusit vysvětlit, jak funguje:

ŘÁDEK (NEPŘÍMÝ („1:“ & LEN (A2))) - tato část vzorce by dala řadu čísel začínajících na jedničku. Funkce LEN ve vzorci vrací celkový počet znaků v řetězci. V případě „Náklady jsou 100 USD“ se vrátí 19. Vzorce by se tak změnily na ŘÁDEK (NEPŘÍMÝ („1:19“). Funkce ŘÁD pak vrátí řadu čísel - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1) - Tato část vzorce vrátí pole #HODNOTA! chyby nebo čísla na základě řetězce. Všechny textové znaky v řetězci se stanou #HODNOTOU! chyby a všechny číselné hodnoty zůstávají tak, jak jsou. K tomu dochází, když jsme vynásobili funkci MID hodnotou 1.
IFERROR ((MID (A2, ROW (INDIRECT („1:“ & LEN (A2))), 1)*1), ””)) - Pokud je použita funkce IFERROR, odstraní by se #HODNOTA! chyby a zůstala by jen čísla. Výstup této části by vypadal takto - {“”; ””; ””; “”; “”; “” ”” ”” ”” ”” ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT („1:” & LEN (A2))), 1)*1), ””))) - funkce TEXTJOIN nyní jednoduše kombinuje znaky řetězce který zůstane (což jsou pouze čísla) a ignoruje prázdný řetězec.

Profesionální tip: Pokud chcete zkontrolovat výstup části vzorce, vyberte buňku, stisknutím klávesy F2 se dostanete do režimu úprav, vyberte část vzorce, pro kterou chcete výstup, a stiskněte klávesu F9. Výsledek uvidíte okamžitě. A pak nezapomeňte stisknout Ctrl + Z nebo stisknout klávesu Escape. NESMÍTE stisknout klávesu Enter.

Stáhněte si ukázkový soubor

Stejnou logiku můžete také použít k extrahování textové části z alfanumerického řetězce. Níže je vzorec, který by získal textovou část z řetězce:

= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN) (A2))), 1), ""))

Drobnou změnou v tomto vzorci je, že funkce IF slouží ke kontrole, zda pole, které získáme z funkce MID, jsou chyby nebo ne. Pokud se jedná o chybu, ponechá si hodnotu, kterou nahradí, prázdnou.

Poté se pomocí TEXTJOIN spojí všechny textové znaky.

Pozor: I když tento vzorec funguje skvěle, používá volatilní funkci (funkce NEPŘÍMÉ). To znamená, že v případě, že to použijete s obrovskou množinou dat, může chvíli trvat, než vám výsledky poskytneme. Před použitím tohoto vzorce v aplikaci Excel je nejlepší vytvořit zálohu.

Extrahovat čísla z řetězce v Excelu (pro Excel 2013/2010/2007)

Pokud máte Excel 2013. 2010. nebo 2007, nemůžete použít vzorec TEXTJOIN, takže k jeho provedení budete muset použít složitý vzorec.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete extrahovat všechna čísla v řetězci v každé buňce.

Následující vzorec to provede:

= IF (SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (-MID (A2, ROW (INDIRECT (" $ 1: $ "& LEN (A2)))), 1)) * ŘÁDEK (NEPŘÍMÝ ("$ 1: $" & LEN (A2))), 0), ROW (NEPŘÍMÝ ("$ 1: $" & LEN (A2))))+1,1) * 10^ŘADA (NEPŘÍMÝ ("$ 1: $" & LEN (A2)))/10), "")

V případě, že v textovém řetězci není žádné číslo, vrátí tento vzorec prázdný (prázdný řetězec).

Ačkoli se jedná o vzorec pole, vy nepotřebuji k tomu použijte „Control-Shift-Enter“. Pro tento vzorec funguje jednoduché zadání.

Kredit na tento vzorec patří úžasnému fóru Mr. Excel.

Tento vzorec opět extrahuje všechna čísla v řetězci bez ohledu na pozici. Pokud je například text „Cena 10 lístků je 200 USD“, výsledkem bude 10200.

Oddělte text a čísla v aplikaci Excel pomocí VBA

Pokud je oddělování textu a čísel (nebo extrahování čísel z textu) něco, co musíte dělat často, můžete také použít metodu VBA.

Vše, co musíte udělat, je použít jednoduchý kód VBA k vytvoření vlastní funkce definované uživatelem (UDF) v aplikaci Excel a poté místo použití dlouhých a složitých vzorců použít tento vzorec VBA.

Ukážu vám, jak ve VBA vytvořit dva vzorce - jeden pro extrahování čísel a jeden pro extrahování textu z řetězce.

Extrahujte čísla z řetězce v aplikaci Excel (pomocí VBA)

V této části vám ukážu, jak vytvořit vlastní funkci pro získání pouze číselné části z řetězce.

Níže je kód VBA, který použijeme k vytvoření této vlastní funkce:

Funkce GetNumeric (CellRef jako řetězec) Dim StringLength jako celé číslo StringLength = Len (CellRef) Pro i = 1 až StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Funkce ukončení výsledku

Zde jsou kroky k vytvoření této funkce a jejímu použití v listu:

Přejděte na kartu Vývojář.
Klikněte na Visual Basic (Můžete také použít klávesovou zkratku ALT + F11)
V otevřeném backendu VB editoru klikněte pravým tlačítkem na libovolný objekt sešitu.
Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro sešit.
V okně Kód modulu zkopírujte a vložte výše uvedený kód VBA.
Zavřete editor VB.

Nyní budete moci použít funkci GetText v listu. Vzhledem k tomu, že jsme provedli veškeré zvedání těžkých nákladů v samotném kódu, stačí použít vzorec = GetNumeric (A2).

To vám okamžitě poskytne pouze číselnou část řetězce.

Protože sešit nyní obsahuje kód VBA, musíte jej uložit s příponou .xls nebo .xlsm.

Stáhněte si ukázkový soubor

V případě, že budete muset tento vzorec používat často, můžete si ho také uložit do svého osobního sešitu maker. To vám umožní použít tento vlastní vzorec v kterémkoli ze sešitů aplikace Excel, se kterými pracujete.

Extrahujte text z řetězce v aplikaci Excel (pomocí VBA)

V této části vám ukážu, jak vytvořit vlastní funkci, abyste z řetězce získali pouze textovou část.

Níže je kód VBA, který použijeme k vytvoření této vlastní funkce:

Funkce GetText (CellRef jako řetězec) Dim StringLength jako celé číslo StringLength = Len (CellRef) Pro i = 1 až StringLength Pokud ne (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1 ) Další i GetText = Výsledná koncová funkce

Zde jsou kroky k vytvoření této funkce a jejímu použití v listu:

Přejděte na kartu Vývojář.
Klikněte na Visual Basic (Můžete také použít klávesovou zkratku ALT + F11)
V otevřeném backendu VB editoru klikněte pravým tlačítkem na libovolný objekt sešitu.
Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro sešit.
- Pokud již modul máte, poklepejte na něj (není třeba vložit nový, pokud ho již máte).
V okně Kód modulu zkopírujte a vložte výše uvedený kód VBA.
Zavřete editor VB.

Nyní budete moci použít funkci GetNumeric v listu. Protože jsme v samotném kódu provedli veškeré zvedání těžkých předmětů, stačí použít vzorec = GetText (A2).

To vám okamžitě poskytne pouze číselnou část řetězce.