Excel VBA -splitfunktion - forklaret med eksempler

Når du arbejder med VBA i Excel, har du muligvis brug for at opdele en streng i forskellige dele baseret på en afgrænser.

For eksempel, hvis du har en adresse, kan du bruge VBA Split -funktionen til at få forskellige dele af adressen, der er adskilt med et komma (hvilket ville være afgrænsningen i dette tilfælde).

SPLIT er en indbygget strengfunktion i Excel VBA, som du kan bruge til at opdele en tekststreng baseret på skilletegn.

Excel VBA SPLIT -funktion - Syntaks

Opdel (Udtryk, [Afgrænser], [Grænse], [Sammenlign])

• Udtryk: Dette er den streng, du vil opdele baseret på afgrænseren. For eksempel i tilfælde af adresseeksemplet ville hele adressen være 'udtrykket'. Hvis dette er en nul-længde streng (“”) SPLIT-funktion ville returnere et tomt array.
• Afgrænser: Dette er et valgfrit argument. Dette er den afgrænsning, der bruges til at opdele argumentet 'Udtryk'. I tilfælde af vores adresseeksempel er et komma en afgrænser, der bruges til at opdele adressen i forskellige dele. Hvis du ikke angiver dette argument, betragtes et mellemrum som standardafgrænsning. Hvis du angiver en streng med nul længde (“”), returneres hele ‘Expression’ -strengen af ​​funktionen.
• Begrænse: Dette er et valgfrit argument. Her angiver du det samlede antal substrings, du vil returnere. For eksempel, hvis du kun vil returnere de tre første substrings fra argumentet 'Udtryk', ville dette være 3. Hvis du ikke angiver dette argument, er standard -1, som returnerer alle underlag.
• Sammenligne: Dette er et valgfrit argument. Her angiver du den sammenligningstype, du vil have, at SPLIT -funktionen skal udføre ved evaluering af underlagene. Følgende muligheder er tilgængelige:
  • Når sammenligning er 0: Dette er en binær sammenligning. Det betyder, at hvis din afgrænser er en tekststreng (lad os sige ABC), så ville dette være store og små bogstaver. 'ABC' ville ikke være lig med 'abc'.
  • Når sammenligning er 1: Dette er en tekst sammenligning. Det betyder, at hvis din afgrænser er en tekststreng (lad os sige ABC), så selvom du har 'abc' i 'Udtryk' -strengen, ville den blive betragtet som en afgrænsning.

Nu hvor vi har dækket det grundlæggende i SPLIT -funktionen, lad os se et par praktiske eksempler.

Eksempel 1 - Opdel ordene i en sætning

Antag, at jeg har teksten - "Den hurtige brune ræv springer over den dovne hund".

Jeg kan bruge funktionen SPLIT til at få hvert ord i denne sætning ind som et separat element i en matrix.

Nedenstående kode ville gøre dette:

Sub SplitWords () Dim TextStrng Som String Dim Resultat () As String TextStrng = "Den hurtige brune ræv springer over den dovne hund" Resultat () = Split (TextStrng) End Sub

Selvom koden ikke gør noget nyttigt, hjælper den dig med at forstå, hvad splitfunktionen i VBA gør.

Opdelingsfunktion opdeler tekststrengen og tildeler hvert ord resultatmatrixen.

Så i dette tilfælde:

• Resultat (0) gemmer værdien “The”
• Resultat (1) gemmer værdien "Hurtig"
• Resultat (2) gemmer værdien "Brun" og så videre.

I dette eksempel har vi kun specificeret det første argument - som er teksten, der skal deles. Da der ikke er angivet en afgrænsning, tager den mellemrumstegn som standardafgrænsning.

Vigtig note:

1. VBA SPLIT -funktion returnerer et array, der starter fra base 0.
2. Når resultatet af SPLIT -funktionen er tildelt til en matrix, skal denne matrix deklareres som en strengdatatype. Hvis du erklærer det som en variantdatatype, viser det en fejl i typen, der ikke matcher). Bemærk i eksemplet ovenfor, at jeg har erklæret Resultat () som en strengdatatype.

Eksempel 2 - Tæl antallet af ord i en sætning

Du kan bruge funktionen SPLIT til at få det samlede antal ord i en sætning. Tricket her er at tælle antallet af elementer i arrayet, du får, når du deler teksten.

Nedenstående kode viser en meddelelsesboks med ordtællingen:

Sub WordCount () Dim TextStrng As String Dim WordCount As Integer Dim Result () As String TextStrng = "Den hurtige brune ræv springer over den dovne hund" Resultat = Split (TextStrng) WordCount = UBound (Resultat ()) + 1 MsgBox "The Ordtælling er "& WordCount End Sub

I dette tilfælde fortæller UBound -funktionen os den øvre grænse for arrayet (dvs. det maksimale antal elementer, arrayet har). Da matrixens base er 0, tilføjes 1 for at få det samlede antal ord.

Du kan bruge en lignende kode til at oprette en brugerdefineret funktion i VBA, der tager teksten som input og returnerer ordtællingen.

Nedenstående kode opretter denne funktion:

Funktion WordCount (CellRef As Range) Dim TextStrng Som String Dim Resultat () Som String Resultat = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Resultat ()) + 1 Slutfunktion

Når den er oprettet, kan du bruge WordCount -funktionen ligesom enhver anden almindelig funktion.

Denne funktion håndterer også ledende, efterfølgende og dobbelte mellemrum mellem ord. Dette er blevet muliggjort ved at bruge TRIM -funktionen i VBA -koden.

Hvis du vil lære mere om, hvordan denne formel fungerer til at tælle antallet af ord i en sætning eller ønsker at lære om en formel, der ikke er VBA, til at få ordtællingen, kan du tjekke denne vejledning.

Eksempel 3 - Brug af en anden afgrænser end mellemrumstegn

I de to foregående eksempler har vi kun brugt et argument i SPLIT -funktionen, og resten var standardargumenterne.

Når du bruger en anden afgrænser, skal du angive det i SPLIT -formlen.

I nedenstående kode returnerer SPLIT -funktionen et array baseret på et komma som afgrænsning, og viser derefter en meddelelse med hvert ord i en separat 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" Resultat = Split (TextStrng, ",") For i = LBound (Resultat ()) Til UBound (Resultat ()) DisplayText = DisplayText & Resultat (i) & vbNewLine Næste i MsgBox DisplayText End Sub

I ovennævnte kode har jeg brugt For Next -løkken til at gå gennem hvert element i 'Resultat' -arrayet, tildele det til variablen' DisplayText '.

Eksempel 4 - Opdel en adresse i tre dele

Med SPLIT -funktionen kan du angive, hvor mange antal splittelser du vil have. For eksempel, hvis jeg ikke angiver noget, vil hver forekomst af afgrænsningen blive brugt til at dele strengen.

Men hvis jeg angiver 3 som grænsen, bliver strengen kun opdelt i tre dele.

For eksempel, hvis jeg har følgende adresse:

2703 Winifred Way, Indianapolis, Indiana, 46204

Jeg kan bruge Split -funktionen i VBA til at opdele denne adresse i tre dele.

Den opdeler de to første baseret på kommaafgrænseren, og den resterende del bliver det tredje element i arrayet.

Nedenstående kode viser adressen i tre forskellige linjer i en meddelelsesboks:

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 & Resultat (i) & vbNewLine Næste i MsgBox DisplayText End Sub

En af de praktiske anvendelser af dette kan være, når du vil opdele en enkelt linjeadresse i det format, der vises i meddelelsesboksen. Derefter kan du oprette en brugerdefineret funktion, der returnerer adressen opdelt i tre dele (med hver del i en ny linje).

Følgende kode ville gøre dette:

Funktion ThreePartAddress (cellRef As Range) Dim TextStrng Som String Dim Resultat () Som String Dim DisplayText Som String Resultat = Split (cellRef, ",", 3) For i = LBound (Resultat ()) Til UBound (Resultat ()) DisplayText = DisplayText & Trim (Resultat (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) Slutfunktion

Når du har denne kode i modulet, kan du bruge funktionen (ThreePartAddress) i projektmappen ligesom alle andre Excel -funktioner.

Denne funktion tager et argument - den cellereference, der har adressen.

Bemærk, at for at den resulterende adresse skal vises i tre forskellige linjer, skal du anvende wrap -tekstformatet på cellerne (det er på fanen Startside i gruppen Justering). Hvis formatet ‘Wrap Text’ ikke er aktiveret, ser du hele adressen som en enkelt linje.

Eksempel 5 - Hent bynavnet fra adressen

Med Split -funktion i VBA kan du angive, hvilken del af det resulterende array du vil bruge.

Antag for eksempel, at jeg opdeler følgende adresse baseret på kommaet som afgrænsning:

2703 Winifred Way, Indianapolis, Indiana, 46204

Det resulterende array ville se noget ud som vist nedenfor:

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

Da dette er en matrix, kan jeg vælge at vise eller returnere en bestemt del af denne matrix.

Nedenfor er en kode til en brugerdefineret funktion, hvor du kan angive et nummer, og det returnerer elementet fra arrayet. For eksempel, hvis jeg vil have statsnavnet, kan jeg angive 3 (da det er det tredje element i arrayet).

Funktion ReturnNthElement (CellRef As Range, ElementNumber As Integer) Dim Resultat () Som strengresultat = Split (CellRef, ",") ReturnNthElement = Resultat (ElementNumber - 1) Afslut funktion

Ovenstående funktion tager to argumenter, den cellereference, der har adressen, og det elementnummer, du vil returnere. Opdelingsfunktionen opdeler adresseelementerne og tildeler resultatvariablen.

Derefter returnerer det elementnummeret, som du angav som det andet argument. Bemærk, at da basen er 0, bruges ElementNumber-1 til at returnere den korrekte del af adressen.

Denne brugerdefinerede formel er bedst egnet, når du har et konsistent format i hele adressen - dvs. byen er altid nævnt efter de to kommaer. Hvis dataene ikke er konsistente, får du ikke det ønskede resultat.

Hvis du vil have bynavnet, kan du bruge 2 som det andet argument. Hvis du bruger et tal, der er højere end det samlede antal elementer, returnerer det #VÆRDI! fejl.

Du kan yderligere forenkle koden som vist herunder:

Function ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) End Function

I ovenstående kode returnerer den i stedet for at bruge resultatvariablen kun det angivne elementnummer.

Så hvis du har Split ("Good Morning") (0), ville det kun returnere det første element, som er "Good".

På samme måde returnerer den i ovenstående kode kun det angivne elementnummer.