Når du arbeider med VBA i Excel, kan du ha behov for å dele en streng i forskjellige deler basert på en skilletegn.
For eksempel, hvis du har en adresse, kan du bruke VBA Split -funksjonen til å få forskjellige deler av adressen som er atskilt med et komma (som ville være skilletegn i dette tilfellet).
SPLIT er en innebygd strengfunksjon i Excel VBA som du kan bruke til å dele en tekststreng basert på skilletegnet.
Excel VBA SPLIT -funksjon - syntaks
Splitt (Uttrykk, [Avgrensning], [Grense], [Sammenlign])
- Uttrykk: Dette er strengen du vil dele basert på skilletegn. For eksempel, i tilfelle av adresseeksemplet, vil hele adressen være "uttrykket". I tilfelle dette er en nulllengde streng (“”) SPLIT-funksjon ville returnere en tom matrise.
- Avgrensning: Dette er et valgfritt argument. Dette er skilletegn som brukes til å dele argumentet "Uttrykk". Når det gjelder vårt adresseeksempel, er et komma en skilletegn som brukes til å dele adressen i forskjellige deler. Hvis du ikke angir dette argumentet, regnes et mellomrom som standard skilletegn. Hvis du gir en nulllengde streng (“”), returneres hele ‘Expression’ strengen av funksjonen.
- Grense: Dette er et valgfritt argument. Her angir du totalt antall substrings du vil returnere. For eksempel, hvis du bare vil returnere de tre første substrengene fra "Uttrykk" -argumentet, vil dette være 3. Hvis du ikke spesifiserer dette argumentet, er standard -1, som returnerer alle understrøkene.
- Sammenligne: Dette er et valgfritt argument. Her angir du hvilken sammenligningstype du vil at SPLIT -funksjonen skal utføre ved evaluering av underlagene. Følgende alternativer er tilgjengelige:
- Når sammenligning er 0: Dette er en binær sammenligning. Dette betyr at hvis skilletegnet er en tekststreng (la oss si ABC), så ville dette være store og små bokstaver. 'ABC' ville ikke være lik 'abc'.
- Når sammenligning er 1: Dette er en tekstsammenligning. Dette betyr at hvis avgrensningen din er en tekststreng (la oss si ABC), så selv om du har 'abc' i 'Uttrykk' -strengen, vil den bli sett på som en skilletegn.
Nå som vi har dekket det grunnleggende i SPLIT -funksjonen, la oss se noen praktiske eksempler.
Eksempel 1 - Del ordene i en setning
Anta at jeg har teksten - “The Quick Brown Fox Jumpes Over The Lazy Dog”.
Jeg kan bruke SPLIT -funksjonen til å få hvert ord i denne setningen inn som et eget element i en matrise.
Koden nedenfor vil gjøre dette:
Sub SplitWords () Dim TextStrng As String Dim Result () As String TextStrng = "The Quick Brown Fox Jumpes Over The Lazy Dog" Resultat () = Split (TextStrng) End Sub
Selv om koden ikke gjør noe nyttig, vil den hjelpe deg å forstå hva Split -funksjonen i VBA gjør.
Delingsfunksjon deler tekststrengen og tilordner hvert ord til resultatmatrisen.
Så i dette tilfellet:
- Resultat (0) lagrer verdien “The”
- Resultat (1) lagrer verdien “Quick”
- Resultat (2) lagrer verdien “Brun” og så videre.
I dette eksemplet har vi bare spesifisert det første argumentet - som er teksten som skal deles. Siden ingen skilletegn er angitt, tar det mellomromstegn som standard skilletegn.
Viktig notat:
- VBA SPLIT -funksjon returnerer en matrise som starter fra base 0.
- Når resultatet av SPLIT -funksjonen er tilordnet en matrise, må denne matrisen deklareres som en strengdatatype. Hvis du erklærer det som en variantdatatype, viser det en feilmelding om type). Vær oppmerksom på at i eksemplet ovenfor har jeg erklært Resultat () som en strengdatatype.
Eksempel 2 - Tell antall ord i en setning
Du kan bruke SPLIT -funksjonen for å få totalt antall ord i en setning. Trikset her er å telle antall elementer i matrisen du får når du deler teksten.
Koden nedenfor viser en meldingsboks med ordtellingen:
Sub WordCount () Dim TextStrng As String Dim WordCount As Integer Dim Result () As String TextStrng = "The Quick Brown Fox Jumpes Over The Lazy Dog" Resultat = Split (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox "The Ordtelling er "& WordCount End Sub
I dette tilfellet forteller UBound -funksjonen oss den øvre grensen for matrisen (dvs. det maksimale antallet elementer matrisen har). Siden basen i matrisen er 0, blir 1 lagt til for å få det totale ordtallet.
Du kan bruke en lignende kode for å lage en egendefinert funksjon i VBA som tar teksten som inndata og returnerer ordtellingen.
Koden nedenfor vil opprette denne funksjonen:
Funksjon WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
Når den er opprettet, kan du bruke WordCount -funksjonen akkurat som alle andre vanlige funksjoner.
Denne funksjonen håndterer også ledende, etterfølgende og doble mellomrom mellom ord. Dette har blitt mulig ved å bruke TRIM -funksjonen i VBA -koden.
Hvis du vil lære mer om hvordan denne formelen fungerer for å telle antall ord i en setning eller ønsker å lære om en formel som ikke er VBA for å få ordtellingen, kan du sjekke ut denne opplæringen.
Eksempel 3 - Bruke en annen skilletegn enn mellomromstegn
I de to foregående eksemplene har vi bare brukt ett argument i SPLIT -funksjonen, og resten var standardargumentene.
Når du bruker en annen skilletegn, må du spesifisere det i SPLIT -formelen.
I koden nedenfor returnerer SPLIT -funksjonen en matrise basert på et komma som skilletegn, og viser deretter en melding med hvert ord i en egen linje.
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, ",") For i = LBound (Resultat ()) Til UBound (Resultat ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub
I koden ovenfor har jeg brukt For Next -løkken til å gå gjennom hvert element i "Resultat" -matrisen tilordne den til "DisplayText" -variabelen.
Eksempel 4 - Del en adresse i tre deler
Med SPLIT -funksjonen kan du angi hvor mange splitt du vil ha. For eksempel, hvis jeg ikke spesifiserer noe, vil hver forekomst av skilletegnet bli brukt til å dele strengen.
Men hvis jeg angir 3 som grense, blir strengen bare delt i tre deler.
For eksempel hvis jeg har følgende adresse:
2703 Winifred Way, Indianapolis, Indiana, 46204
Jeg kan bruke Split -funksjonen i VBA til å dele denne adressen i tre deler.
Den deler de to første basert på kommaavgrenseren, og gjenværende del blir det tredje elementet i matrisen.
Koden nedenfor viser adressen i tre forskjellige linjer i en meldingsboks:
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) For i = LBound (Resultat ( )) Til UBound (Resultat ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub
En av de praktiske bruksområdene til dette kan være når du vil dele en enkelt linjeadresse i formatet som vises i meldingsboksen. Deretter kan du opprette en egendefinert funksjon som returnerer adressen delt inn i tre deler (med hver del i en ny linje).
Følgende kode ville gjøre dette:
Funksjon ThreePartAddress (cellRef As Range) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Trim (Resultat (i)) & vbNewLine Next i ThreePartAddress = Midt (DisplayText, 1, Len (DisplayText) - 1) Sluttfunksjon
Når du har denne koden i modulen, kan du bruke funksjonen (ThreePartAddress) i arbeidsboken akkurat som alle andre Excel -funksjoner.
Denne funksjonen tar ett argument - cellereferansen som har adressen.
Vær oppmerksom på at for at den resulterende adressen skal vises i tre forskjellige linjer, må du bruke wrap -tekstformatet på cellene (det er i kategorien Hjem i justeringsgruppen). Hvis "Wrap Text" -formatet ikke er aktivert, ser du hele adressen som en enkelt linje.
Eksempel 5 - Få bynavnet fra adressen
Med Split -funksjon i VBA kan du spesifisere hvilken del av den resulterende matrisen du vil bruke.
Anta for eksempel at jeg deler følgende adresse basert på kommaet som skilletegn:
2703 Winifred Way, Indianapolis, Indiana, 46204
Den resulterende matrisen vil se noe ut som vist nedenfor:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Siden dette er en matrise, kan jeg velge å vise eller returnere en bestemt del av denne matrisen.
Nedenfor er en kode for en egendefinert funksjon, der du kan angi et tall, og det vil returnere det elementet fra matrisen. For eksempel, hvis jeg vil ha statens navn, kan jeg spesifisere 3 (ettersom det er det tredje elementet i matrisen).
Function ReturnNthElement (CellRef As Range, ElementNumber As Integer) Dim Result () As String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Function
Funksjonen ovenfor tar to argumenter, cellereferansen som har adressen og elementnummeret du vil returnere. Split -funksjonen deler adresseelementene og tilordner det til resultatvariabelen.
Deretter returnerer det elementnummeret du angav som det andre argumentet. Vær oppmerksom på at siden basen er 0, brukes ElementNumber-1 for å returnere den riktige delen av adressen.
Hvis du vil ha bynavnet, kan du bruke 2 som det andre argumentet. Hvis du bruker et tall som er høyere enn det totale antallet elementer, vil det returnere #VERDI! feil.
Du kan forenkle koden ytterligere som vist nedenfor:
Funksjon ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) Sluttfunksjon
I koden ovenfor, i stedet for å bruke resultatvariabelen, returnerer den bare det angitte elementnummeret.
Så hvis du har Split ("Good Morning") (0), vil det bare returnere det første elementet, som er "Good".
På samme måte returnerer den i den ovennevnte koden bare det angitte elementnummeret.
