✅ Izvlecite številke iz niza v Excelu (z uporabo formul ali VBA)

Kazalo

Izvlecite številke iz niza v Excelu (Formula za Excel 2016)
Izvlecite številke iz niza v Excelu (za Excel 2013/2010/2007)
Ločite besedilo in številke v Excelu z uporabo VBA

Oglejte si video - Kako izvleči številke iz besedilnega niza v Excelu (z uporabo formule in VBA)

V Excelu ni vgrajene funkcije za ekstrahiranje številk iz niza v celici (ali obratno - odstranite številski del in izvlecite besedilni del iz alfanumeričnega niza).

Vendar pa je to mogoče storiti s koktajlom Excelovih funkcij ali preprosto kodo VBA.

Naj vam najprej pokažem, o čem govorim.

Recimo, da imate niz podatkov, kot je prikazano spodaj, in želite izvleči številke iz niza (kot je prikazano spodaj):

Izbrana metoda bo odvisna tudi od različice Excela, ki jo uporabljate:

Za različice pred Excelom 2016 morate uporabiti nekoliko daljše formule
Za Excel 2016 lahko uporabite na novo predstavljeno funkcijo TEXTJOIN
Metodo VBA je mogoče uporabiti v vseh različicah Excela

Kliknite tukaj, če želite prenesti primer datoteke

Izvlecite številke iz niza v Excelu (Formula za Excel 2016)

Ta formula bo delovala samo v Excelu 2016, saj uporablja na novo predstavljeno funkcijo TEXTJOIN.

Ta formula lahko tudi izvleče številke, ki so na začetku, koncu ali sredi besedilnega niza.

Upoštevajte, da bi formula TEXTJOIN, zajeta v tem razdelku, dala vse številske znake skupaj. Na primer, če je besedilo »Cena 10 vstopnic 200 USD«, boste kot rezultat dobili 10200.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izvleči številke iz nizov v vsaki celici:

Spodaj je formula, ki vam bo dala številčni del iz niza v Excelu.

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

To je matrična formula, zato morate uporabiti "Control + Shift + Enter"Namesto da bi uporabili Enter.

Če v besedilnem nizu ni številk, bi ta formula vrnila prazno (prazen niz).

Kako deluje ta formula?

Naj razčlenim to formulo in poskusim razložiti, kako deluje:

ROW (INDIRECT (“1:” & LEN (A2))) - ta del formule bi dal niz številk, ki se začnejo od ena. Funkcija LEN v formuli vrne skupno število znakov v nizu. V primeru »Cena je 100 USD« se bo vrnilo 19. Formule bi tako postale ROW (INDIRECT (»1:19«). Funkcija ROW bo nato vrnila vrsto številk - {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 bi vrnil niz #VALUE! napake ali številke na podlagi niza. Vsi besedilni znaki v nizu postanejo #VALUE! napake in vse številčne vrednosti ostanejo takšne, kot so. To se zgodi, ko smo funkcijo MID pomnožili z 1.
IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - Ko se uporabi funkcija IFERROR, bi se odstranile vse #VALUE! napake in ostale bodo le številke. Rezultat tega dela bi bil videti tako - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ”” ”;” ”; ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””))) - Funkcija TEXTJOIN zdaj preprosto združuje znake niza ki ostane (ki so samo številke) in prezre prazen niz.

Nasvet za profesionalce: Če želite preveriti izhod dela formule, izberite celico, pritisnite F2 za vstop v način urejanja, izberite del formule, za katerega želite izpis, in pritisnite F9. Takoj boste videli rezultat. Nato ne pozabite pritisniti Control + Z ali pritisniti tipke Escape. NE pritiskajte tipke enter.

Prenesite datoteko z vzorcem

Z isto logiko lahko izvlečete tudi besedilni del iz alfanumeričnega niza. Spodaj je formula, ki bi dobila besedilni del iz niza:

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

Manjša sprememba te formule je, da se funkcija IF uporablja za preverjanje, ali je matrika, ki jo dobimo iz funkcije MID, napaka ali ne. Če gre za napako, ohrani vrednost, sicer pa jo nadomesti s prazno.

Nato se TEXTJOIN uporablja za združevanje vseh besedilnih znakov.

Pozor: Čeprav ta formula deluje odlično, uporablja hlapno funkcijo (funkcija INDIRECT). To pomeni, da lahko v primeru, da to uporabite z velikim naborom podatkov, traja nekaj časa, da dobite rezultate. Pred uporabo te formule v Excelu je najbolje ustvariti varnostno kopijo.

Izvlecite številke iz niza v Excelu (za Excel 2013/2010/2007)

Če imate Excel 2013. 2010. ali 2007, ne morete uporabiti formule TEXTJOIN, zato boste morali za to uporabiti zapleteno formulo.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izvleči vse številke v nizu v vsaki celici.

Spodnja formula bo to naredila:

= IF (SUM (LEN (A2) -LEN (ZAMENJA (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), "")

Če v besedilnem nizu ni številke, bi ta formula vrnila prazno (prazen niz).

Čeprav je to formula matrike, vi ni treba Če želite to uporabiti, uporabite »Control-Shift-Enter«. Za to formulo deluje preprost vnos.

Ta formula je zaslužna za neverjeten forum gospoda Excela.

Ponovno bo ta formula izvlekla vse številke v nizu ne glede na položaj. Na primer, če je besedilo »Cena 10 vstopnic 200 USD«, boste kot rezultat dobili 10200.

Ločite besedilo in številke v Excelu z uporabo VBA

Če morate pogosto ločevati besedilo in številke (ali izvleči številke iz besedila), lahko uporabite tudi metodo VBA.

Vse, kar morate storiti, je, da s preprosto kodo VBA ustvarite uporabniško definirano funkcijo (UDF) v Excelu, nato pa namesto dolgih in zapletenih formul uporabite to formulo VBA.

Dovolite mi, da vam pokažem, kako v VBA ustvarite dve formuli - eno za ekstrahiranje številk in eno za izvlečenje besedila iz niza.

Izvlecite številke iz niza v Excelu (z uporabo VBA)

V tem delu vam bom pokazal, kako ustvarite funkcijo po meri, da iz niza dobite samo številčni del.

Spodaj je koda VBA, ki jo bomo uporabili za ustvarjanje te funkcije po meri:

Funkcija GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Potem Result = Result & Mid (CellRef, i, 1) Naprej i GetNumeric = Končna funkcija rezultata

Za ustvarjanje te funkcije in njeno uporabo na delovnem listu uporabite naslednje korake:

Pojdite na zavihek Razvijalec.
Kliknite Visual Basic (Uporabite lahko tudi bližnjico na tipkovnici ALT + F11)
V zaledju urejevalnika VB, ki se odpre, z desno tipko miške kliknite kateri koli predmet delovnega zvezka.
Pojdite na Vstavi in kliknite Modul. To bo vstavilo predmet modula za delovni zvezek.
V oknu Module code kopirajte in prilepite zgoraj omenjeno kodo VBA.
Zaprite urejevalnik VB.

Zdaj boste lahko na delovnem listu uporabili funkcijo GetText. Ker smo v sami kodi opravili vsa težka dela, morate le uporabiti formulo = GetNumeric (A2).

To vam bo takoj dalo samo številčni del niza.

Upoštevajte, da mora delovni zvezek zdaj vsebovati kodo VBA, zato jo morate shraniti z razširitvijo .xls ali .xlsm.

Prenesite datoteko z vzorcem

Če morate pogosto uporabljati to formulo, jo lahko shranite tudi v svoj osebni makro delovni zvezek. To vam bo omogočilo uporabo te formule po meri v katerem koli Excelovem delovnem zvezku, s katerim delate.

Izvleči 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.

Spodaj je koda VBA, ki jo bomo uporabili za ustvarjanje te funkcije po meri:

Funkcija GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Potem Result = Result & Mid (CellRef, i, 1 ) Naprej i GetText = Končna funkcija rezultata

Za ustvarjanje te funkcije in njeno uporabo na delovnem listu uporabite naslednje korake:

Pojdite na zavihek Razvijalec.
Kliknite Visual Basic (Uporabite lahko tudi bližnjico na tipkovnici ALT + F11)
V zaledju urejevalnika VB, ki se odpre, z desno tipko miške kliknite kateri koli predmet delovnega zvezka.
Pojdite na Vstavi in kliknite Modul. To bo vstavilo predmet modula za delovni zvezek.
- Če že imate modul, ga dvokliknite (novega ni treba vstavljati, če ga že imate).
V oknu Module code kopirajte in prilepite zgoraj omenjeno kodo VBA.
Zaprite urejevalnik VB.

Zdaj boste lahko na delovnem listu uporabili funkcijo GetNumeric. Ker smo v sami kodi opravili vsa težka dela, morate le uporabiti formulo = GetText (A2).

To vam bo takoj dalo samo številčni del niza.