Använda VBA -delningsfunktionen
VBA Split -funktionen låter dig separera komponentdelarna från en standardtextsträng där varje komponent använder ett specifikt avgränsare tecken t.ex. ett komma eller ett kolon. Det är lättare att använda än att skriva kod för att söka efter avgränsarna i strängen och sedan extrahera värdena.
Den kan användas om du läser på en rad från ett kommaseparerat värde (CSV-fil) eller om du har en postadress som finns på en rad, men du vill se den som flera rader.
Syntaxen är:
| 1 | Delat uttryck, avgränsare [valfritt], gräns [valfritt], jämför [valfritt] |
VBA Split -funktionen har fyra parametrar:
- Uttryck - Textsträngen som du vill dela upp i olika delar.
- Avgränsare (frivillig)- sträng eller icke -utskrivbart tecken - Definierar avgränsarens tecken som ska användas för delningen. Om inget avgränsande tecken tillhandahålls används standard för ett mellanslag.
- Begränsa (frivillig) - nummer - Definierar hur många delningar som ska göras. Om det är tomt kommer alla tillgängliga delningar att göras inom strängen. Om den är inställd på 1 kommer inga delningar att göras. I grunden gör det det möjligt att skilja ut ett visst antal värden som börjar i början av strängen, t.ex. där strängen är mycket lång och du bara behöver de tre första delningarna.
- Jämföra (frivillig) - Om din avgränsare är ett texttecken används detta för att växla om avgränsaren är skiftlägeskänslig eller inte. Värdena är vbBinaryCompare (skiftlägeskänslig) och vbTextCompare (inte skiftlägeskänslig).
Delningsfunktionen returnerar alltid en array.
Enkelt exempel på delningsfunktionen
| 123456789101112 | Sub SplitExample ()'Definiera variablerDim MyArray () As String, MyString As String, I As Variant'Provsträng med mellanslagMyString = "One Two Three Four"'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Dela (MyString)'iterera genom matrisen som skapats för att visa varje värdeFör varje I In MyArrayMsgBox INästa jagAvsluta Sub |
I det här exemplet anges ingen avgränsare eftersom alla ord har ett mellanslag mellan sig, så standardavgränsaren (mellanslag) kan användas.
Arrayen har inga dimensioner och är inställd som en sträng. Variabeln I, som används i For… Next -slingan måste dimensioneras som en variant.
När denna kod körs kommer den att visa fyra meddelanderutor, en för var och en av delningarna t.ex. Ett två tre. Fyra.
Observera att om det finns ett mellanrum mellan orden i strängen, kommer detta att utvärderas som en delning, även om det inte finns något i det. Det här kanske inte är resultatet du vill se.
Du kan åtgärda detta problem genom att använda ersättningsfunktionen för att ersätta dubbelrum med ett enda mellanslag:
| 1 | MyString = Ersätt (MyString, "", "") |
Ett bakre eller ledande utrymme kan också orsaka problem genom att producera en tom delning. Dessa är ofta väldigt svåra att se. Du kan ta bort dessa främmande mellanslag med hjälp av funktionen Trim:
| 1 | MyString = Trim (MyString) |
Använda delningsfunktionen med en avgränsare
Vi kan använda en avgränsare för ett semikolon (;). Detta finns ofta i e -postadresssträngar för att separera adresserna. Du kan få ett e -postmeddelande till dig som delas med ett antal kollegor och du vill se en lista i ditt arbetsblad över vem det har gått till. Du kan enkelt kopiera e -postadresserna från rutorna "Till" eller "Kopiera" till din kod.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definiera variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Provsträng med halvkolonavgränsareMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Split (MyString, ";")'Rensa kalkylbladetActiveSheet.UsedRange.Clear'iterera genom matrisenFör N = 0 Till UBound (MyArray)'Placera varje e -postadress i den första kolumnen i kalkylbladetOmråde ("A" och N + 1). Värde = MyArray (N)Nästa NAvsluta Sub |
Observera att en For … Next loop används för att iterera genom arrayen. Det första elementet i arrayen börjar alltid vid noll, och funktionen Upper Bound används för att få maximalt antal element.
När du har kört den här koden kommer ditt kalkylblad att se ut så här:
Använda en gränsparameter i en delad funktion
Limitparametern gör att ett visst antal delningar kan göras från början av strängen. Tyvärr kan du inte ge en startposition eller en rad delningar som ska göras, så det är ganska enkelt. Du kan bygga din egen VBA -kod för att skapa en funktion för att göra detta, och detta kommer att förklaras senare i den här artikeln.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Skapa variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Provsträng med kommaavgränsareMyString = "En, två, tre, fyra, fem, sex"'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Split (MyString, ",", 4)'Rensa kalkylbladetActiveSheet.UsedRange.Clear'Iterera genom matrisenFör N = 0 Till UBound (MyArray)'Placera varje delning i den första kolumnen i kalkylbladetOmråde ("A" och N + 1). Värde = MyArray (N)Nästa NAvsluta Sub |
När du har kört den här koden ser ditt kalkylblad ut så här:
Endast de tre första delade värdena visas separat. De tre senare värdena visas som en lång sträng och delas inte.
Om du väljer ett gränsvärde som är större än antalet avgränsare inom en sträng, kommer detta inte att ge ett fel. Strängen delas upp i alla dess komponentdelar som om gränsvärdet inte hade angetts.
Använda jämförparametern i en delad funktion
Parametern Jämför avgör om avgränsaren är skiftlägeskänslig eller inte. Detta är inte tillämpligt om avgränsaren är kommatecken, halvkolon eller kolon.
Obs! I stället kan du alltid placera Option Compare Text <> högst upp i modulen för att eliminera skiftlägeskänslighet för hela modulen.
| 123456789101112131415 | Sub SplitByCompareExample ()'Skapa variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Provsträng med X avgränsareMyString = "OneXTwoXThreexFourXFivexSix"'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Split (MyString, "X",, vbBinaryCompare)'Rensa kalkylbladetActiveSheet.UsedRange.Clear'iterera genom matrisenFör N = 0 Till UBound (MyArray)'Placera varje delning i den första kolumnen i kalkylbladetOmråde ("A" och N + 1). Värde = MyArray (N)Nästa NAvsluta Sub |
I det här exemplet använder strängen som ska delas "X" -tecknet som avgränsare. I den här strängen finns det dock en blandning av stora och små X -tecken. Parametern Jämför i Split -funktionen använder ett X -tecken med stora bokstäver.
Om parametern Jämför är inställd på vbBinaryCompare ignoreras de små x -tecknen och ditt kalkylblad kommer att se ut så här:
Om parametern Jämför är inställd på vbTextCompare, kommer 'x' -tecknen med gemener att användas i delningen och ditt kalkylblad kommer att se ut så här:
Observera att värdet i cell A6 är avkortat eftersom det innehåller ett x -tecken med gemener. Eftersom delningen inte är skiftlägeskänslig kommer varje avgränsare som ingår i en delsträng att få en delning att ske.
Detta är en viktig punkt att tänka på när du använder en textavgränsare och vbTextCompare. Du kan enkelt hamna i fel resultat.
Använda icke-skrivbara tecken som avgränsare
Du kan använda icke -skrivbara tecken som avgränsare, till exempel en vagnretur (ett radbrytning).
Här använder vi vbCr för att ange en vagnretur <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Skapa variablerDim MyArray () As String, MyString As String, I As Variant, N As Integer'Provsträng med vagnreturavgränsareMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Dela (MyString, vbCr,, vbTextCompare)'Rensa kalkylbladetActiveSheet.UsedRange.Clear'Iterera genom matrisenFör N = 0 Till UBound (MyArray)'Placera varje delning i den första kolumnen i kalkylbladetOmråde ("A" och N + 1). Värde = MyArray (N)Nästa NAvsluta Sub |
I det här exemplet byggs en sträng upp med vbCr (vagnretur) som avgränsare.
När den här koden körs kommer ditt kalkylblad att se ut så här:
Använda funktionen för att vända en delning
Funktionen Koppla samman igen alla element i en matris, men med hjälp av en specificerad avgränsare. Om inget avgränsande tecken har angetts kommer ett mellanslag att användas.
| 123456789101112131415 | Sub JoinExample ()'Skapa variablerDim MyArray () As String, MyString As String, I As Variant, N As IntegerDim mål som sträng'Provsträng med kommaavgränsareMyString = "En, två, tre, fyra, fem, sex"'Placera MyString i cell A1Område ("A1"). Värde = MyString'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Split (MyString, ",")'Använd Join-funktionen för att återskapa den ursprungliga strängen med hjälp av en semikolonavgränsareTarget = Join (MyArray, ”;”)'Placera resultatsträngen vid cell A2Område ("A2"). Värde = målAvsluta Sub |
Den här koden delar upp en sträng med kommaavgränsare i en array och sammanfogar den igen med hjälp av semikolonavgränsare.
När du har kört den här koden kommer ditt kalkylblad att se ut så här:
Cell A1 har den ursprungliga strängen med kommaavgränsare, och cell A2 har den nya sammanfogade strängen med semikolonavgränsare.
Använd delningsfunktionen för att räkna ord
Med tanke på att en strängvariabel i Excel VBA kan vara upp till 2 Gb lång kan du använda delningsfunktionen för att göra ordräkning i en textbit. Uppenbarligen gör Microsoft Word det automatiskt, men det kan vara användbart för en enkel textfil eller text som kopieras från ett annat program.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Skapa variablerDim MyArray () Som sträng, MyString som sträng'Provsträng med mellanslagMyString = "En två tre fyra fem sex"'Ta bort alla dubbla mellanslagMyString = Ersätt (MyString, "", "")'Ta bort alla ledande eller bakre utrymmenMyString = Trim (MyString)'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Dela (MyString)'Visa antal ord med UBound -funktionenMsgBox "Antal ord" & UBound (MyArray) + 1Avsluta Sub |
En av farorna med denna ordräkningskod är att den kommer att kastas av dubbla mellanslag och ledande och bakre mellanslag. Om dessa finns, kommer de att räknas som extra ord, och antalet ord kommer att sluta som felaktiga.
Koden använder funktionerna Ersätt och Trim för att ta bort dessa extra mellanslag.
Den sista kodraden visar antalet ord som hittats med hjälp av UBound -funktionen för att få det maximala elementnumret i matrisen och sedan öka det med 1. Detta beror på att det första arrayelementet börjar på noll.
Dela upp en adress i kalkylbladsceller
E -postadresser är ofta långa textsträngar med kommaavgränsare. Du kanske vill dela upp varje del av adressen i en separat cell.
| 123456789101112131415 | Sub AddressExample ()'Skapa variablerDim MyArray () As String, MyString As String, N As Integer'Ställ in sträng med Microsoft Corporation AddressMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Använd delningsfunktionen för att dela strängen med en kommaavgränsareMyArray = Split (MyString, ",")'Rensa kalkylbladetActiveSheet.UsedRange.Clear'iterera genom matrisenFör N = 0 Till UBound (MyArray)'Placera varje delning i den första kolumnen i kalkylbladetOmråde ("A" och N + 1). Värde = MyArray (N)Nästa NAvsluta Sub |
Om du kör den här koden används kommadelaren för att placera varje rad i adressen i en separat cell:
Om du bara ville returnera postnumret (sista arrayelementet) kan du använda koden:
| 123456789101112 | Sub AddressZipCodeExample ()'Skapa variablerDim MyArray () As String, MyString As String, N As Integer, Temp As String'Ställ in sträng med Microsoft Corporation AddressMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Använd delningsfunktionen för att dela strängen med en kommaavgränsareMyArray = Split (MyString, ",")'Rensa kalkylbladetActiveSheet.UsedRange.Clear'Sätt postnummer vid cell A1Område ("A1"). Värde = MyArray (UBound (MyArray))Avsluta Sub |
Detta kommer bara att använda det sista elementet i arrayen, som hittas med hjälp av UBound -funktionen.
Å andra sidan kanske du vill se alla rader i en cell så att de kan skrivas ut på en adressetikett:
| 1234567891011121314151617 | Sub AddressExample ()'Skapa variablerDim MyArray () As String, MyString As String, N As Integer, Temp As String'Ställ in sträng med Microsoft Corporation AddressMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Använd delningsfunktionen för att dela strängen med en kommaavgränsareMyArray = Split (MyString, ",")'Rensa kalkylbladetActiveSheet.UsedRange.Clear'iterera genom matrisenFör N = 0 Till UBound (MyArray)'placera varje arrayelement plus ett radmatningstecken i en strängTemp = Temp & MyArray (N) & vbLfNästa N'Lägg strängen på kalkylbladetOmråde ("A1") = TempAvsluta Sub |
Det här exemplet fungerar på samma sätt som det tidigare, förutom att det skapar en tillfällig sträng av alla arrayelement, men infogar ett radmatningstecken efter varje element.
Arbetsbladet kommer att se ut så här efter att koden har körts:
Dela sträng i kalkylblad
Du kan kopiera Split -matrisen till kalkylbladsceller <> med bara ett kommando:
| 12345678910 | Sub CopyToRange ()'Skapa variablerDim MyArray () Som sträng, MyString som sträng'Provsträng med mellanslagMyString = "En, två, tre, fyra, fem, sex"'Använd delningsfunktionen för att dela upp strängens komponentdelarMyArray = Split (MyString, ",")'Kopiera matrisen till kalkylbladetOmråde ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Avsluta Sub |
När den här koden har körts ser ditt kalkylblad ut så här:
Skapa en ny funktion för att tillåta splittring från en given punkt
Parametern Limit i Split -funktionen låter dig bara ange en övre gräns där du vill att delningen ska stoppas. Det börjar alltid från början av strängen.
Det skulle vara mycket användbart att ha en liknande funktion där du kan ange startpunkten för delningen inom strängen och antalet delningar som du vill se från den punkten och framåt. Det kommer också bara att extrahera de delningar som du har angett i arrayen, snarare än att ha ett enormt strängvärde som det sista elementet i arrayen.
Du kan enkelt bygga en funktion (kallad SplitSlicer) själv i VBA för att göra detta:
| 123456789101112131415161718192021222324 | Funktion SplitSlicer (mål som sträng, del som sträng, start som heltal, N som heltal)'Skapa matrisvariabelDim MyArray () Som sträng'Fånga delningen med hjälp av startvariabeln med avgränsarenMyArray = Dela (Target, Del, Start)‘Kontrollera om startparametern är större än antalet delningar - det kan orsaka problemOm Start> UBound (MyArray) + 1 Då"Visa fel och avsluta funktionenMsgBox "Startparametern är större än antalet tillgängliga delningar"SplitSlicer = MyArrayAvsluta funktionAvsluta om'Sätt in det sista arrayelementet i strängenMål = MyArray (UBound (MyArray))'Dela strängen med N som gränsMyArray = Dela (Target, Del, N)'Kontrollera att den övre gränsen är större än noll eftersom koden tar bort det sista elementetOm UBound (MyArray)> 0 Då'Använd ReDim för att ta bort det sista elementet i matrisenReDim Preserve MyArray (UBound (MyArray) - 1)Avsluta om'Returnera den nya matrisenSplitSlicer = MyArrayAvsluta funktion |
Denna funktion är byggd med fyra parametrar:
- Mål - string - det här är inmatningssträngen som du vill dela
- Del - sträng eller icke -utskrivbart tecken - detta är avgränsningstecknet som du använder t.ex. komma, kolon
- Start - nummer - detta är startdelningen för din skiva
- N - nummer - detta är antalet delningar som du vill göra inom din skiva
Ingen av dessa parametrar är valfria eller har standardvärden, men du kan arbeta in det i koden för funktionen om du vill förlänga den ytterligare.
Funktionen använder delningsfunktionen för att skapa en array med parametern Start som gräns. Detta innebär att matriselementen håller delarna upp till startparametern, men resten av strängen blir det sista elementet och delas inte.
Det sista elementet i arrayen överförs tillbaka till en sträng med UBound -funktionen för att bestämma vilket element detta är.
Strängen delas sedan upp igen i arrayen med N som gränsvariabel. Detta innebär att delningar kommer att göras för strängen upp till position N, varefter resten av strängen kommer att bilda det sista elementet i gruppen.
ReDim -satsen används för att ta bort det sista elementet eftersom vi bara vill ha de specifika elementen kvar i matrisen. Observera att Preserve -parametern används, annars går alla data i arrayen förlorade.
Den nya matrisen returneras sedan till koden som den anropades från.
Observera att koden är "felskyddad". Användare kommer ofta att göra konstiga saker som du inte tänkte på. Om de till exempel försöker använda funktionen med parametern Start eller N större än det tillgängliga antalet delningar i strängen, kommer detta sannolikt att få funktionen att misslyckas.
Kod ingår för att kontrollera startvärdet, och också för att se till att det finns ett element som kan tas bort när ReDim -satsen används på matrisen.
Här är koden för att testa funktionen:
| 123456789101112 | Sub TestSplitSlicer ()'Skapa variablerDim MyArray () Som sträng, MyString som sträng'Definiera samplingssträng med kommaavgränsareMyString = "En, två, tre, fyra, fem, sex, sju, åtta, nio, tio"'Använd Splitslicer -funktionen för att definiera ny matrisMyArray = SplitSlicer (MyString, ",", 4, 3)'Rensa det aktiva arketActiveSheet.UsedRange.Clear'Kopiera matrisen till kalkylbladetOmråde ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)Avsluta Sub |
Kör den här koden så ser ditt kalkylblad ut så här:
