Excel VBA Split Funkcija - pojasnjeno s primeri

Pri delu z VBA v Excelu boste morda morali razdeliti niz na različne dele na podlagi ločila.

Na primer, če imate naslov, lahko s funkcijo VBA Split dobite različne dele naslova, ki so ločeni z vejico (kar bi bilo v tem primeru ločilo).

SPLIT je vgrajena funkcija niza v Excelu VBA, ki jo lahko uporabite za razdelitev besedilnega niza na podlagi razmejevalnika.

Excel VBA SPLIT funkcija - Sintaksa

Razdeljeno (izraz, [ločilo], [omejitev], [primerjaj])

• Izraz: To je niz, ki ga želite razdeliti na podlagi ločila. Na primer, v primeru naslova bi bil celoten naslov "izraz". Če gre za niz ničelne dolžine (“”), funkcija SPLIT vrne prazno polje.
• Ločilo: To je neobvezen argument. To je ločilo, ki se uporablja za razdelitev argumenta "izraz". V našem primeru naslova je vejica ločilo, ki se uporablja za razdelitev naslova na različne dele. Če tega argumenta ne podate, se presledek šteje za privzeti ločilo. Če podate niz ničelne dolžine (""), funkcija vrne celoten niz "Expression".
• Omejitev: To je neobvezen argument. Tu določite skupno število podnizov, ki jih želite vrniti. Če na primer želite vrniti samo prve tri podniz iz argumenta 'Expression', bi to bilo 3. Če tega argumenta ne podate, je privzeto -1, ki vrne vse podniz.
• Primerjaj: To je neobvezen argument. Tu določite vrsto primerjave, ki jo želite izvesti s funkcijo SPLIT pri ocenjevanju podnizov. Na voljo so naslednje možnosti:
  • Ko je Primerjaj 0: To je binarna primerjava. To pomeni, da če je vaš razmejevalnik besedilni niz (recimo ABC), bo to odvisno od velikih in malih črk. "ABC" ne bi bilo enako "abc".
  • Ko je Primerjaj 1: To je primerjava besedila. To pomeni, da če je vaš razmejevalnik besedilni niz (recimo ABC), potem bi se tudi, če imate v nizu »izraz« »abc«, obravnaval kot ločilo.

Zdaj, ko smo obravnavali osnove funkcije SPLIT, poglejmo nekaj praktičnih primerov.

Primer 1 - Razdelite besede v stavku

Recimo, da imam besedilo - "Hitra rjava lisica skoči čez lenobnega psa".

S funkcijo SPLIT lahko vsako besedo tega stavka spremenim v ločeno postavko v matriki.

Spodnja koda bi to naredila:

Sub SplitWords () Dim TextStrng As String Dim Result () As String TextStrng = "Hitra rjava lisica skoči čez lenega psa" Rezultat () = Razdeli (TextStrng) End Sub

Čeprav koda ne naredi nič koristnega, vam bo pomagala razumeti, kaj počne funkcija Split v VBA.

Funkcija Split razdeli besedilni niz in vsako besedo dodeli matriki Result.

Torej v tem primeru:

• Rezultat (0) shrani vrednost »The«
• Rezultat (1) shrani vrednost »Hitro«
• Rezultat (2) shrani vrednost »Brown« itd.

V tem primeru smo podali le prvi argument - to je besedilo, ki ga je treba razdeliti. Ker omejevalnik ni določen, je za privzeti razmejevalnik potreben presledek.

Pomembna opomba:

1. Funkcija VBA SPLIT vrne matriko, ki se začne od osnove 0.
2. Ko je rezultat funkcije SPLIT dodeljen matriki, mora biti ta matrika deklarirana kot podatkovni tip String. Če ga deklarirate kot podatkovni tip Variant, se prikaže napaka pri neusklajenosti vrst). V zgornjem primeru upoštevajte, da sem Result () razglasil za podatkovni tip String.

Primer 2 - Preštejte število besed v stavku

S funkcijo SPLIT lahko dobite skupno število besed v stavku. Trik tukaj je šteti število elementov v matriki, ki jih dobite, ko razdelite besedilo.

Spodnja koda bi prikazala polje s sporočilom s številom besed:

Sub WordCount () Dim TextStrng As String Dim WordCount As Integer Dim Result () As String TextStrng = "Hitra rjava lisica skoči čez lenega psa" Rezultat = Split (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox " Število besed je "& WordCount End Sub

V tem primeru nam funkcija UBound pove zgornjo mejo matrike (tj. Največje število elementov, ki jih ima matrika). Ker je osnova matrike 0, se doda 1, da dobimo skupno število besed.

Podobno kodo lahko uporabite za ustvarjanje funkcije po meri v VBA, ki bo vzela besedilo kot vnos in vrnila število besed.

Spodnja koda bo ustvarila to funkcijo:

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

Ko ustvarite, lahko uporabite funkcijo WordCount tako kot katero koli drugo običajno funkcijo.

Ta funkcija obravnava tudi začetne, zadnje in dvojne presledke med besedami. To je bilo mogoče z uporabo funkcije TRIM v kodi VBA.

Če želite izvedeti več o tem, kako ta formula deluje za štetje števila besed v stavku, ali če želite izvedeti o načinu formule, ki ni VBA, da dobite število besed, si oglejte to vadnico.

Primer 3 - Uporaba ločevalnika, ki ni vesoljski znak

V prejšnjih dveh primerih smo v funkciji SPLIT uporabili le en argument, ostali pa so bili privzeti argumenti.

Ko uporabljate kakšen drug razmejevalnik, morate to navesti v formuli SPLIT.

V spodnji kodi funkcija SPLIT vrne matriko na osnovi vejice kot ločilo in nato prikaže sporočilo z vsako besedo v ločeni vrstici.

Sub CommaSeparator () Dim TextStrng As String Dim Result () 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

V zgornji kodi sem uporabil zanko For Next, da sem šel skozi vsak element matrike »Result« in ga dodelil spremenljivki »DisplayText«.

Primer 4 - Razdelite naslov na tri dele

S funkcijo SPLIT lahko določite, koliko številk razdelkov želite dobiti. Če na primer nič ne navedem, bi se za razdelitev niza uporabil vsak primerek razmejevalnika.

Če pa za mejo navedem 3, bo niz razdeljen samo na tri dele.

Na primer, če imam naslednji naslov:

2703 Winifred Way, Indianapolis, Indiana, 46204

S funkcijo Split v VBA lahko ta naslov razdelim na tri dele.

Prva dva razdeli na podlagi ločila vejice, preostali del pa postane tretji element matrike.

Spodnja koda bi prikazala naslov v treh različnih vrsticah v polju za sporočila:

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

Ena od praktičnih uporab tega je lahko, če želite naslov ene vrstice razdeliti v obliko, prikazano v polju za sporočila. Nato lahko ustvarite funkcijo po meri, ki vrne naslov, razdeljen na tri dele (vsak del v novi vrstici).

Naslednja koda bi to naredila:

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 ()) V UBound (Result ()) DisplayText = DisplayText & Trim (Result (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) Končna funkcija

Ko imate to kodo v modulu, lahko uporabite funkcijo (ThreePartAddress) v delovnem zvezku tako kot katero koli drugo funkcijo Excel.

Ta funkcija ima en argument - sklic na celico, ki ima naslov.

Upoštevajte, da morate za nastali naslov prikazati v treh različnih vrsticah, za celice uporabite obliko besedila za prelom (to je na zavihku Domov v skupini Poravnava). Če oblika »Prelomi besedilo« ni omogočena, bo celoten naslov prikazan v eni sami vrstici.

Primer 5 - Pridobite ime mesta iz naslova

S funkcijo Split v VBA lahko določite, kateri del nastale matrike želite uporabiti.

Predpostavimo, na primer, da ločim naslednji naslov na podlagi vejice kot ločila:

2703 Winifred Way, Indianapolis, Indiana, 46204

Nastala matrika bi izgledala nekako tako, kot je prikazano spodaj:

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

Ker je to matrika, se lahko odločim za prikaz ali vrnitev določenega dela te matrike.

Spodaj je koda za funkcijo po meri, kjer lahko podate številko in ta element bo vrnil iz matrike. Na primer, če želim ime stanja, lahko podam 3 (saj je to tretji element v matriki).

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

Zgornja funkcija ima dva argumenta, sklic na celico, ki ima naslov, in številko elementa, ki ga želite vrniti. Funkcija Split razdeli naslovne elemente in jih dodeli spremenljivki Result.

Nato vrne številko elementa, ki ste jo podali kot drugi argument. Upoštevajte, da ker je osnova 0, se ElementNumber-1 uporablja za vrnitev pravilnega dela naslova.

Ta formula po meri je najbolj primerna, če imate v vsem naslovu dosledno obliko - to pomeni, da je mesto vedno omenjeno za dvema vejicama. Če podatki niso skladni, ne boste dobili želenega rezultata.

Če želite ime mesta, lahko uporabite 2 kot drugi argument. Če uporabite število, ki je večje od skupnega števila elementov, bi vrnilo #VALUE! napaka.

Kodo lahko dodatno poenostavite, kot je prikazano spodaj:

Funkcija ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Razdeli (CellRef, ",") (ElementNumber - 1) Končna funkcija

V zgornji kodi namesto spremenljivke Result vrne le podano številko elementa.

Če imate torej Split ("Dobro jutro") (0), bi vrnil le prvi element, ki je "Dobro".

Podobno v zgornji kodi vrne le podano številko elementa.