V Excelu ni vgrajene funkcije za ekstrakcijo številk iz niza v celici (in obratno – odstranite številčni del in izvlecite besedilni del iz alfanumeričnega niza).
Vendar je to mogoče storiti s funkcijo Excel ali s kakšno preprosto kodo VBA.
Naj vam najprej povem, o čem govorim.
Recimo, da imate nabor podatkov, kot je spodaj, in želite izvleči številke iz nizov (kot je spodaj):
Metoda, ki jo izberete, je odvisna tudi od različice Excela, ki jo uporabljate:
- Za različice Excela pred letom 2016 morate uporabiti nekoliko daljšo formulo
- Za Excel 2016 lahko uporabite novo uvedeno funkcijo TEXTJOIN
- Metoda VBA je na voljo za vse različice Excela
vsebina
Izvlecite številke iz nizov v Excelu (formule Excel 2016)
Ta formula deluje samo v Excelu 2016, ker uporablja na novo uvedeno funkcijo TEXTJOIN.
Poleg tega lahko ta formula izvleče številke na začetku, koncu ali sredini besedilnega niza.
Upoštevajte, da formula TEXTJOIN, opisana v tem razdelku, združuje vse številske znake.Na primer, če je besedilo "10 vstopnic je 200 $", je rezultat 10200.
Recimo, da imate nabor podatkov, kot je ta, in želite izvleči številke iz nizov v vsaki celici:
Spodnja formula vam bo dala številčni del niza v Excelu.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
To je formula matrike, zato morate uporabiti " Control + Shift + Enter " namesto da bi uporabili Enter.
Ta formula bo vrnila prazen (prazen niz), če v besedilnem nizu ni številk.
Kako deluje ta formula?
Naj razčlenim to formulo in poskušam razložiti, kako deluje:
- ROW(INDIRECT(“1:”&LEN(A2))) – Ta del formule bo dal niz številk, ki se začnejo od ena.Funkcija LEN v formuli vrne skupno število znakov v nizu.在“成本為 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,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - Ta del formule bo vrnil niz #VREDNOSTI!Napake ali številke na podlagi nizov.Vsi besedilni znaki v nizu postanejo #VALUE!Napake in vse vrednosti ostanejo takšne, kot so.To se zgodi, ko funkcijo MID pomnožimo z 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Ko uporabljate funkcijo IFERROR, se vse #VREDNOSTI odstranijo!Napaka, ostale bodo samo številke.Izhod tega dela je videti takole - {"";"";"";"";"";"";"";"";"";"";"";"";""";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – funkcija TEXTJOIN zdaj preprosto združuje niz znakov. Še vedno obstaja (samo številke) in ignorira prazne nize.
Profesionalni nasvet:Če želite preveriti izhod dela formule, izberite celico, pritisnite F2 za vstop v način urejanja, izberite del formule, ki ga želite izpisati, in pritisnite F9.Rezultate boste videli takoj.Nato ne pozabite pritisniti Control + Z ali pritisniti Escape.Ne pritiskajte enter.
Enako logiko lahko uporabite tudi za ekstrakcijo delov besedila iz alfanumeričnih nizov.Tukaj je formula za pridobitev besedilnega dela iz niza:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Majhna sprememba te formule je, da se funkcija IF uporablja za preverjanje matrike, ki jo dobimo od funkcije MID, za napake.Če gre za napako, obdržite vrednost, sicer jo zamenjajte s prazno.
Nato uporabite TEXTJOIN, da združite vse besedilne znake.
注意: Čeprav je ta formula veljavna, uporablja hlapno funkcijo (INDIRECT funkcija).To pomeni, da če ga uporabljate z velikim naborom podatkov, lahko traja nekaj časa, da dobite rezultate.Najbolje je, da ustvarite varnostno kopijo, preden uporabite to formulo v Excelu.
Izvlecite številke iz nizov v Excelu (za Excel 2013/2010/2007)
Če imate Excel 2013. 2010. ali 2007, ne morete uporabljati formul TEXTJOIN, zato morate za to uporabiti zapletene formule.
Recimo, da imate nabor podatkov, kot je spodaj, in želite izvleči vse številke v nizih v vsaki celici.
Spodnja formula bo naredila to:
=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),"")
Ta formula bo vrnila prazen (prazen niz), če v besedilnem nizu ni številk.
Čeprav je to formula matrike, viNi potrebeZa uporabo uporabite "Control-Shift-Enter".Za to formulo deluje preprost vnos.
Zasluge za to formulo gredo neverjetnemuG. Excel Forum.
Tudi ta formula bo izvlekla vse številke v nizu ne glede na položaj.Na primer, če je besedilo "10 vstopnic je 200 $", je rezultat 10200.
注意: Čeprav je ta formula veljavna, uporablja hlapno funkcijo (INDIRECT funkcija).To pomeni, da če ga uporabljate z velikim naborom podatkov, lahko traja nekaj časa, da dobite rezultate.Najbolje je, da ustvarite varnostno kopijo, preden uporabite to formulo v Excelu.
Ločite besedilo in številke v Excelu z VBA
Če morate pogosto ločiti besedilo in številke (ali črpati številke iz besedila), lahko uporabite tudi metodo VBA.
Vse, kar morate storiti, je ustvariti uporabniško definirano funkcijo (UDF) v Excelu s preprosto kodo VBA in nato uporabiti to formulo VBA namesto dolge in zapletene formule.
Naj vam pokažem, kako ustvariti dve formuli v VBA - eno za ekstrakcijo številk in drugo za ekstrakcijo besedila iz nizov.
Izvlecite številke iz nizov v Excelu (z uporabo VBA)
V tem delu vam bom pokazal, kako ustvarite funkcijo po meri, da iz niza dobite samo številčni del.
Tukaj je koda VBA, ki jo bomo uporabili za ustvarjanje te funkcije po meri:
Funkcija GetNumeric(CellRef kot niz) Dim StringLength kot celo število StringLength = Len(CellRef) Za i = 1 do StringLength Če je IsNumeric(Mid(CellRef, i, 1)) Potem je rezultat = Result & Mid(CellRef, i, 1) Naslednji i GetNumeric = Končna funkcija rezultata
Tukaj so koraki za ustvarjanje te funkcije in jo nato uporabite na delovnem listu:
Zdaj boste na delovnem listu lahko uporabljali funkcijo GetText.Ker smo v sami kodi opravili vse težke naloge, morate uporabiti formulo =GetNumeric(A2).
Tako boste takoj dobili številčni del niza.
Ker delovni zvezek zdaj vsebuje kodo VBA, ga boste morali shraniti z razširitvijo .xls ali .xlsm.
Če morate to formulo pogosto uporabljati, jo lahko shranite tudi v svoj osebni delovni zvezek z makrami.To vam bo omogočilo uporabo te formule po meri v katerem koli delovnem zvezku Excel, ki ga uporabljate.
Izvlecite besedilo iz niza v Excelu (z uporabo VBA)
V tem delu vam bom pokazal, kako ustvarite funkcijo po meri, da iz niza dobite samo del besedila.
Tukaj je koda VBA, ki jo bomo uporabili za ustvarjanje te funkcije po meri:
Funkcija GetText(CellRef kot niz) Dim StringLength kot celo število StringLength = Len(CellRef) Za i = 1 do StringLength Če ne (IsNumeric(Mid(CellRef, i, 1))) Potem je rezultat = Result & Mid(CellRef, i, 1 ) Naprej i GetText = Končna funkcija rezultata
Tukaj so koraki za ustvarjanje te funkcije in jo nato uporabite na delovnem listu:
Zdaj boste na delovnem listu lahko uporabljali funkcijo GetNumeric.Ker smo v sami kodi opravili vse težke naloge, morate le uporabiti formulo =GetText(A2).
Tako boste takoj dobili številčni del niza.
Ker delovni zvezek zdaj vsebuje kodo VBA, ga boste morali shraniti z razširitvijo .xls ali .xlsm.
Če morate to formulo pogosto uporabljati, jo lahko shranite tudi v svoj osebni delovni zvezek z makrami.To vam bo omogočilo uporabo te formule po meri v katerem koli delovnem zvezku Excel, ki ga uporabljate.
