När du arbetar med VBA i Excel kan du behöva dela upp en sträng i olika delar baserat på en avgränsare.
Om du till exempel har en adress kan du använda VBA Split -funktionen för att få olika delar av adressen som är separerade med ett komma (vilket skulle vara avgränsaren i detta fall).
SPLIT är en inbyggd strängfunktion i Excel VBA som du kan använda för att dela en textsträng baserad på avgränsaren.
Excel VBA SPLIT -funktion - Syntax
Split (Uttryck, [Avgränsare], [Gräns], [Jämför])
- Uttryck: Detta är strängen som du vill dela baserat på avgränsaren. Till exempel, i fallet med adressexemplet, skulle hela adressen vara 'uttrycket'. Om detta är en nolllängd sträng (“”) SPLIT-funktion skulle returnera en tom matris.
- Avgränsare: Detta är ett valfritt argument. Detta är avgränsaren som används för att dela argumentet 'Uttryck'. När det gäller vårt adressexempel är ett komma en avgränsare som används för att dela upp adressen i olika delar. Om du inte anger detta argument anses ett mellanslag som standardavgränsare. Om du anger en sträng med noll längd ("") returneras hela "Expression" -strängen av funktionen.
- Begränsa: Detta är ett valfritt argument. Här anger du det totala antalet underlag som du vill returnera. Till exempel, om du bara vill returnera de tre första delarna från "Expression" -argumentet, skulle detta vara 3. Om du inte anger detta argument är standard -1, vilket returnerar alla underlag.
- Jämföra: Detta är ett valfritt argument. Här anger du vilken typ av jämförelse du vill att SPLIT -funktionen ska utföra vid utvärdering av underlaget. Följande alternativ är tillgängliga:
- När Jämförelse är 0: Detta är en binär jämförelse. Det betyder att om din avgränsare är en textsträng (låt oss säga ABC), så skulle detta vara skiftlägeskänsligt. 'ABC' skulle inte vara lika med 'abc'.
- När jämförelse är 1: Detta är en textjämförelse. Detta betyder att om din avgränsare är en textsträng (låt oss säga ABC), även om du har 'abc' i 'Expression' -strängen, skulle den betraktas som en avgränsare.
Nu när vi har täckt grunderna i SPLIT -funktionen, låt oss se några praktiska exempel.
Exempel 1 - Dela upp orden i en mening
Antag att jag har texten - "The Quick Brown Fox hoppar över den lata hunden".
Jag kan använda SPLIT -funktionen för att få in varje ord i denna mening som ett separat objekt i en array.
Nedanstående kod skulle till detta:
Sub SplitWords () Dim TextStrng Som String Dim Resultat () As String TextStrng = "The Quick Brown Fox hoppar över den lata hunden" Resultat () = Split (TextStrng) End Sub
Även om koden inte gör något användbart, hjälper den dig att förstå vad Split -funktionen i VBA gör.
Delningsfunktionen delar textsträngen och tilldelar varje ord till resultatmatrisen.
Så i det här fallet:
- Resultat (0) lagrar värdet “The”
- Resultat (1) lagrar värdet ”Snabbt”
- Resultat (2) lagrar värdet “Brun” och så vidare.
I det här exemplet har vi bara specificerat det första argumentet - vilket är texten som ska delas. Eftersom ingen avgränsare har angetts, tar det mellanslag som standardavgränsare.
Viktig notering:
- VBA SPLIT -funktionen returnerar en array som startar från bas 0.
- När resultatet av funktionen SPLIT är tilldelat en matris måste den matrisen deklareras som en strängdatatyp. Om du förklarar det som en datatyp av variant, visas det ett felmeddelande om felaktig typ). Observera i exemplet ovan att jag har deklarerat Resultat () som en strängdatatyp.
Exempel 2 - Räkna antalet ord i en mening
Du kan använda SPLIT -funktionen för att få det totala antalet ord i en mening. Tricket här är att räkna antalet element i matrisen som du får när du delar upp texten.
Koden nedan visar en meddelanderuta med ordräkningen:
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 Ordräkning är "& WordCount End Sub
I det här fallet berättar UBound -funktionen den övre gränsen för matrisen (det vill säga det maximala antalet element som arrayen har). Eftersom matrisens bas är 0, läggs 1 till för att få det totala ordräkningen.
Du kan använda en liknande kod för att skapa en anpassad funktion i VBA som tar texten som inmatning och returnerar ordräkningen.
Koden nedan skapar denna 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 du har skapat den kan du använda WordCount -funktionen precis som alla andra vanliga funktioner.
Denna funktion hanterar också ledande, bakre och dubbla mellanslag mellan ord. Detta har möjliggjorts genom att använda TRIM -funktionen i VBA -koden.
Om du vill lära dig mer om hur denna formel fungerar för att räkna antalet ord i en mening eller vill lära dig om ett sätt som inte är VBA-formel för att få ordräkningen, kolla in den här självstudien.
Exempel 3 - Använda en annan avgränsare än mellanslagstecken
I de två föregående exemplen har vi bara använt ett argument i SPLIT -funktionen, och resten var standardargumenten.
När du använder någon annan avgränsare måste du ange det i SPLIT -formeln.
I koden nedan returnerar SPLIT -funktionen en array baserad på ett komma som avgränsare och visar sedan ett meddelande med varje ord i en separat rad.
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 ()) Till UBound (Resultat ()) DisplayText = DisplayText & Resultat (i) & vbNewLine Nästa i MsgBox DisplayText End Sub
I koden ovan har jag använt For Next -slingan för att gå igenom varje element i "Resultat" -matrisen tilldela den "DisplayText" -variabeln.
Exempel 4 - Dela upp en adress i tre delar
Med SPLIT -funktionen kan du ange hur många delningar du vill få. Till exempel, om jag inte anger något, skulle varje instans av avgränsaren användas för att dela strängen.
Men om jag anger 3 som gräns, delas strängen bara i tre delar.
Till exempel om jag har följande adress:
2703 Winifred Way, Indianapolis, Indiana, 46204
Jag kan använda Split -funktionen i VBA för att dela upp den här adressen i tre delar.
Den delar de två första baserat på kommadelaren och återstående del blir det tredje elementet i matrisen.
Koden nedan visar adressen i tre olika rader i en meddelanderuta:
Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Resultat = Split (TextStrng, ",", 3) För i = LBound (Resultat ( )) Till UBound (Resultat ()) DisplayText = DisplayText & Resultat (i) & vbNewLine Nästa i MsgBox DisplayText End Sub
En av de praktiska användningsområdena för detta kan vara när du vill dela upp en enda radadress i det format som visas i meddelanderutan. Sedan kan du skapa en anpassad funktion som returnerar adressen uppdelad i tre delar (med varje del i en ny rad).
Följande kod skulle göra detta:
Funktion ThreePartAddress (cellRef As Range) Dim TextStrng Som String Dim Resultat () Som String Dim DisplayText Som String Resultat = Split (cellRef, ",", 3) För i = LBound (Resultat ()) Till UBound (Resultat ()) DisplayText = DisplayText & Trim (Resultat (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) Slutfunktion
När du har den här koden i modulen kan du använda funktionen (ThreePartAddress) i arbetsboken precis som alla andra Excel -funktioner.
Denna funktion tar ett argument - cellreferensen som har adressen.
Observera att för att den resulterande adressen ska visas i tre olika rader måste du använda wrap -textformatet på cellerna (det finns på fliken Hem i gruppen Justering). Om formatet "Wrap Text" inte är aktiverat ser du hela adressen som en enda rad.
Exempel 5 - Hämta stadsnamnet från adressen
Med Split -funktion i VBA kan du ange vilken del av den resulterande matrisen du vill använda.
Anta till exempel att jag delar upp följande adress baserat på kommatecken som avgränsare:
2703 Winifred Way, Indianapolis, Indiana, 46204
Den resulterande matrisen skulle se ut något som visas nedan:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Eftersom detta är en array kan jag välja att visa eller returnera en specifik del av denna array.
Nedan finns en kod för en anpassad funktion, där du kan ange ett nummer och det returnerar det elementet från matrisen. Till exempel, om jag vill ha statens namn, kan jag ange 3 (eftersom det är det tredje elementet i matrisen).
Function ReturnNthElement (CellRef As Range, ElementNumber As Integer) Dim Resultat () Som Strängresultat = Split (CellRef, ",") ReturnNthElement = Resultat (ElementNumber - 1) Avsluta funktion
Ovanstående funktion tar två argument, cellreferensen som har adressen och elementnumret du vill returnera. Delningsfunktionen delar upp adresselementen och tilldelar resultatvariabeln.
Sedan returnerar det elementnumret som du angav som det andra argumentet. Observera att eftersom basen är 0 används ElementNumber-1 för att returnera rätt del av adressen.
Om du vill ha stadsnamnet kan du använda 2 som det andra argumentet. Om du använder ett tal som är högre än det totala antalet element, skulle det returnera #VÄRDE! fel.
Du kan ytterligare förenkla koden enligt nedan:
Function ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) End Function
I koden ovan, istället för att använda resultatvariabeln, returnerar den bara det angivna elementnumret.
Så om du har Split ("Good Morning") (0), skulle det bara returnera det första elementet, vilket är "Good".
På samma sätt returnerar den i koden ovan bara det angivna elementnumret.
