Utilizarea funcției VBA Split
Funcția VBA Split vă permite să separați părțile componente dintr-un șir de text standard în care fiecare componentă utilizează un caracter specific delimitator, de ex. o virgulă sau un colon. Este mai ușor de utilizat decât scrierea codului pentru a căuta delimitatorii în șir și apoi pentru a extrage valorile.
Poate fi folosit dacă citiți într-o linie dintr-o valoare separată prin virgulă (fișier CSV) sau dacă aveți o adresă poștală care se află pe o singură linie, dar doriți să o vedeți ca mai multe linii.
Sintaxa este:
| 1 | Expresie divizată, delimitator [opțional], limită [opțional], comparare [opțional] |
Funcția VBA Split are patru parametri:
- Expresie - Șirul de text pe care doriți să îl împărțiți în diferite părți.
- Delimitator (opțional)- șir sau caracter neprimabil - Definește caracterul delimitator care va fi folosit pentru împărțire. Dacă nu este furnizat niciun caracter delimitator, atunci se folosește implicit un spațiu.
- Limită (opțional) - număr - Definește câte împărțiri vor fi făcute. Dacă este necompletat, toate divizările disponibile vor fi făcute în cadrul șirului. Dacă este setat la 1, atunci nu se vor face divizări. Practic, vă permite să separați un anumit număr de valori începând de la începutul șirului, de ex. unde șirul este foarte lung și aveți nevoie doar de primele trei despărțiri.
- Comparaţie (opțional) - Dacă delimitatorul dvs. este un caracter text, acesta este utilizat pentru a comuta dacă delimitatorul este sensibil la majuscule sau nu. Valorile sunt vbBinaryCompare (case-senstiive) și vbTextCompare (not case-sensitive).
Funcția de divizare returnează întotdeauna o matrice.
Exemplu simplu de funcție Split
| 123456789101112 | Sub SplitExample ()„Definiți variabileDim MyArray () Ca șir, MyString ca șir, eu ca variantă„Șir de eșantion cu delimitatori de spațiuMyString = "One Two Three Four"'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString)'iterați prin matricea creată pentru a afișa fiecare valoarePentru fiecare I din MyArrayMsgBox I.Apoi euSfârșitul Sub |
În acest exemplu, nu este specificat niciun delimitator, deoarece toate cuvintele au un spațiu între ele, deci poate fi utilizat delimitatorul implicit (spațiu).
Matricea nu are dimensiuni și este setată ca un șir. Variabila I, care este utilizată în bucla Pentru … Următorul trebuie dimensionată ca o variantă.
Când se execută acest cod, acesta va afișa patru casete de mesaj, câte una pentru fiecare dintre divizări, de ex. Unu doi trei. Patru.
Rețineți că dacă există un spațiu dublu între cuvintele din șir, acesta va fi evaluat ca o împărțire, deși fără nimic în el. Este posibil să nu fie rezultatul pe care doriți să-l vedeți.
Puteți remedia această problemă utilizând funcția Înlocuire pentru a înlocui orice spații duble cu un singur spațiu:
| 1 | MyString = Înlocuiți (MyString, "", "") |
Un spațiu de urmărire sau de conducere poate provoca, de asemenea, probleme prin producerea unei împărțiri goale. Acestea sunt adesea foarte greu de văzut. Puteți elimina aceste spații străine utilizând funcția Trim:
| 1 | MyString = Trim (MyString) |
Utilizarea funcției Split cu un caracter Delimiter
Putem folosi un delimitator de punct și virgulă (;). Acest lucru se găsește frecvent în șirurile de adrese de e-mail pentru a separa adresele. Este posibil să vi se trimită un e-mail care să fie distribuit mai multor colegi și doriți să vedeți o listă în foaia de lucru cu cine a ajuns. Puteți copia cu ușurință adresele de e-mail din casetele de e-mail „Către” sau „Copiere” și în codul dvs.
| 123456789101112131415 | Sub SplitBySemicolonExample ()„Definiți variabileDim MyArray () Ca șir, MyString ca șir, I Ca variantă, N Ca întreg'Șir de probă cu delimitatori de punct și virgulăMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString, ";")„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'itera prin matricePentru N = 0 la UBound (MyArray)„Plasați fiecare adresă de e-mail în prima coloană a foii de lucruGama ("A" & N + 1). Valoare = MyArray (N)Următorul NSfârșitul Sub |
Rețineți că o buclă For … Next este utilizată pentru a itera prin matrice. Primul element din matrice începe întotdeauna de la zero, iar funcția Upper Bound este utilizată pentru a obține numărul maxim de elemente.
După rularea acestui cod, foaia dvs. de lucru va arăta astfel:
Utilizarea unui parametru de limită într-o funcție de împărțire
Parametrul limită permite efectuarea unui număr specific de divizări de la începutul șirului. Din păcate, nu puteți oferi o poziție de start sau o serie de împărțiri de făcut, deci este destul de simplu. Puteți crea propriul cod VBA pentru a crea o funcție pentru a face acest lucru, iar acest lucru va fi explicat mai târziu în acest articol.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, I Ca variantă, N Ca întreg'Șir de eșantion cu delimitatori de virgulăMyString = "Unu, Doi, Trei, Patru, Cinci, Șase"'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString, ",", 4)„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'Iterează prin matricePentru N = 0 la UBound (MyArray)'Plasați fiecare împărțire în prima coloană a foii de lucruGama ("A" & N + 1). Valoare = MyArray (N)Următorul NSfârșitul Sub |
După ce rulați acest cod, foaia dvs. de lucru va arăta astfel:
Doar primele trei valori divizate sunt afișate separat. Cele trei valori ulterioare sunt afișate ca un șir lung și nu se împart.
Dacă alegeți o valoare limită mai mare decât numărul de delimitatori dintr-un șir, aceasta nu va produce o eroare. Șirul va fi împărțit în toate părțile sale componente ca și cum nu ar fi fost furnizată valoarea limită.
Utilizarea parametrului de comparare într-o funcție de împărțire
Parametrul Compare determină dacă delimitatorul este sensibil la majuscule sau nu. Acest lucru nu se aplică dacă delimitatorii sunt virgule, punct și virgulă sau puncte.
Notă: în schimb, puteți plasa oricând Opțiunea Comparare text <> în partea de sus a modulului pentru a elimina sensibilitatea la majuscule și minuscule pentru întregul modul.
| 123456789101112131415 | Sub SplitByCompareExample ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, I Ca variantă, N Ca întreg'Șir de probă cu X delimitatoriMyString = "OneXTwoXThreexFourXFivexSix"'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString, "X",, vbBinaryCompare)„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'itera prin matricePentru N = 0 la UBound (MyArray)'Plasați fiecare împărțire în prima coloană a foii de lucruGama ("A" & N + 1). Valoare = MyArray (N)Următorul NSfârșitul Sub |
În acest exemplu, șirul care trebuie divizat utilizează caracterul „X” ca delimitator. Cu toate acestea, în acest șir, există un amestec de caractere majuscule și minuscule „X”. Parametrul Compare din funcția Split folosește un caracter „X” cu majuscule.
Dacă parametrul Compare este setat la vbBinaryCompare, atunci caracterele minuscule „x” vor fi ignorate, iar foaia dvs. de lucru va arăta astfel:
Dacă parametrul Comparare este setat la vbTextCompare, atunci caracterele minuscule „x” vor fi folosite în împărțire și foaia dvs. de lucru va arăta astfel:
Rețineți că valoarea din celula A6 este trunchiată, deoarece conține un caracter minuscul „x”. Deoarece împărțirea nu este sensibilă la majuscule și minuscule, orice delimitator care face parte dintr-un șir secundar va provoca o împărțire.
Acesta este un punct important care trebuie avut în vedere atunci când utilizați un delimitator de text și vbTextCompare. Puteți ajunge cu ușurință la un rezultat greșit.
Utilizarea caracterelor neprimabile ca caracter delimitator
Puteți utiliza caractere care nu se pot imprima ca delimitator, cum ar fi o întoarcere de car (o întrerupere de linie).
Aici folosim vbCr pentru a specifica un return de transport <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, I Ca variantă, N Ca întreg'Șir de eșantion cu delimitatori de retur de transportMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString, vbCr,, vbTextCompare)„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'Iterează prin matricePentru N = 0 la UBound (MyArray)'Plasați fiecare împărțire în prima coloană a foii de lucruGama ("A" & N + 1). Valoare = MyArray (N)Următorul NSfârșitul Sub |
În acest exemplu, un șir este construit folosind vbCr (caracterul de returnare a transportului) ca delimitator.
Când se execută acest cod, foaia dvs. de lucru va arăta astfel:
Utilizarea funcției Unire pentru a inversa o împărțire
Funcția Alăturați va re-alătura toate elementele unui tablou, dar folosind un delimitator specificat. Dacă nu este specificat niciun caracter delimitator, atunci va fi folosit un spațiu.
| 123456789101112131415 | Sub JoinExample ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, I Ca variantă, N Ca întregReduceți ținta ca șir'Șir de eșantion cu delimitatori de virgulăMyString = "Unu, Doi, Trei, Patru, Cinci, Șase"„Plasați MyString în celula A1Gama („A1”). Valoare = MyString'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString, ",")'Utilizați funcția Join pentru a recrea șirul original folosind un delimitator de punct și virgulăȚintă = Alăturați-vă (MyArray, ”;”)'Plasați șirul de rezultate la celula A2Interval („A2”). Valoare = țintăSfârșitul Sub |
Acest cod împarte un șir cu delimitatoare de virgule într-o matrice și îl unește din nou folosind delimitatori de punct și virgulă.
După rularea acestui cod, foaia dvs. de lucru va arăta astfel:
Celula A1 are șirul original cu delimitatori de virgulă, iar celula A2 are noul șir îmbinat cu delimitatori de punct și virgulă.
Utilizarea funcției Split pentru a face un număr de cuvinte
Având în vedere că o variabilă de șir în Excel VBA poate avea o lungime de până la 2 GB, puteți utiliza funcția de împărțire pentru a face numărul de cuvinte într-o bucată de text. Evident, Microsoft Word o face automat, dar acest lucru ar putea fi util pentru un fișier text simplu sau text copiat dintr-o altă aplicație.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Creați variabileDim MyArray () As String, MyString As String„Șir de eșantion cu delimitatori de spațiuMyString = "One Two Three Four Five Six"'Eliminați orice spații dubleMyString = Înlocuiți (MyString, "", "")'Eliminați orice spațiu de conducere sau de spateMyString = Trim (MyString)'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString)'Afișați numărul de cuvinte utilizând funcția UBoundMsgBox „Număr de cuvinte” & UBound (MyArray) + 1Sfârșitul Sub |
Unul dintre pericolele acestui cod de numărare a cuvintelor este că acesta va fi aruncat de spații duble și spații de conducere și spate. Dacă acestea sunt prezente, vor fi numărate ca cuvinte în plus, iar numărul de cuvinte va ajunge ca inexact.
Codul folosește funcțiile Înlocuire și tăiere pentru a elimina aceste spații suplimentare.
Linia de cod finală afișează numărul de cuvinte găsite utilizând funcția UBound pentru a obține numărul maxim de element al matricei și apoi incrementând-o cu 1. Acest lucru se datorează faptului că primul element matrice începe de la zero.
Împărțirea unei adrese în celule ale foii de lucru
Adresele de e-mail sunt deseori șiruri lungi de text cu delimitatori de virgulă. Poate doriți să împărțiți fiecare parte a adresei într-o celulă separată.
| 123456789101112131415 | Exemplu de adresă secundară ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, N Ca întreg'Configurați șirul cu adresa Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 SUA"'Utilizați funcția de împărțire pentru a împărți șirul folosind un delimitator de virguleMyArray = Split (MyString, ",")„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'itera prin matricePentru N = 0 la UBound (MyArray)'Plasați fiecare împărțire în prima coloană a foii de lucruGama ("A" & N + 1). Valoare = MyArray (N)Următorul NSfârșitul Sub |
Rularea acestui cod va folosi delimitatorul de virgule pentru a pune fiecare linie a adresei într-o celulă separată:
Dacă doriți doar să returnați codul poștal (ultimul element de matrice), atunci puteți utiliza codul:
| 123456789101112 | SubAdresaZipCodeExample ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, N Ca întreg, Temp Ca șir„Configurați șirul cu adresa Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 SUA"'Utilizați funcția de împărțire pentru a împărți șirul folosind un delimitator de virguleMyArray = Split (MyString, ",")„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'Puneți codul poștal la celula A1Gama ("A1"). Valoare = MyArray (UBound (MyArray))Sfârșitul Sub |
Aceasta va utiliza doar ultimul element din matrice, care se găsește utilizând funcția UBound.
Pe de altă parte, poate doriți să vedeți toate liniile dintr-o singură celulă, astfel încât să poată fi imprimate pe o etichetă de adresă:
| 1234567891011121314151617 | Exemplu de adresă secundară ()'Creați variabileDim MyArray () Ca șir, MyString ca șir, N Ca întreg, Temp Ca șir„Configurați șirul cu adresa Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 SUA"'Utilizați funcția de împărțire pentru a împărți șirul folosind un delimitator de virguleMyArray = Split (MyString, ",")„Ștergeți foaia de lucruActiveSheet.UsedRange.Clear'itera prin matricePentru N = 0 la UBound (MyArray)'plasează fiecare element de matrice plus un caracter de linie într-un șirTemp = Temp & MyArray (N) & vbLfUrmătorul N'Puneți șirul pe foaia de lucruGama ("A1") = TempSfârșitul Sub |
Acest exemplu funcționează la fel ca cel anterior, cu excepția faptului că creează un șir temporar al tuturor elementelor matrice, dar inserând un caracter de alimentare de linie după fiecare element.
Foaia de lucru va arăta astfel după ce codul a fost rulat:
Împarte șirul în celulele foii de lucru
Puteți copia matricea Împărțiți în celulele foii de lucru <> cu o singură comandă:
| 12345678910 | Sub CopyToRange ()'Creați variabileDim MyArray () As String, MyString As String„Șir de eșantion cu delimitatori de spațiuMyString = "Unu, Doi, Trei, Patru, Cinci, Șase"'Utilizați funcția Split pentru a împărți părțile componente ale șiruluiMyArray = Split (MyString, ",")'Copiați matricea în foaia de lucruRange ("A1: A" & UBound (MyArray) + 1). Valoare = WorksheetFunction.Transpose (MyArray)Sfârșitul Sub |
După executarea acestui cod, foaia dvs. de lucru va arăta astfel:
Crearea unei noi funcții pentru a permite împărțirea dintr-un punct dat
Parametrul Limit din funcția Split vă permite doar să specificați o limită superioară unde doriți să se oprească divizarea. Începe întotdeauna de la începutul șirului.
Ar fi foarte util să aveți o funcție similară în care puteți specifica punctul de pornire al împărțirii în cadrul șirului și numărul de despărțiri pe care doriți să le vedeți de la acel punct încoace. De asemenea, va extrage doar divizările pe care le-ați specificat în matrice, mai degrabă decât să aveți o valoare de șir enormă ca ultim element din matrice.
Puteți construi cu ușurință o funcție (numită SplitSlicer) în VBA pentru a face acest lucru:
| 123456789101112131415161718192021222324 | Funcție SplitSlicer (țintă ca șir, ștergere ca șir, pornire ca întreg, N ca întreg)'Creați o variabilă matriceDim MyArray () Ca șir'Capturați împărțirea folosind variabila de pornire folosind caracterul delimitatorMyArray = Split (Target, Del, Start)‘Verificați dacă parametrul de pornire este mai mare decât numărul de divizări - acest lucru poate cauza problemeDacă Start> UBound (MyArray) + 1 Apoi‘Afișați eroarea și ieșiți din funcțieMsgBox "Parametrul de pornire este mai mare decât numărul de divizări disponibile"SplitSlicer = MyArrayFuncția de ieșireEnd If'Puneți ultimul element matrice în șirȚintă = MyArray (UBound (MyArray))'Împarte șirul folosind N ca limităMyArray = Split (Țintă, Del, N)‘Verificați dacă limita superioară este mai mare decât zero, deoarece codul elimină ultimul elementDacă UBound (MyArray)> 0 Atunci'Utilizați ReDim pentru a elimina elementul final al matriceiReDim Preserve MyArray (UBound (MyArray) - 1)End If'Returnează noua matriceSplitSlicer = MyArrayFuncția de sfârșit |
Această funcție este construită cu patru parametri:
- Ţintă - șir - acesta este șirul de intrare pe care doriți să îl împărțiți
- Del - șir sau caracter neprimabil - acesta este caracterul delimitator pe care îl utilizați de ex. virgula, colon
- start - număr - aceasta este divizarea de început pentru felia ta
- N - număr - acesta este numărul de împărțiri pe care doriți să le faceți în felie
Niciunul dintre acești parametri nu este opțional sau nu are valori implicite, dar îl puteți introduce în codul funcției dacă doriți să îl extindeți în continuare.
Funcția utilizează funcția Split pentru a crea o matrice folosind parametrul Start ca Limită. Aceasta înseamnă că elementele matricei vor menține împărțirile până la parametrul de pornire, dar restul șirului va fi ultimul element și nu va fi împărțit.
Ultimul element din matrice este transferat înapoi la un șir folosind funcția UBound pentru a determina ce element este acesta.
Șirul este apoi împărțit din nou în matrice, folosind N ca variabilă limită. Aceasta înseamnă că împărțirile se vor face pentru șirul până la poziția N, după care restul șirului va forma ultimul element din matrice.
Instrucțiunea ReDim este utilizată pentru a elimina ultimul element deoarece vrem doar elementele specifice rămase în matrice. Rețineți că este utilizat parametrul Conservare, altfel toate datele din matrice se vor pierde.
Noua matrice este apoi returnată la codul de la care a fost apelat.
Rețineți că codul este „rezistent la erori”. Utilizatorii vor face adesea lucruri ciudate pe care nu le-ați luat în considerare. De exemplu, dacă încearcă să utilizeze funcția cu parametrul Start sau N mai mare decât numărul disponibil de despărțiri din șir, acest lucru va provoca eșecul funcției.
Codul este inclus pentru a verifica valoarea Start și, de asemenea, pentru a vă asigura că există un element care poate fi eliminat atunci când instrucțiunea ReDim este utilizată pe matrice.
Iată codul pentru a testa funcția:
| 123456789101112 | Sub TestSplitSlicer ()'Creați variabileDim MyArray () As String, MyString As String'Definiți șirul de eșantion cu delimitatori de virgulăMyString = "Unu, Doi, Trei, Patru, Cinci, Șase, Șapte, Opt, Nouă, Zece"'Utilizați funcția Splitslicer pentru a defini o nouă matriceMyArray = SplitSlicer (MyString, ",", 4, 3)'Ștergeți foaia activăActiveSheet.UsedRange.Clear'Copiați matricea în foaia de lucruRange ("A1: A" & UBound (MyArray) + 1). Valoare = WorksheetFunction.Transpose (MyArray)Sfârșitul Sub |
Rulați acest cod și foaia dvs. de lucru va arăta astfel:
