✅ Izdvajanje brojeva iz niza u Excelu (pomoću formula ili VBA)

Sadržaj

Izdvajanje brojeva iz niza u Excelu (Formula za Excel 2016)
Izdvajanje brojeva iz niza u Excelu (za Excel 2013/2010/2007)
Odvojite tekst i brojeve u Excelu pomoću VBA

Pogledajte video - Kako izdvojiti brojeve iz tekstualnog niza u Excelu (pomoću formule i VBA)

U Excelu nema ugrađene funkcije za izdvajanje brojeva iz niza u ćeliji (ili obrnuto - uklonite numerički dio i izvucite tekstualni dio iz alfanumeričkog niza).

Međutim, to se može učiniti pomoću koktela Excel funkcija ili nekog jednostavnog VBA koda.

Dopustite mi da vam prvo pokažem o čemu govorim.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izdvojiti brojeve iz niza (kao što je prikazano u nastavku):

Način koji odaberete ovisit će i o verziji programa Excel koju koristite:

Za verzije prije Excela 2016 morate koristiti nešto dulje formule
Za Excel 2016 možete koristiti novo predstavljenu funkciju TEXTJOIN
VBA metoda može se koristiti u svim verzijama programa Excel

Kliknite ovdje za preuzimanje datoteke primjera

Izdvajanje brojeva iz niza u Excelu (Formula za Excel 2016)

Ova će formula raditi samo u Excelu 2016 jer koristi novo predstavljenu funkciju TEXTJOIN.

Također, ova formula može izdvojiti brojeve koji su na početku, kraju ili sredini tekstualnog niza.

Imajte na umu da bi vam formula TEXTJOIN prikazana u ovom odjeljku dala sve numeričke znakove zajedno. Na primjer, ako je tekst “Cijena 10 ulaznica 200 USD”, dobit ćete 10200 kao rezultat.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izdvojiti brojeve iz nizova u svakoj ćeliji:

Ispod je formula koja će vam dati numerički dio iz niza u Excelu.

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

Ovo je formula niza, pa morate koristiti 'Control + Shift + Enter‘Umjesto da koristite Enter.

U slučaju da u tekstualnom nizu nema brojeva, ova bi formula vratila prazno (prazan niz).

Kako funkcionira ova formula?

Dopustite mi da razbijem ovu formulu i pokušam objasniti kako to funkcionira:

ROW (INDIRECT (“1:” & LEN (A2))) - ovaj dio formule dao bi niz brojeva koji počinju od jedan. Funkcija LEN u formuli vraća ukupan broj znakova u nizu. U slučaju "Cijena je 100 USD", vratit će se 19. Formule bi tako postale ROW (INDIRECT ("1:19"). Funkcija ROW tada će vratiti niz brojeva - {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) - Ovaj dio formule vratio bi niz od #VALUE! pogreške ili brojevi na temelju niza. Svi tekstualni znakovi u nizu postaju #VREDNOSTI! pogreške i sve numeričke vrijednosti ostaju kakve jesu. To se događa jer smo pomnožili funkciju MID s 1.
IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - Kad se koristi funkcija IFERROR, uklonile bi se sve vrijednosti #VALUE! greške i ostali bi samo brojevi. Ispis ovog dijela bi izgledao ovako - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ”” ”;” ”; ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””))) - Funkcija TEXTJOIN sada jednostavno kombinira znakove niza to ostaje (koji su samo brojevi) i zanemaruje prazan niz.

Profesionalni savjet: Ako želite provjeriti izlaz dijela formule, odaberite ćeliju, pritisnite F2 za ulazak u način uređivanja, odaberite dio formule za koji želite ispis i pritisnite F9. Odmah ćete vidjeti rezultat. Zatim ne zaboravite pritisnuti Control + Z ili pritisnuti tipku Escape. NEMOJTE pritisnuti tipku enter.

Preuzmite datoteku primjera

Istu logiku možete koristiti i za izdvajanje tekstualnog dijela iz alfanumeričkog niza. Ispod je formula koja bi dobila tekstualni dio iz niza:

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

Manja promjena ove formule je ta što se IF funkcija koristi za provjeru je li niz koji dobivamo od MID funkcije pogrešan ili ne. Ako je riječ o pogrešci, zadržava vrijednost ili je zamjenjuje praznom.

Tada se TEXTJOIN koristi za kombiniranje svih znakova teksta.

Oprez: Iako ova formula odlično funkcionira, koristi hlapljivu funkciju (funkcija INDIRECT). To znači da u slučaju da ovo koristite s velikim skupom podataka, može proći neko vrijeme da se dobiju rezultati. Prije upotrebe ove formule u Excelu najbolje je stvoriti sigurnosnu kopiju.

Izdvajanje brojeva iz niza u Excelu (za Excel 2013/2010/2007)

Ako imate Excel 2013. 2010. ili 2007., ne možete koristiti formulu TEXTJOIN, pa ćete za to morati koristiti kompliciranu formulu.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izdvojiti sve brojeve u nizu u svakoj ćeliji.

Formula u nastavku će to učiniti:

= IF (SUM (LEN (A2) -LEN (ZAMJENA (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, SUMPRODUCT (MID (0 & A2, VELIKI (INDEKS (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), "")

U slučaju da u tekstualnom nizu nema broja, ova bi formula vratila prazno (prazan niz).

Iako je ovo formula niza, vi ne trebaju koristiti "Control-Shift-Enter" za ovu upotrebu. Jednostavan unos funkcionira za ovu formulu.

Zasluga za ovu formulu pripada nevjerojatnom forumu g. Excel.

Opet, ova formula će izdvojiti sve brojeve u nizu bez obzira na poziciju. Na primjer, ako je tekst “Cijena 10 ulaznica 200 USD”, dobit ćete 10200 kao rezultat.

Oprez: Iako ova formula odlično radi, koristi hlapljivu funkciju (funkcija INDIRECT). To znači da u slučaju da ovo koristite s velikim skupom podataka, može proći neko vrijeme da se dobiju rezultati. Prije upotrebe ove formule u Excelu najbolje je stvoriti sigurnosnu kopiju.

Odvojite tekst i brojeve u Excelu pomoću VBA

Ako često morate odvajati tekst i brojeve (ili izdvajati brojeve iz teksta), možete upotrijebiti i metodu VBA.

Sve što trebate učiniti je koristiti jednostavan VBA kôd za stvaranje prilagođene korisnički definirane funkcije (UDF) u Excelu, a zatim umjesto dugih i kompliciranih formula upotrijebite tu formulu VBA.

Dopustite mi da vam pokažem kako stvoriti dvije formule u VBA - jednu za izdvajanje brojeva i jednu za izdvajanje teksta iz niza.

Izdvajanje brojeva iz niza u Excelu (pomoću VBA)

U ovom dijelu ću vam pokazati kako stvoriti prilagođenu funkciju kako biste iz niza dobili samo numerički dio.

Ispod je VBA kôd koji ćemo koristiti za izradu ove prilagođene funkcije:

Funkcija 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) Next i GetNumeric = Krajnja funkcija rezultata

Evo koraka za stvaranje ove funkcije, a zatim je koristite na radnom listu:

Idite na karticu Developer.
Pritisnite Visual Basic (Možete koristiti i tipkovnički prečac ALT + F11)
U pozadini VB Editor koja se otvori desnom tipkom miša kliknite bilo koji objekt radne knjige.
Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula za radnu knjigu.
U prozoru koda modula kopirajte i zalijepite gore navedeni VBA kôd.
Zatvorite VB Editor.

Sada ćete moći koristiti funkciju GetText na radnom listu. Budući da smo izvršili sve teške poslove u samom kodu, sve što trebate učiniti je koristiti formulu = GetNumeric (A2).

Ovo će vam odmah dati samo numerički dio niza.

Imajte na umu da budući da radna knjiga sada ima VBA kôd, morate je spremiti s .xls ili .xlsm ekstenzijom.

Preuzmite datoteku primjera

U slučaju da često morate koristiti ovu formulu, možete je spremiti i u svoju osobnu makro radnu knjigu. To će vam omogućiti korištenje ove prilagođene formule u bilo kojoj radnoj knjizi programa Excel s kojom radite.

Izdvajanje teksta iz niza u Excelu (pomoću VBA)

U ovom dijelu ću vam pokazati kako stvoriti prilagođenu funkciju da biste dobili samo tekstualni dio iz niza.

Ispod je VBA kôd koji ćemo koristiti za izradu ove prilagođene funkcije:

Funkcija GetText (CellRef kao niz) 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 ) Sljedeće i GetText = Krajnja funkcija rezultata

Evo koraka za stvaranje ove funkcije, a zatim je koristite na radnom listu:

Idite na karticu Developer.
Pritisnite Visual Basic (Također možete koristiti tipkovnički prečac ALT + F11)
U pozadini VB Editor koja se otvori desnom tipkom miša kliknite bilo koji objekt radne knjige.
Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula za radnu knjigu.
- Ako već imate modul, dvaput kliknite na njega (nema potrebe za umetanjem novog ako ga već imate).
U prozoru koda modula kopirajte i zalijepite gore navedeni VBA kôd.
Zatvorite VB Editor.

Sada ćete moći koristiti funkciju GetNumeric na radnom listu. Budući da smo izvršili sve teške poslove u samom kodu, sve što trebate učiniti je koristiti formulu = GetText (A2).

Ovo će vam odmah dati samo numerički dio niza.