Excel VBA Split funkcija - objašnjeno s primjerima

Prilikom rada s VBA -om u Excelu možda ćete morati podijeliti niz na različite dijelove na temelju graničnika.

Na primjer, ako imate adresu, pomoću funkcije VBA Split možete dobiti različite dijelove adrese koji su odvojeni zarezom (što bi u ovom slučaju bilo razdjelnik).

SPLIT je ugrađena funkcija niza u programu Excel VBA koju možete koristiti za podjelu tekstualnog niza na temelju razdjelnika.

Excel VBA SPLIT funkcija - Sintaksa

Podijeli (izraz, [razdjelnik], [ograničenje], [usporedi])

• Izraz: Ovo je niz koji želite podijeliti na temelju graničnika. Na primjer, u slučaju primjera adrese, cijela adresa bila bi "izraz". U slučaju da se radi o nizu nulte duljine (“”), funkcija SPLIT vratila bi prazan niz.
• Razdjelnik: Ovo je izborni argument. Ovo je razdjelnik koji se koristi za podjelu argumenta "izraz". U našem primjeru adrese, zarez je graničnik koji se koristi za podjelu adrese na različite dijelove. Ako ne navedete ovaj argument, razmak se smatra zadanim razdjelnikom. U slučaju da navedete niz nulte duljine (""), funkcija vraća cijeli niz 'Expression'.
• Ograničiti: Ovo je izborni argument. Ovdje navodite ukupan broj podnizova koje želite vratiti. Na primjer, ako samo želite vratiti prve tri podniz iz argumenta ‘Expression’, to bi bilo 3. Ako ne navedete ovaj argument, zadana vrijednost je -1, koja vraća sve podnizove.
• Usporedi: Ovo je izborni argument. Ovdje navodite vrstu usporedbe koju želite da funkcija SPLIT izvodi pri procjeni podnizova. Dostupne su sljedeće opcije:
  • Kad je Usporedi 0: Ovo je binarna usporedba. To znači da ako je vaš razdjelnik tekstualni niz (recimo ABC), to će razlikovati velika i mala slova. "ABC" ne bi bio jednak "abc".
  • Kad je Usporedi 1: Ovo je usporedba teksta. To znači da ako je vaš razdjelnik tekstualni niz (recimo ABC), onda bi se čak i ako imate "abc" u nizu "izraz" smatrao razdjelnikom.

Sada kada smo pokrili osnove funkcije SPLIT, pogledajmo nekoliko praktičnih primjera.

Primjer 1 - Podijelite riječi u rečenici

Pretpostavimo da imam tekst - "Brza smeđa lisica preskače lijenog psa".

Mogu koristiti funkciju SPLIT da svaku riječ ove rečenice unesem kao zasebnu stavku u nizu.

Donji kôd bi ovo učinio:

Sub SplitWords () Dim TextStrng As String Dim Result () As String TextStrng = "Brza smeđa lisica preskače lijenog psa" Rezultat () = Split (TextStrng) End Sub

Iako kôd ne čini ništa korisno, pomoći će vam da shvatite što funkcija Split u VBA radi.

Split funkcija razdvaja tekstualni niz i dodjeljuje svaku riječ nizu Result.

Dakle u ovom slučaju:

• Rezultat (0) pohranjuje vrijednost "The"
• Rezultat (1) pohranjuje vrijednost "Brzo"
• Rezultat (2) pohranjuje vrijednost “Brown” i tako dalje.

U ovom primjeru naveli smo samo prvi argument - a to je tekst koji treba podijeliti. Budući da nije naveden razdjelnik, kao zadani razdjelnik uzima znak razmaka.

Važna nota:

1. VBA SPLIT funkcija vraća niz koji počinje od baze 0.
2. Kada je rezultat funkcije SPLIT dodijeljen nizu, taj niz mora biti deklariran kao vrsta podataka String. Ako ga deklarirate kao tip podataka Variant, prikazat će se pogreška neusklađenosti tipa). U gornjem primjeru imajte na umu da sam Result () deklarirao kao vrstu podataka String.

Primjer 2 - Prebrojite broj riječi u rečenici

Pomoću funkcije SPLIT možete dobiti ukupan broj riječi u rečenici. Trik ovdje je u prebrojavanju broja elemenata u nizu koji dobijete kada podijelite tekst.

Kod u nastavku prikazao bi okvir s porukom s brojem riječi:

