Brug af VBA -splitfunktionen
VBA Split -funktionen giver dig mulighed for at adskille komponentdelene fra en standard tekststreng, hvor hver komponent bruger et specifikt skilletegn f.eks. et komma eller et tyktarm. Det er lettere at bruge end at skrive kode til at søge efter afgrænserne i strengen og derefter udtrække værdierne.
Det kan bruges, hvis du læser i en linje fra en kommasepareret værdi (CSV-fil), eller hvis du har en postadresse, der er på en linje, men du vil se det som flere linjer.
Syntaksen er:
| 1 | Opdel udtryk, afgrænser [valgfrit], grænse [valgfrit], sammenlign [valgfrit] |
VBA Split -funktionen har fire parametre:
- Udtryk - Tekststrengen, som du ønsker at opdele i forskellige dele.
- Afgrænser (valgfri)- streng eller tegn, der ikke kan udskrives - Definerer det afgrænsningstegn, der skal bruges til opdelingen. Hvis der ikke er angivet et afgrænsningstegn, bruges standardværdien for et mellemrum.
- Begrænse (valgfri) - nummer - Definerer, hvor mange opdelinger der skal foretages. Hvis det er tomt, foretages alle tilgængelige opdelinger inden for strengen. Hvis den er indstillet til 1, foretages der ingen opdelinger. Grundlæggende giver det dig mulighed for at adskille et bestemt antal værdier, der starter ved begyndelsen af strengen, f.eks. hvor strengen er meget lang, og du kun har brug for de første tre split.
- Sammenligne (valgfri) - Hvis din afgrænsning er et teksttegn, bruges dette til at skifte, om afgrænsningen er store og små bogstaver. Værdierne er vbBinaryCompare (case-senstiive) og vbTextCompare (ikke store og små bogstaver).
Opdelingsfunktionen returnerer altid en matrix.
Enkelt eksempel på splitfunktionen
| 123456789101112 | Sub SplitExample ()'Definer variablerDim MyArray () Som streng, MyString som streng, I Som variant'Prøve streng med mellemrumMyString = "One Two Three Four"'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString)'gentag gennem det array, der er oprettet for at vise hver værdiFor hver I In MyArrayMsgBox INæste jegAfslut Sub |
I dette eksempel er der ikke angivet en afgrænsning, fordi alle ordene har et mellemrum mellem dem, så standardafgrænsningen (mellemrum) kan bruges.
Arrayen har ingen dimensioner og er angivet som en streng. Variablen I, der bruges i For… Next loop skal dimensioneres som en variant.
Når denne kode køres, viser den fire meddelelsesbokse, en for hver af opdelingerne f.eks. Et to tre. Fire.
Bemærk, at hvis der er et dobbelt mellemrum mellem ordene i strengen, vil dette blive vurderet som en opdeling, selvom der ikke er noget i det. Dette er muligvis ikke det resultat, du vil se.
Du kan løse dette problem ved at bruge funktionen Erstat til at erstatte dobbeltrum med et enkelt mellemrum:
| 1 | MyString = Erstat (MyString, "", "") |
Et efterfølgende eller ledende rum kan også forårsage problemer ved at producere en tom split. Disse er ofte meget svære at se. Du kan fjerne disse fremmede mellemrum ved hjælp af funktionen Trim:
| 1 | MyString = Trim (MyString) |
Brug af splitfunktionen med et afgrænsningstegn
Vi kan bruge en afgrænser for et semikolon (;). Dette findes ofte i e -mailadressestrenge for at adskille adresserne. Du har muligvis en e -mail sendt til dig, som deles med et antal kolleger, og du vil se en liste i dit regneark over, hvem den er gået til. Du kan nemt kopiere e -mail -adresserne fra boksen "Til" eller "Kopi" til din kode.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definer variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Prøve streng med semi kolon afgrænsereMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString, ";")'Ryd regnearketActiveSheet.UsedRange.Clear'gentag gennem arrayetFor N = 0 Til UBound (MyArray)'Placer hver e -mail -adresse i den første kolonne i regnearketOmråde ("A" og N + 1) .Værdi = MyArray (N)Næste NAfslut Sub |
Bemærk, at en For … Næste loop bruges til at iterere gennem arrayet. Det første element i arrayet starter altid på nul, og funktionen Upper Bound bruges til at få det maksimale antal elementer.
Efter at have kørt denne kode vil dit regneark se sådan ud:
Brug af en grænseparameter i en splitfunktion
Limitparameteren gør det muligt at foretage et specifikt antal spaltninger fra strengens start. Desværre kan du ikke give en startposition eller en række opdelinger, der skal udføres, så det er temmelig grundlæggende. Du kan opbygge din egen VBA -kode for at oprette en funktion til at gøre dette, og dette vil blive forklaret senere i denne artikel.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Opret variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Prøve streng med kommaafgrænsereMyString = "En, to, tre, fire, fem, seks"'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString, ",", 4)'Ryd regnearketActiveSheet.UsedRange.Clear'Iterere gennem arrayetFor N = 0 Til UBound (MyArray)'Placer hver split i den første kolonne i regnearketOmråde ("A" og N + 1) .Værdi = MyArray (N)Næste NAfslut Sub |
Når du har kørt denne kode, ser dit regneark sådan ud:
Kun de første tre splitværdier vises separat. De senere tre værdier vises som en lang streng og bliver ikke delt.
Hvis du vælger en grænseværdi, der er større end antallet af afgrænsere inden for en streng, vil dette ikke give en fejl. Strengen opdeles i alle dens komponentdele, som om grænseværdien ikke var angivet.
Brug af sammenligningsparameteren i en splitfunktion
Parameteren Sammenlign bestemmer, om afgrænsningen er store og små bogstaver. Dette gælder ikke, hvis afgrænserne er kommaer, semikolon eller kolon.
Bemærk: I stedet kan du altid placere Option Compare Text <> øverst i dit modul for at fjerne store og små bogstaver over hele modulet.
| 123456789101112131415 | Sub SplitByCompareExample ()'Opret variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Prøve streng med X afgrænsningerMyString = "OneXTwoXThreexFourXFivexSix"'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Split (MyString, "X",, vbBinaryCompare)'Ryd regnearketActiveSheet.UsedRange.Clear'gentag gennem arrayetFor N = 0 Til UBound (MyArray)'Placer hver split i den første kolonne i regnearketOmråde ("A" og N + 1) .Værdi = MyArray (N)Næste NAfslut Sub |
I dette eksempel bruger strengen, der skal opdeles, tegnet ‘X’ som afgrænsning. I denne streng er der imidlertid en blanding af store og små X -tegn. Parameteren Sammenlign i Split -funktionen bruger et stort X -tegn.
Hvis parameteren Sammenlign er sat til vbBinaryCompare, ignoreres de små x -tegn, og dit regneark vil se sådan ud:
Hvis parameteren Sammenlign er indstillet til vbTextCompare, bruges 'x' -tegnene med små bogstaver i opdelingen, og dit regneark vil se sådan ud:
Bemærk, at værdien i celle A6 er afkortet, fordi den indeholder et lille x -tegn. Fordi opdelingen ikke er store og små bogstaver, vil enhver afgrænser, der er en del af en understreng, få en opdeling til at ske.
Dette er et vigtigt punkt at huske på, når du bruger en tekstafgrænser og vbTextCompare. Du kan let ende med det forkerte resultat.
Brug af tegn, der ikke kan udskrives, som skilletegn
Du kan bruge tegn, der ikke kan udskrives, som skilletegn, f.eks. En vognretur (et linjeskift).
Her bruger vi vbCr til at angive en vognretur <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Opret variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Prøvestreng med vognreturafgrænsningMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString, vbCr,, vbTextCompare)'Ryd regnearketActiveSheet.UsedRange.Clear'Iterere gennem arrayetFor N = 0 Til UBound (MyArray)'Placer hver split i den første kolonne i regnearketOmråde ("A" og N + 1) .Værdi = MyArray (N)Næste NAfslut Sub |
I dette eksempel er en streng opbygget ved hjælp af vbCr (vognreturtegn) som afgrænsning.
Når denne kode køres, ser dit regneark sådan ud:
Brug sammenføjningsfunktionen til at vende en split
Funktionen Deltag vil genforbinde alle elementerne i en matrix, men ved hjælp af en specificeret afgrænser. Hvis der ikke er angivet et afgrænsningstegn, bruges der et mellemrum.
| 123456789101112131415 | Sub JoinExample ()'Opret variablerDim MyArray () As String, MyString As String, I As Variant, N As IntegerDim mål som streng'Prøve streng med kommaafgrænsereMyString = "En, to, tre, fire, fem, seks"'Placer MyString i celle A1Område ("A1"). Værdi = MyString'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString, ",")'Brug Join-funktionen til at genskabe den originale streng ved hjælp af en semikolonafgrænserMål = Deltag (MyArray, ”;”)'Placer resultatstrengen ved celle A2Område ("A2"). Værdi = målAfslut Sub |
Denne kode opdeler en streng med kommaafgrænsere i en matrix og slutter den sammen igen ved hjælp af semikolon afgrænsere.
Efter at have kørt denne kode vil dit regneark se sådan ud:
Celle A1 har den originale streng med kommaafgrænsere, og celle A2 har den nye sammenføjede streng med semikolon afgrænsere.
Brug af splitfunktionen til at foretage et ordtælling
Når du husker på, at en strengvariabel i Excel VBA kan være op til 2 Gb lang, kan du bruge splitfunktionen til at foretage ordtælling i et stykke tekst. Det er klart, at Microsoft Word gør det automatisk, men dette kan være nyttigt for en simpel tekstfil eller tekst, der er kopieret fra et andet program.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Opret variablerDim MyArray () Som streng, MyString som streng'Prøve streng med mellemrumMyString = "One Two Three Four Five Six"'Fjern eventuelle mellemrumMyString = Erstat (MyString, "", "")'Fjern eventuelle ledende eller bageste mellemrumMyString = Trim (MyString)'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString)'Vis antal ord ved hjælp af UBound -funktionenMsgBox "Antal ord" og UBound (MyArray) + 1Afslut Sub |
En af farerne ved denne ordtællingskode er, at den vil blive kastet af dobbelt mellemrum og førende og efterfølgende mellemrum. Hvis disse er til stede, tælles de som ekstra ord, og ordtællingen ender som unøjagtig.
Koden bruger funktionerne Erstat og Trim til at fjerne disse ekstra mellemrum.
Den sidste kodelinje viser antallet af ord, der findes ved hjælp af UBound -funktionen for at få det maksimale elementnummer i arrayet og derefter øge det med 1. Dette skyldes, at det første array -element begynder ved nul.
Opdeling af en adresse i regnearkceller
Mailadresser er ofte lange tekststrenge med kommaafgrænsere. Det kan være en god idé at opdele hver del af adressen i en separat celle.
| 123456789101112131415 | Underadresse Eksempel ()'Opret variablerDim MyArray () Som streng, MyString som streng, N som heltal'Konfigurer streng med Microsoft Corporation -adresseMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Brug splitfunktionen til at opdele strengen ved hjælp af en kommaafgrænserMyArray = Opdel (MyString, ",")'Ryd regnearketActiveSheet.UsedRange.Clear'gentag gennem arrayetFor N = 0 Til UBound (MyArray)'Placer hver split i den første kolonne i regnearketOmråde ("A" og N + 1) .Værdi = MyArray (N)Næste NAfslut Sub |
Hvis du kører denne kode, bruges kommaafgrænseren til at sætte hver linje i adressen i en separat celle:
Hvis du kun ville returnere postnummeret (sidste array -element), kunne du bruge koden:
| 123456789101112 | Sub AddressZipCodeExample ()'Opret variablerDim MyArray () Som streng, MyString som streng, N som heltal, Temp som streng'Konfigurer streng med Microsoft Corporation -adresseMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Brug splitfunktionen til at opdele strengen ved hjælp af en kommaafgrænserMyArray = Opdel (MyString, ",")'Ryd regnearketActiveSheet.UsedRange.Clear'Sæt postnummeret på celle A1Område ("A1"). Værdi = MyArray (UBound (MyArray))Afslut Sub |
Dette vil kun bruge det sidste element i arrayet, som findes ved hjælp af UBound -funktionen.
På den anden side vil du måske gerne se alle linjerne i en celle, så de kan udskrives på en adresselabel:
| 1234567891011121314151617 | Underadresse Eksempel ()'Opret variablerDim MyArray () Som streng, MyString som streng, N som heltal, Temp som streng'Konfigurer streng med Microsoft Corporation -adresseMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Brug splitfunktionen til at opdele strengen ved hjælp af en kommaafgrænserMyArray = Opdel (MyString, ",")'Ryd regnearketActiveSheet.UsedRange.Clear'gentag gennem arrayetFor N = 0 Til UBound (MyArray)'placer hvert array -element plus et linjefeed tegn i en strengTemp = Temp & MyArray (N) & vbLfNæste N'Sæt strengen på regnearketOmråde ("A1") = TempAfslut Sub |
Dette eksempel fungerer på samme måde som det tidligere, bortset fra at det opretter en midlertidig streng af alle matrixelementerne, men der indsættes et linjefeedtegn efter hvert element.
Regnearket ser sådan ud, når koden er kørt:
Opdel streng i regnearkceller
Du kan kopiere Split -arrayet til regnearkceller <> med kun en kommando:
| 12345678910 | Sub CopyToRange ()'Opret variablerDim MyArray () Som streng, MyString som streng'Prøve streng med mellemrumMyString = "En, to, tre, fire, fem, seks"'Brug Split -funktion til at opdele komponentdelene i strengenMyArray = Opdel (MyString, ",")'Kopier arrayet til regnearketOmråde ("A1: A" og UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Afslut Sub |
Når denne kode er kørt, ser dit regneark sådan ud:
Oprettelse af en ny funktion for at tillade opdeling fra et givet punkt
Parameteren Limit i Split -funktionen giver dig kun mulighed for at angive en øvre grænse, hvor splittelsen skal stoppe. Det starter altid fra starten af strengen.
Det ville være meget nyttigt at have en lignende funktion, hvor du kan angive startpunktet for opdelingen i strengen og antallet af opdelinger, du vil se fra det punkt og fremefter. Det vil også kun udtrække de split, du har angivet i arrayet, frem for at have en enorm strengværdi som det sidste element i arrayet.
Du kan let bygge en funktion (kaldet SplitSlicer) selv i VBA for at gøre dette:
| 123456789101112131415161718192021222324 | Funktion SplitSlicer (mål som streng, del som streng, start som heltal, N som heltal)'Opret array -variabelDim MyArray () som streng'Fang split ved hjælp af startvariablen ved hjælp af skilletegnMyArray = Opdel (mål, slet, start)'Kontroller, om startparameteren er større end antallet af splittelser - dette kan forårsage problemerHvis Start> UBound (MyArray) + 1 Så'Vis fejl, og afslut funktionenMsgBox "Startparameter er større end antallet af tilgængelige opdelinger"SplitSlicer = MyArrayAfslut funktionAfslut Hvis'Sæt det sidste array -element i strengenMål = MyArray (UBound (MyArray))'Del strengen med N som grænsenMyArray = Opdel (Mål, Del, N)'Kontroller, at den øvre grænse er større end nul, da koden fjerner det sidste elementHvis UBound (MyArray)> 0 Så'Brug ReDim til at fjerne det sidste element i arrayetReDim Preserve MyArray (UBound (MyArray) - 1)Afslut Hvis'Returner det nye arraySplitSlicer = MyArrayAfslut funktion |
Denne funktion er bygget med fire parametre:
- Mål - string - dette er inputstrengen, du vil dele
- Del - streng eller tegn, der ikke kan udskrives - dette er afgrænsningstegnet, som du f.eks. bruger komma, tyktarm
- Start - nummer - dette er startdelingen for din skive
- N - nummer - dette er antallet af opdelinger, du vil foretage inden for dit udsnit
Ingen af disse parametre er valgfri eller har standardværdier, men du kan indarbejde det i koden til funktionen, hvis du ønsker at udvide den yderligere.
Funktionen bruger splitfunktionen til at oprette et array ved hjælp af parameteren Start som grænse. Det betyder, at matrixelementerne holder splitterne op til startparameteren, men resten af strengen vil være det sidste element og vil ikke blive delt.
Det sidste element i arrayet overføres tilbage til en streng ved hjælp af UBound -funktionen til at bestemme hvilket element dette er.
Strengen opdeles derefter igen i arrayet ved hjælp af N som grænsevariabel. Det betyder, at der vil blive opdelt for strengen op til position N, hvorefter resten af strengen vil danne det sidste element i matrixen.
ReDim -sætningen bruges til at fjerne det sidste element, da vi kun vil have de specifikke elementer tilbage i arrayet. Bemærk, at parameteren Preserve bruges, ellers går alle data i arrayet tabt.
Det nye array returneres derefter til den kode, det blev kaldt fra.
Bemærk, at koden er 'fejlsikret'. Brugere vil ofte gøre mærkelige ting, som du ikke overvejede. For eksempel, hvis de prøver at bruge funktionen med parameteren Start eller N større end det tilgængelige antal splits i strengen, vil dette sandsynligvis få funktionen til at mislykkes.
Kode er inkluderet for at kontrollere Start -værdien og også for at sikre, at der er et element, der kan fjernes, når ReDim -sætningen bruges på arrayet.
Her er koden til at teste funktionen:
| 123456789101112 | Sub TestSplitSlicer ()'Opret variablerDim MyArray () Som streng, MyString som streng'Definer prøvestreng med kommaafgrænsereMyString = "En, to, tre, fire, fem, seks, syv, otte, ni, ti"'Brug Splitslicer -funktionen til at definere nyt arrayMyArray = SplitSlicer (MyString, ",", 4, 3)'Ryd det aktive arkActiveSheet.UsedRange.Clear'Kopier arrayet til regnearketOmråde ("A1: A" og UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Afslut Sub |
Kør denne kode, og dit regneark vil se sådan ud:
