Bruke VBA Split -funksjonen
VBA Split -funksjonen lar deg skille ut komponentdelene fra en standard tekststreng der hver komponent bruker et bestemt skilletegn, f.eks. et komma eller et kolon. Det er lettere å bruke enn å skrive kode for å søke etter avgrenserne i strengen og deretter trekke ut verdiene.
Den kan brukes hvis du leser på en linje fra en kommaseparert verdi (CSV-fil) eller hvis du har en postadresse som er på en linje, men du vil se den som flere linjer.
Syntaksen er:
| 1 | Delt uttrykk, skilletegn [valgfritt], grense [valgfritt], sammenligne [valgfritt] |
VBA Split -funksjonen har fire parametere:
- Uttrykk - Tekststrengen du ønsker å dele opp i forskjellige deler.
- Avgrensning (valgfri)- streng eller tegn som ikke kan skrives ut - Definerer skilletegnet som skal brukes for delingen. Hvis det ikke er angitt noen skilletegn, brukes standarden til et mellomrom.
- Grense (valgfri) - nummer - Definerer hvor mange delinger som skal gjøres. Hvis det er tomt, vil alle tilgjengelige splittelser bli gjort i strengen. Hvis den er satt til 1, blir det ikke delt. I utgangspunktet lar den deg skille ut et bestemt antall verdier som starter ved begynnelsen av strengen, f.eks. hvor strengen er veldig lang og du bare trenger de tre første delingene.
- Sammenligne (valgfri) - Hvis skilletegnet er et teksttegn, brukes dette til å veksle om skilletegnet er bokstavfølsomt eller ikke. Verdiene er vbBinaryCompare (store og små bokstaver) og vbTextCompare (ikke store og små bokstaver).
Delfunksjonen returnerer alltid en matrise.
Enkelt eksempel på delingsfunksjonen
| 123456789101112 | Sub SplitExample ()'Definer variablerDim MyArray () As String, MyString As String, I As Variant'Eksempelstreng med mellomromstegnMyString = "One Two Three Four"'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString)'iterere gjennom matrisen som er opprettet for å vise hver verdiFor hver jeg i MyArrayMsgBox INeste jegSlutt Sub |
I dette eksemplet er det ikke angitt noen skilletegn fordi alle ordene har et mellomrom mellom seg, så standard skilletegn (mellomrom) kan brukes.
Arrayen har ingen dimensjoner, og er angitt som en streng. Variabelen I, som brukes i For… Next -løkken, må dimensjoneres som en variant.
Når denne koden kjøres, vil den vise fire meldingsbokser, en for hver av delingene f.eks. En to tre. Fire.
Vær oppmerksom på at hvis det er et mellomrom mellom ordene i strengen, vil dette bli vurdert som en splittelse, men uten noe i det. Dette er kanskje ikke resultatet du vil se.
Du kan løse dette problemet ved å bruke Erstatt -funksjonen for å erstatte alle dobbeltrom med et enkelt mellomrom:
| 1 | MyString = Erstatt (MyString, "", "") |
Et etterfølgende eller ledende mellomrom kan også forårsake problemer ved å produsere en tom splitt. Disse er ofte veldig vanskelige å se. Du kan fjerne disse fremmede mellomrom ved å bruke Trim -funksjonen:
| 1 | MyString = Trim (MyString) |
Bruke delingsfunksjonen med et skilletegn
Vi kan bruke en skilletegn på et semikolon (;). Dette finnes ofte i e -postadressestrenger for å skille adressene. Du kan få en e -post sendt til deg som deles med en rekke kolleger, og du vil se en liste i regnearket over hvem den har gått til. Du kan enkelt kopiere e -postadressene fra "Til" - eller "Kopier" -boksene til koden din.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definer variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Prøve streng med semikolon avgrensereMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString, ";")"Fjern regnearketActiveSheet.UsedRange.Clear'iterere gjennom matrisenFor N = 0 Til UBound (MyArray)'Plasser hver e -postadresse i den første kolonnen i regnearketOmråde ("A" og N + 1) .Value = MyArray (N)Neste NSlutt Sub |
Vær oppmerksom på at en For … Next loop brukes til å iterere gjennom matrisen. Det første elementet i matrisen starter alltid på null, og funksjonen Upper Bound brukes til å få maksimalt antall elementer.
Etter at du har kjørt denne koden, vil regnearket ditt se slik ut:
Bruke en grenseparameter i en delt funksjon
Grenseparameteren gjør at et spesifikt antall splitt kan utføres fra starten av strengen. Dessverre kan du ikke gi en startposisjon eller en rekke splittelser som skal gjøres, så det er ganske grunnleggende. Du kan bygge din egen VBA -kode for å lage en funksjon for å gjøre dette, og dette vil bli forklart senere i denne artikkelen.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Lag variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Eksempelstreng med kommaavgrensereMyString = "En, to, tre, fire, fem, seks"'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString, ",", 4)"Fjern regnearketActiveSheet.UsedRange.Clear'Iterate gjennom matrisenFor N = 0 Til UBound (MyArray)'Plasser hver splitt i den første kolonnen i regnearketOmråde ("A" og N + 1) .Value = MyArray (N)Neste NSlutt Sub |
Etter at du har kjørt denne koden, vil regnearket ditt se slik ut:
Bare de tre første delingsverdiene vises separat. De tre senere verdiene vises som en lang streng og blir ikke delt.
Hvis du velger en grenseverdi som er større enn antall avgrensere i en streng, vil dette ikke gi en feil. Strengen blir delt inn i alle komponentdelene som om grenseverdien ikke var angitt.
Bruke sammenligningsparameteren i en delt funksjon
Sammenlign-parameteren avgjør om skilletegnet er skille mellom store og små bokstaver. Dette er ikke aktuelt hvis skilletegnene er kommaer, semikolon eller kolon.
Merk: I stedet kan du alltid plassere Option Compare Text <> øverst i modulen for å eliminere store og små bokstaver for hele modulen.
| 123456789101112131415 | Sub SplitByCompareExample ()'Lag variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Eksempelstreng med X -skilletegnMyString = "OneXTwoXThreexFourXFivexSix"'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString, "X",, vbBinaryCompare)"Fjern regnearketActiveSheet.UsedRange.Clear'iterere gjennom matrisenFor N = 0 Til UBound (MyArray)'Plasser hver splitt i den første kolonnen i regnearketOmråde ("A" og N + 1) .Value = MyArray (N)Neste NSlutt Sub |
I dette eksemplet bruker strengen som skal deles "X" -tegnet som skilletegn. I denne strengen er det imidlertid en blanding av store og små X -tegn. Sammenlign -parameteren i Split -funksjonen bruker et X -tegn med store bokstaver.
Hvis sammenligningsparameteren er satt til vbBinaryCompare, blir "x" -tegnene ignorert og regnearket ditt ser slik ut:
Hvis sammenligningsparameteren er satt til vbTextCompare, vil de x -tegnene i små bokstaver bli brukt i delingen, og regnearket ditt vil se slik ut:
Vær oppmerksom på at verdien i celle A6 er avkortet fordi den inneholder et lite x -tegn. Fordi splittelsen ikke er store og små bokstaver, vil enhver avgrensning som inngår i en delstreng føre til at en splittelse skjer.
Dette er et viktig poeng å huske på når du bruker en tekstavgrensning og vbTextCompare. Du kan enkelt ende opp med feil resultat.
Bruke tegn som ikke kan skrives ut som skilletegn
Du kan bruke tegn som ikke kan skrives ut som skilletegn, for eksempel vognretur (et linjeskift).
Her bruker vi vbCr til å spesifisere en vognretur <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Lag variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Eksempelstreng med vognreturavgrensereMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString, vbCr,, vbTextCompare)"Fjern regnearketActiveSheet.UsedRange.Clear'Iterate gjennom matrisenFor N = 0 Til UBound (MyArray)'Plasser hver splitt i den første kolonnen i regnearketOmråde ("A" og N + 1) .Value = MyArray (N)Neste NSlutt Sub |
I dette eksemplet er en streng bygget opp med vbCr (vognreturtegn) som skilletegn.
Når denne koden kjøres, vil regnearket ditt se slik ut:
Bruk sammenføyningsfunksjonen til å reversere en splittelse
Join-funksjonen kobler sammen alle elementene i en matrise på nytt, men bruker en spesifisert skilletegn. Hvis det ikke er angitt noen skilletegn, vil et mellomrom bli brukt.
| 123456789101112131415 | Sub JoinExample ()'Lag variablerDim MyArray () As String, MyString As String, I As Variant, N As IntegerDim mål som streng'Eksempelstreng med kommaavgrensereMyString = "En, to, tre, fire, fem, seks"'Plasser MyString i celle A1Område ("A1"). Verdi = MyString'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString, ",")'Bruk Join-funksjonen til å lage den opprinnelige strengen på nytt ved hjelp av en semikolonavgrensningMål = Bli med (MyArray, ”;”)'Plasser resultatstrengen ved celle A2Område ("A2"). Verdi = målSlutt Sub |
Denne koden deler en streng med kommaavgrensere i en matrise, og kobler den sammen igjen ved hjelp av semikolon-skilletegn.
Etter å ha kjørt denne koden vil regnearket ditt se slik ut:
Celle A1 har den opprinnelige strengen med kommaavgrensere, og celle A2 har den nye sammenføyde strengen med semikolon-skilletegn.
Bruke delingsfunksjonen til å gjøre et ordtelling
Når du husker at en strengvariabel i Excel VBA kan være opptil 2 Gb lang, kan du bruke delingsfunksjonen til å gjøre ordtelling i et stykke tekst. Selvfølgelig gjør Microsoft Word det automatisk, men dette kan være nyttig for en enkel tekstfil eller tekst kopiert fra et annet program.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Lag variablerDim MyArray () Som streng, MyString som streng'Eksempelstreng med mellomromstegnMyString = "One Two Three Four Five Six"'Fjern eventuelle mellomromMyString = Erstatt (MyString, "", "")'Fjern eventuelle ledende eller bakre mellomromMyString = Trim (MyString)'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString)'Vis antall ord ved hjelp av UBound -funksjonenMsgBox "Antall ord" og UBound (MyArray) + 1Slutt Sub |
En av farene ved denne ordtellingskoden er at den vil bli kastet av doble mellomrom og ledende og etterfølgende mellomrom. Hvis disse er tilstede, blir de regnet som ekstraord, og ordtellingen vil ende opp som unøyaktig.
Koden bruker funksjonene Erstatt og Trim for å fjerne disse ekstra mellomrommene.
Den siste kodelinjen viser antall ord som er funnet ved å bruke UBound -funksjonen for å få det maksimale elementnummeret i matrisen og deretter øke det med 1. Dette er fordi det første arrayelementet begynner på null.
Del en adresse i regnearkceller
Postadresser er ofte lange tekststrenger med kommaavgrensere. Det kan være lurt å dele hver del av adressen i en egen celle.
| 123456789101112131415 | Sub AddressExample ()'Lag variablerDim MyArray () As String, MyString As String, N As Integer'Sett opp streng med Microsoft Corporation -adresseMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Bruk delingsfunksjonen til å dele strengen ved hjelp av en kommaavgrensningMyArray = Split (MyString, ",")"Fjern regnearketActiveSheet.UsedRange.Clear'iterere gjennom matrisenFor N = 0 Til UBound (MyArray)'Plasser hver splitt i den første kolonnen i regnearketOmråde ("A" og N + 1) .Value = MyArray (N)Neste NSlutt Sub |
Hvis du kjører denne koden, bruker du kommaavgrenseren for å sette hver linje i adressen i en egen celle:
Hvis du bare ville returnere postnummeret (siste matrise -elementet), kan du bruke koden:
| 123456789101112 | Sub AddressZipCodeExample ()'Lag variablerDim MyArray () As String, MyString As String, N As Integer, Temp As String'Sett opp streng med Microsoft Corporation -adresseMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Bruk delingsfunksjonen til å dele strengen ved hjelp av en kommaavgrensningMyArray = Split (MyString, ",")"Fjern regnearketActiveSheet.UsedRange.Clear'Sett postnummeret i celle A1Område ("A1"). Verdi = MyArray (UBound (MyArray))Slutt Sub |
Dette vil bare bruke det siste elementet i matrisen, som er funnet ved å bruke UBound -funksjonen.
På den annen side kan det være lurt å se alle linjene i en celle slik at de kan skrives ut på en adresselabel:
| 1234567891011121314151617 | Sub AddressExample ()'Lag variablerDim MyArray () As String, MyString As String, N As Integer, Temp As String'Sett opp streng med Microsoft Corporation -adresseMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Bruk delingsfunksjonen til å dele strengen ved hjelp av en kommaavgrensningMyArray = Split (MyString, ",")"Fjern regnearketActiveSheet.UsedRange.Clear'iterere gjennom matrisenFor N = 0 Til UBound (MyArray)'plasser hvert arrayelement pluss et linjeformet tegn i en strengTemp = Temp & MyArray (N) & vbLfNeste N'Legg strengen på regnearketOmråde ("A1") = TempSlutt Sub |
Dette eksemplet fungerer på samme måte som det tidligere, bortsett fra at det oppretter en midlertidig streng for alle matriseelementene, men setter inn et linjeformet tegn etter hvert element.
Regnearket vil se slik ut etter at koden er kjørt:
Del strengen i regnearkceller
Du kan kopiere Split -matrisen til regnearkceller <> med bare en kommando:
| 12345678910 | Sub CopyToRange ()'Lag variablerDim MyArray () Som streng, MyString som streng'Eksempelstreng med mellomromstegnMyString = "En, to, tre, fire, fem, seks"'Bruk Split -funksjonen til å dele opp komponentdelene i strengenMyArray = Split (MyString, ",")'Kopier matrisen til regnearketOmråde ("A1: A" og UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Slutt Sub |
Når denne koden er kjørt, vil regnearket ditt se slik ut:
Opprette en ny funksjon for å tillate splitting fra et gitt punkt
Limit -parameteren i Split -funksjonen lar deg bare angi en øvre grense der du vil at delingen skal stoppe. Det starter alltid fra starten av strengen.
Det ville være veldig nyttig å ha en lignende funksjon der du kan spesifisere startpunktet for delingen i strengen, og antall splitt du vil se fra det punktet og fremover. Det vil også bare trekke ut splittene du har spesifisert i matrisen, i stedet for å ha en enorm strengverdi som det siste elementet i matrisen.
Du kan enkelt bygge en funksjon (kalt SplitSlicer) selv i VBA for å gjøre dette:
| 123456789101112131415161718192021222324 | Funksjon SplitSlicer (mål som streng, del som streng, start som heltall, N som heltall)'Lag matrisevariabelDim MyArray () Som streng'Ta delingen ved å bruke startvariabelen ved hjelp av skilletegnetMyArray = Split (Target, Del, Start)'Sjekk om startparameteren er større enn antall splittelser - dette kan forårsake problemerHvis Start> UBound (MyArray) + 1 Da"Vis feil og avslutt funksjonenMsgBox "Startparameter er større enn antall tilgjengelige splitter"SplitSlicer = MyArrayAvslutt funksjonSlutt om'Sett det siste matriseelementet inn i strengenMål = MyArray (UBound (MyArray))'Del strengen med N som grenseMyArray = Split (Target, Del, N)'Kontroller at toppgrensen er større enn null ettersom koden fjerner det siste elementetHvis UBound (MyArray)> 0 Da'Bruk ReDim for å fjerne det siste elementet i matrisenReDim Preserve MyArray (UBound (MyArray) - 1)Slutt om'Returner den nye matrisenSplitSlicer = MyArraySluttfunksjon |
Denne funksjonen er bygget med fire parametere:
- Mål - string - dette er inndatastrengen du vil dele
- Del - streng eller tegn som ikke kan skrives ut - dette er skilletegnet som du bruker f.eks. komma, tykktarm
- Start - nummer - dette er startdelingen for skiven din
- N - nummer - dette er antall splitt du vil gjøre i skiven
Ingen av disse parameterne er valgfrie eller har standardverdier, men du kan legge det inn i koden for funksjonen hvis du ønsker å utvide den ytterligere.
Funksjonen bruker Split -funksjonen til å lage en matrise ved hjelp av Start -parameteren som grense. Dette betyr at matriseelementene holder splittene opp til startparameteren, men resten av strengen vil være det siste elementet, og vil ikke bli delt.
Det siste elementet i matrisen overføres tilbake til en streng ved hjelp av UBound -funksjonen for å bestemme hvilket element dette er.
Strengen blir deretter delt opp igjen i matrisen, ved å bruke N som grensevariabel. Dette betyr at splitter vil bli utført for strengen opp til posisjon N, hvoretter resten av strengen vil danne det siste elementet i rekken.
ReDim -setningen brukes til å fjerne det siste elementet, ettersom vi bare vil ha de spesifikke elementene igjen i matrisen. Vær oppmerksom på at Preserve -parameteren brukes, ellers vil alle data i matrisen gå tapt.
Den nye matrisen returneres deretter til koden den ble kalt fra.
Vær oppmerksom på at koden er "feilsikker". Brukere vil ofte gjøre rare ting du ikke tenkte på. For eksempel, hvis de prøver å bruke funksjonen med Start eller N -parameteren større enn det tilgjengelige antallet splitt i strengen, vil dette sannsynligvis føre til at funksjonen mislykkes.
Koden er inkludert for å kontrollere Start -verdien, og også for å sikre at det er et element som kan fjernes når ReDim -setningen brukes på matrisen.
Her er koden for å teste funksjonen:
| 123456789101112 | Sub TestSplitSlicer ()'Lag variablerDim MyArray () Som streng, MyString som streng'Definer prøvestreng med kommaavgrensereMyString = "En, to, tre, fire, fem, seks, syv, åtte, ni, ti"'Bruk Splitslicer -funksjonen til å definere nytt arrayMyArray = SplitSlicer (MyString, ",", 4, 3)'Fjern det aktive arketActiveSheet.UsedRange.Clear'Kopier matrisen til regnearketOmråde ("A1: A" og UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Slutt Sub |
Kjør denne koden, og regnearket ditt vil se slik ut:
