✅ Extrahujte čísla z reťazca v programe Excel (pomocou vzorcov alebo VBA)

Obsah

Extrahujte čísla z reťazca v programe Excel (Vzorec pre Excel 2016)
Extrahovať čísla z reťazcov v programe Excel (pre Excel 2013/2010/2007)
Oddeľte text a čísla v programe Excel pomocou jazyka VBA

Pozrite si video - Ako extrahovať čísla z textových reťazcov v programe Excel (pomocou vzorcov a VBA)

V programe Excel nie je vstavaná funkcia na extrahovanie čísel z reťazca v bunke (alebo naopak - odstráňte číselnú časť a extrahujte textovú časť z alfanumerického reťazca).

To sa však dá urobiť pomocou kokteilu funkcií programu Excel alebo jednoduchého kódu VBA.

Najprv vám ukážem, o čom hovorím.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete extrahovať čísla z reťazca (ako je uvedené nižšie):

Metóda, ktorú si vyberiete, bude závisieť aj od verzie Excelu, ktorú používate:

Vo verziách starších ako Excel 2016 musíte použiť o niečo dlhšie vzorce
Pre Excel 2016 môžete použiť novo uvedenú funkciu TEXTJOIN
Metódu VBA je možné použiť vo všetkých verziách programu Excel

Kliknutím sem stiahnete ukážkový súbor

Extrahujte čísla z reťazca v programe Excel (Vzorec pre Excel 2016)

Tento vzorec bude fungovať iba v Exceli 2016, pretože používa novo uvedenú funkciu TEXTJOIN.

Tento vzorec môže tiež extrahovať čísla, ktoré sú na začiatku, konci alebo v strede textového reťazca.

Všimnite si toho, že vzorec TEXTJOIN obsiahnutý v tejto časti vám poskytne všetky číselné znaky dohromady. Ak je napríklad text „Cena 10 lístkov je 200 USD“, výsledkom bude 10200.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete extrahovať čísla z reťazcov v každej bunke:

Nasleduje vzorec, ktorý vám poskytne číselnú časť z reťazca v programe Excel.

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

Toto je vzorec poľa, takže musíte použiť „Ctrl + Shift + Enter‘Namiesto použitia klávesu Enter.

V prípade, že v textovom reťazci nie sú žiadne čísla, tento vzorec vráti prázdny (prázdny reťazec).

Ako tento vzorec funguje?

Dovoľte mi prelomiť tento vzorec a pokúsiť sa vysvetliť, ako funguje:

RIADOK (NEPRIAMY („1:“ & LEN (A2))) - táto časť vzorca by poskytla sériu čísel začínajúcich od jedného. Funkcia LEN vo vzorci vráti celkový počet znakov v reťazci. V prípade „Náklady sú 100 USD“ sa vráti 19. Vzorce by sa tak stali ROW (NEPRIAMY („1:19“). Funkcia ROW potom vráti sériu čísel - {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MID (A2, ROW (NEPRIAMY („1:“ & LEN (A2))), 1)*1) - Táto časť vzorca vráti pole #HODNOTA! chyby alebo čísla na základe reťazca. Všetky textové znaky v reťazci sa stanú #HODNOTOU! chyby a všetky číselné hodnoty zostanú tak, ako sú. Stáva sa to, pretože sme znásobili funkciu MID číslom 1.
IFERROR ((MID (A2, ROW (INDIRECT („1:“ & LEN (A2))), 1)*1), ””)) - Keď sa použije funkcia IFERROR, odstráni sa celá #HODNOTA! chyby a zostali by iba čísla. Výstup z tejto časti by vyzeral takto - {“”; ””; ””; ””; “”; “”; “”; “”; “; ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT („1:” & LEN (A2))), 1)*1), ””))) - funkcia TEXTJOIN teraz jednoducho kombinuje reťazcové znaky ktorý zostane (čo sú iba čísla) a prázdny reťazec ignoruje.

Pro tip: Ak chcete skontrolovať výstup časti vzorca, vyberte bunku, stlačením klávesu F2 sa dostanete do režimu úprav, vyberte časť vzorca, pre ktorý chcete výstup, a stlačte kláves F9. Výsledok uvidíte okamžite. A potom nezabudnite buď stlačiť kombináciu klávesov Control + Z, alebo stlačiť kláves Escape. NEDÁVAJTE kláves Enter.

Stiahnite si ukážkový súbor

Rovnakú logiku môžete použiť aj na extrahovanie textovej časti z alfanumerického reťazca. Nasleduje vzorec, ktorý by získal textovú časť z reťazca:

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

Menšou zmenou v tomto vzorci je, že funkcia IF sa používa na kontrolu, či pole, ktoré získavame z funkcie MID, sú chyby alebo nie. Ak ide o chybu, ponechá hodnotu, ktorú nahradí, prázdnym miestom.

Potom sa pomocou TEXTJOIN spoja všetky textové znaky.

Pozor: Aj keď tento vzorec funguje skvele, používa volatilnú funkciu (funkcia NEPRIAMY). To znamená, že v prípade, že ho použijete s veľkým súborom údajov, môže dosiahnutie určitého výsledku trvať nejaký čas. Pred použitím tohto vzorca v programe Excel je najlepšie vytvoriť zálohu.

Extrahovať čísla z reťazcov v programe Excel (pre Excel 2013/2010/2007)

Ak máte Excel 2013. 2010. alebo 2007, nemôžete použiť vzorec TEXTJOIN, takže na jeho vykonanie budete musieť použiť zložitý vzorec.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete extrahovať všetky čísla v reťazci v každej bunke.

Nasledujúci vzorec to urobí:

= IF (SUM (LEN (A2) -LEN (SUBSTITUTE (A2; {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," "))))> 0, SUMPRODUCT (MID (0 a A2, VEĽKÝ (INDEX (ISNUMBER (-MID (A2, ROW (NEPRIAMY) (" $ 1: $ "& LEN (A2))), 1)) * RIADOK (NEPRIAMY („$ 1: $“ & LEN (A2))), 0), ROW (NEPRIAMY („$ 1: $“ & LEN (A2))))+1,1) * 10^ROW (NEPRIAMY („$ 1: $“ & LEN (A2)))/10), „“)

V prípade, že v textovom reťazci nie je žiadne číslo, tento vzorec by vrátil prázdny (prázdny reťazec).

Aj keď je to vzorec poľa, vy nepotrebujem použiť na to „Control-Shift-Enter“. Pre tento vzorec funguje jednoduché zadanie.

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

Tento vzorec opäť extrahuje všetky čísla v reťazci bez ohľadu na pozíciu. Ak je napríklad text „Cena 10 lístkov je 200 USD“, výsledkom bude 10200.

Pozor: Aj keď tento vzorec funguje skvele, používa volatilnú funkciu (funkcia NEPRIAMY). To znamená, že v prípade, že to použijete s rozsiahlou množinou údajov, môže získanie výsledkov trvať nejaký čas. Pred použitím tohto vzorca v programe Excel je najlepšie vytvoriť zálohu.

Oddeľte text a čísla v programe Excel pomocou jazyka VBA

Ak oddeľovanie textu a číslic (alebo extrahovanie čísel z textu) musíte vykonávať často, môžete tiež použiť metódu VBA.

Všetko, čo musíte urobiť, je použiť jednoduchý kód VBA na vytvorenie vlastnej funkcie definovanej používateľom (UDF) v programe Excel a potom namiesto dlhých a komplikovaných vzorcov použiť tento vzorec VBA.

Ukážem vám, ako vo VBA vytvoriť dva vzorce - jeden na extrahovanie čísel a jeden na extrahovanie textu z reťazca.

Extrahujte čísla z reťazcov v programe Excel (pomocou VBA)

V tejto časti vám ukážem, ako vytvoriť vlastnú funkciu tak, aby ste z reťazca získali iba číselnú časť.

Nasleduje kód VBA, ktorý použijeme na vytvorenie tejto vlastnej funkcie:

Funkcia GetNumeric (CellRef ako reťazec) Dim StringLength ako celé číslo StringLength = Len (CellRef) For i = 1 až StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Funkcia ukončenia výsledku

Tu je postup, ako vytvoriť túto funkciu a potom ju použiť v pracovnom hárku:

Prejdite na kartu Vývojár.
Kliknite na položku Visual Basic (Môžete tiež použiť klávesovú skratku ALT + F11)
V otvorenom backende editora VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita.
Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do zošita.
V okne Kód modulu skopírujte a prilepte vyššie uvedený kód VBA.
Zatvorte editor VB.

Teraz budete môcť v hárku používať funkciu GetText. Pretože sme urobili všetko ťažké v samotnom kóde, stačí použiť vzorec = GetNumeric (A2).

To vám okamžite poskytne iba číselnú časť reťazca.

Keďže zošit teraz obsahuje kód VBA, musíte ho uložiť s príponou .xls alebo .xlsm.

Stiahnite si ukážkový súbor

V prípade, že budete musieť tento vzorec používať často, môžete si ho uložiť aj do osobného zošita makier. To vám umožní použiť tento vlastný vzorec v ktoromkoľvek zošite programu Excel, s ktorým pracujete.

Extrahujte text z reťazca v programe Excel (pomocou VBA)

V tejto časti vám ukážem, ako vytvoriť vlastnú funkciu na získanie iba textovej časti z reťazca.

Nasleduje kód VBA, ktorý použijeme na vytvorenie tejto vlastnej funkcie:

Funkcia GetText (CellRef ako reťazec) Dim StringLength ako celé číslo StringLength = Len (CellRef) Pre i = 1 až StringLength ak nie (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1 ) Ďalej i GetText = funkcia ukončenia výsledku

Tu je postup, ako vytvoriť túto funkciu a potom ju použiť v pracovnom hárku:

Prejdite na kartu Vývojár.
Kliknite na položku Visual Basic (Môžete tiež použiť klávesovú skratku ALT + F11)
V otvorenom backende editora VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita.
Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do zošita.
- Ak už modul máte, dvakrát naň kliknite (nie je potrebné vkladať nový, ak ho už máte).
V okne Kód modulu skopírujte a prilepte vyššie uvedený kód VBA.
Zatvorte editor VB.