V Exceli nie je vstavaná funkcia na extrahovanie čísel z reťazca v bunke (a naopak - odstráňte číselnú časť a extrahujte textovú časť z alfanumerického reťazca).
Dá sa to však urobiť pomocou funkcie Excelu alebo nejakého jednoduchého kódu VBA.
Najprv vám poviem, o čom hovorím.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete extrahovať čísla z reťazcov (ako je uvedené nižšie):
Metóda, ktorú si vyberiete, závisí aj od verzie Excelu, ktorú používate:
- Pre verzie Excelu pred rokom 2016 musíte použiť o niečo dlhší vzorec
- Pre Excel 2016 môžete použiť novo zavedenú funkciu TEXTJOIN
- Metóda VBA dostupná pre všetky verzie Excelu
Obsah
Extrahovanie čísel z reťazcov v Exceli (vzorce Excel 2016)
Tento vzorec funguje iba v Exceli 2016, pretože používa novo zavedenú funkciu TEXTJOIN.
Okrem toho môže tento vzorec extrahovať čísla na začiatku, konci alebo v strede textového reťazca.
Všimnite si, že vzorec TEXTJOIN opísaný v tejto časti spája všetky číselné znaky dohromady.Ak je napríklad text „10 lístkov je 200 USD“, výsledok je 10200 XNUMX.
Predpokladajme, že máte množinu údajov, ako je táto, a chcete extrahovať čísla z reťazcov v každej bunke:
Vzorec nižšie vám poskytne číselnú časť reťazca v Exceli.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Toto je vzorec poľa, takže musíte použiť " Control + Shift + Enter " namiesto použitia Enter.
Tento vzorec vráti prázdnu hodnotu (prázdny reťazec), ak v textovom reťazci nie sú žiadne čísla.
Ako tento vzorec funguje?
Dovoľte mi rozobrať tento vzorec a pokúsiť sa vysvetliť, ako to funguje:
- RIADOK(NEPRIAME(“1:”&DĹŽKA(A2))) – Táto časť vzorca poskytne sériu čísel začínajúcich od jednej.Funkcia LEN vo vzorci vráti celkový počet znakov v reťazci.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,RIADOK(NEPRIAME("1:"&DĹŽKA(A2))),1)*1) – Táto časť vzorca vráti pole #VALUEs!Chyby alebo čísla založené na reťazci.Všetky textové znaky v reťazci sa stanú #HODNOTA!Chyby a všetky hodnoty zostanú tak, ako sú.To sa stane, keď vynásobíme funkciu MID číslom 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Pri použití funkcie IFERROR sa odstránia všetky #HODNOTY!Chyba, zostanú len čísla.Výstup tejto časti vyzerá takto - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(NEPRIAME("1:"&LEN(A2))),1)*1),"")) – funkcia TEXTJOIN teraz jednoducho kombinuje reťazcové znaky Stále tam (iba čísla) a ignoruje prázdne reťazce.
Profi tip:Ak chcete skontrolovať výstup čiastočného vzorca, vyberte bunku, stlačením klávesu F2 prejdite do režimu úprav, vyberte časť vzorca, ktorý chcete vypísať, a stlačte kláves F9.Výsledky uvidíte okamžite.Potom nezabudnite stlačiť Control + Z alebo stlačte Escape.Nestláčajte enter.
Rovnakú logiku môžete použiť aj na extrahovanie častí textu z alfanumerických reťazcov.Tu je vzorec na získanie textovej časti z reťazca:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Malou zmenou v tomto vzorci je, že funkcia IF sa používa na kontrolu chýb v poli, ktoré získame z funkcie MID.Ak ide o chybu, ponechajte hodnotu, v opačnom prípade ju nahraďte prázdnou.
Potom použite TEXTJOIN na spojenie všetkých textových znakov.
注意: Hoci je tento vzorec platný, používa volatilnú funkciu (funkcia NEPRIAME).To znamená, že ak ho používate s obrovským súborom údajov, môže chvíľu trvať, kým získate výsledky.Pred použitím tohto vzorca v Exceli je najlepšie vytvoriť zálohu.
Extrahovanie čísel z reťazcov v Exceli (pre Excel 2013/2010/2007)
Ak máte Excel 2013. 2010. alebo 2007, nemôžete použiť vzorce TEXTJOIN, takže na to musíte použiť zložité vzorce.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete extrahovať všetky čísla v reťazcoch v každej bunke.
Nižšie uvedený vzorec to urobí:
=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))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Tento vzorec vráti prázdnu hodnotu (prázdny reťazec), ak v textovom reťazci nie sú žiadne čísla.
Aj keď ide o vzorec poľa, vyNie je potrebnéPoužite "Ctrl-Shift-Enter".Pre tento vzorec funguje jednoduchý vstup.
Zásluha za tento vzorec patrí úžasnýmPán Excel Fórum.
Tento vzorec opäť extrahuje všetky čísla v reťazci bez ohľadu na pozíciu.Ak je napríklad text „10 lístkov je 200 USD“, výsledok je 10200 XNUMX.
注意: Hoci je tento vzorec platný, používa volatilnú funkciu (funkcia NEPRIAME).To znamená, že ak ho používate s obrovským súborom údajov, môže chvíľu trvať, kým získate výsledky.Pred použitím tohto vzorca v Exceli je najlepšie vytvoriť zálohu.
Oddeľte text a čísla v Exceli pomocou VBA
Ak často potrebujete oddeliť text a čísla (alebo extrahovať čísla z textu), môžete použiť aj metódu VBA.
Všetko, čo musíte urobiť, je vytvoriť vlastnú používateľom definovanú funkciu (UDF) v Exceli pomocou jednoduchého kódu VBA a potom použiť tento vzorec VBA namiesto dlhého a zložitého vzorca.
Dovoľte mi ukázať vám, ako vytvoriť dva vzorce vo VBA – jeden na extrahovanie čísel a jeden na extrahovanie textu z reťazcov.
Extrahovanie čísel z reťazcov v Exceli (pomocou VBA)
V tejto časti vám ukážem, ako vytvoriť vlastnú funkciu na získanie iba numerickej časti z reťazca.
Tu je kód VBA, ktorý použijeme na vytvorenie tejto vlastnej funkcie:
Funkcia GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Ďalej i GetNumeric = Funkcia konca výsledku
Tu sú kroky na vytvorenie tejto funkcie a jej použitie v pracovnom hárku:
Teraz budete môcť na pracovnom hárku použiť funkciu GetText.Keďže sme urobili všetku ťažkú prácu v samotnom kóde, všetko, čo musíte urobiť, je použiť vzorec =GetNumeric(A2).
To vám okamžite poskytne číselnú časť reťazca.
Upozorňujeme, že keďže zošit teraz obsahuje kód VBA, musíte ho uložiť s príponou .xls alebo .xlsm.
Ak tento vzorec musíte 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 akomkoľvek zošite programu Excel, ktorý používate.
Extrahovať text z reťazca v Exceli (pomocou VBA)
V tejto časti vám ukážem, ako vytvoriť vlastnú funkciu na získanie iba textovej časti z reťazca.
Tu je kód VBA, ktorý použijeme na vytvorenie tejto vlastnej funkcie:
Funkcia GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Ďalej i GetText = Result End Function
Tu sú kroky na vytvorenie tejto funkcie a jej použitie v pracovnom hárku:
Teraz budete môcť v pracovnom hárku použiť funkciu GetNumeric.Keďže sme urobili všetku ťažkú prácu v samotnom kóde, všetko, čo musíte urobiť, je použiť vzorec =GetText(A2).
To vám okamžite poskytne číselnú časť reťazca.
Upozorňujeme, že keďže zošit teraz obsahuje kód VBA, musíte ho uložiť s príponou .xls alebo .xlsm.
Ak tento vzorec musíte 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 akomkoľvek zošite programu Excel, ktorý používate.