Sub WordCount () Dim TextStrng As String Dim WordCount As Integer Dim Result () As String TextStrng = "Brza smeđa lisica preskače lijenog psa" Rezultat = Split (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox "The Broj riječi je "& WordCount End Sub

U ovom slučaju, funkcija UBound govori nam gornju granicu niza (tj. Najveći broj elemenata koje polje ima). Budući da je baza niza 0, dodaje se 1 kako bi se dobio ukupan broj riječi.

Sličan kod možete upotrijebiti za stvaranje prilagođene funkcije u VBA -i koja će uzeti tekst kao unos i vratiti broj riječi.

Kod u nastavku stvorit će ovu funkciju:

Funkcija WordCount (CellRef kao raspon) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Funkcija

Nakon stvaranja možete koristiti funkciju WordCount kao i bilo koju drugu redovitu funkciju.

Ova funkcija također upravlja početnim, završnim i dvostrukim razmacima između riječi. To je omogućeno upotrebom funkcije TRIM u VBA kodu.

U slučaju da želite saznati više o tome kako ova formula funkcionira za brojanje broja riječi u rečenici ili želite saznati o načinu formule koja nije VBA za dobivanje broja riječi, pogledajte ovaj vodič.

Primjer 3 - Upotreba razdjelnika osim razmaka

U prethodna dva primjera koristili smo samo jedan argument u funkciji SPLIT, a ostali su zadani argumenti.

Kad koristite neki drugi razdjelnik, morate to navesti u formuli SPLIT.

U donjem kodu funkcija SPLIT vraća niz na temelju zareza kao graničnik, a zatim prikazuje poruku sa svakom riječi u zasebnom retku.

Sub CommaSeparator () Dim TextStrng As String Dim Rezultat () As String Dim DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") Za i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub

U gornjem kodu koristio sam petlju For Next za prolazak kroz svaki element niza ‘Result’ dodijelivši ga varijabli ‘DisplayText’.

Primjer 4 - Podijelite adresu na tri dijela

Pomoću funkcije SPLIT možete odrediti koliko brojeva podjela želite dobiti. Na primjer, ako ništa ne navedem, svaka instanca graničnika će se koristiti za podjelu niza.

Ali ako navedem 3 kao granicu, tada će se niz podijeliti samo na tri dijela.

Na primjer, ako imam sljedeću adresu:

2703 Winifred Way, Indianapolis, Indiana, 46204

Mogu koristiti funkciju Split u VBA -i za podjelu ove adrese na tri dijela.

Ona dijeli prva dva na temelju graničnika zareza, a preostali dio postaje treći element niza.

Kod ispod prikazuje adresu u tri različita retka u okviru za poruku:

Sub CommaSeparator () Dim TextStrng As String Dim Rezultat () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Rezultat = Split (TextStrng, ",", 3) Za i = LBound (Rezultat ( )) U UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub

Jedna od praktičnih upotreba ovoga mogla bi biti kada želite podijeliti adresu s jednim retkom u format prikazan u okviru za poruku. Zatim možete stvoriti prilagođenu funkciju koja vraća adresu podijeljenu na tri dijela (sa svakim dijelom u novom retku).

Sljedeći kod bi to učinio:

Funkcija ThreePartAddress (cellRef As Range) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) Za i = LBound (Result ()) U UBound (Result ()) DisplayText = DisplayText & Trim (Rezultat (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) Krajnja funkcija

Nakon što imate ovaj kôd u modulu, možete koristiti funkciju (ThreePartAddress) u radnoj knjizi, kao i bilo koju drugu Excel funkciju.

Ova funkcija uzima jedan argument - referencu ćelije koja ima adresu.

Imajte na umu da za rezultirajuću adresu da se prikaže u tri različita retka, morate primijeniti format tekstualnog preloma na ćelije (nalazi se na kartici Početna u grupi Poravnanje). Ako format "Prelom teksta" nije omogućen, vidjet ćete cijelu adresu u jednom retku.

Primjer 5 - Dobijte naziv grada s adrese

Pomoću funkcije Split u VBA -u možete odrediti koji dio rezultirajućeg niza želite koristiti.

Na primjer, pretpostavimo da dijelim sljedeću adresu na temelju zareza kao graničnika:

2703 Winifred Way, Indianapolis, Indiana, 46204

Rezultirajući niz bi izgledao ovako kako je prikazano u nastavku:

{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}

Budući da je ovo niz, mogu odabrati prikaz ili vraćanje određenog dijela ovog niza.

Ispod je kôd za prilagođenu funkciju, gdje možete navesti broj koji će vratiti taj element iz niza. Na primjer, ako želim naziv stanja, mogu navesti 3 (jer je to treći element u nizu).

Funkcija ReturnNthElement (CellRef As Range, ElementNumber As Integer) Dim Result () As String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Funkcija

Gornja funkcija uzima dva argumenta, referencu ćelije koja ima adresu i broj elementa koji želite vratiti. Funkcija Split razdvaja elemente adrese i dodjeljuje ih varijabli Result.

Zatim vraća broj elementa koji ste naveli kao drugi argument. Imajte na umu da budući da je baza 0, ElementNumber-1 se koristi za vraćanje ispravnog dijela adrese.

Ova prilagođena formula najbolje odgovara kada imate dosljedan format u svim adresama - tj. Grad se uvijek spominje iza dvije zareze. Ako podaci nisu dosljedni, nećete postići željeni rezultat.

U slučaju da želite naziv grada, možete koristiti 2 kao drugi argument. U slučaju da koristite broj veći od ukupnog broja elemenata, vratio bi se #VALUE! pogreška.

Kôd možete dodatno pojednostaviti kao što je prikazano u nastavku:

Funkcija ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) Kraj funkcija

U gornjem kodu, umjesto korištenja varijable Result, vraća samo navedeni broj elementa.

Dakle, ako imate Split ("Dobro jutro") (0), vratio bi se samo prvi element, koji je "Dobro".

Slično, u gornjem kodu vraća samo navedeni broj elementa.