VBA-Split-Funktion - Textzeichenfolge in Array aufteilen

Verwenden der VBA-Split-Funktion

Mit der VBA-Split-Funktion können Sie die Komponententeile aus einer Standardtextzeichenfolge heraustrennen, wobei jede Komponente ein bestimmtes Trennzeichen verwendet, z. ein Komma oder ein Doppelpunkt. Es ist einfacher zu verwenden, als Code zu schreiben, um nach den Trennzeichen in der Zeichenfolge zu suchen und dann die Werte zu extrahieren.

Es kann verwendet werden, wenn Sie eine Zeile aus einem durch Kommas getrennten Wert (CSV-Datei) einlesen oder eine Postadresse haben, die sich in einer Zeile befindet, aber Sie möchten, dass sie mehrere Zeilen umfasst.

Die Syntax lautet:

1	Ausdruck teilen, Trennzeichen[optional], limit[optional], vergleichen[optional]

Die VBA-Split-Funktion hat vier Parameter:

• Ausdruck - Die Textfolge, die Sie in verschiedene Teile aufteilen möchten.
• Trennzeichen (Optional)- Zeichenfolge oder nicht druckbares Zeichen - Definiert das Trennzeichen, das für die Aufteilung verwendet wird. Wenn kein Trennzeichen angegeben ist, wird der Standardwert eines Leerzeichens verwendet.
• Grenze (Optional) - number - Definiert, wie viele Splits gemacht werden. Wenn es leer ist, werden alle verfügbaren Aufteilungen innerhalb der Zeichenfolge vorgenommen. Wenn es auf 1 gesetzt ist, werden keine Splits durchgeführt. Grundsätzlich ermöglicht es Ihnen, eine bestimmte Anzahl von Werten beginnend am Anfang des Strings herauszutrennen, z.B. wo die Saite sehr lang ist und man nur die ersten drei Splits braucht.
• Vergleichen (Optional) - Wenn Ihr Trennzeichen ein Textzeichen ist, wird dies verwendet, um umzuschalten, ob beim Trennzeichen die Groß-/Kleinschreibung beachtet wird oder nicht. Die Werte sind vbBinaryCompare (Groß-/Kleinschreibung beachten) und vbTextCompare (Groß-/Kleinschreibung nicht beachten).

Die Funktion split gibt immer ein Array zurück.

Einfaches Beispiel für die Split-Funktion

123456789101112

Sub SplitExample()'Variablen definierenDim MyArray() As String, MyString As String, I als Variante'Beispielstring mit Leerzeichen als TrennzeichenMyString = "Eins Zwei Drei Vier"'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString)'durch das erstellte Array iterieren, um jeden Wert anzuzeigenFür jedes Ich in MyArrayMsgBox IAls nächstesEnd Sub

In diesem Beispiel wird kein Trennzeichen angegeben, da zwischen allen Wörtern ein Leerzeichen steht, sodass das Standardtrennzeichen (Leerzeichen) verwendet werden kann.

Das Array hat keine Dimensionen und wird als String festgelegt. Die Variable I, die in der For… Next-Schleife verwendet wird, muss als Variante dimensioniert werden.

Wenn dieser Code ausgeführt wird, werden vier Meldungsfelder angezeigt, eines für jeden der Splits, z. Eins zwei drei. Vier.

Beachten Sie, dass ein doppeltes Leerzeichen zwischen den Wörtern in der Zeichenfolge als Teilung gewertet wird, obwohl nichts darin enthalten ist. Dies ist möglicherweise nicht das Ergebnis, das Sie sehen möchten.

Sie können dieses Problem beheben, indem Sie die Funktion Ersetzen verwenden, um doppelte Leerzeichen durch ein einzelnes Leerzeichen zu ersetzen:

1	MyString = Ersetzen(MyString, " ", " ")

Ein nachgestelltes oder führendes Leerzeichen kann ebenfalls Probleme verursachen, indem es einen leeren Split erzeugt. Diese sind oft sehr schwer zu erkennen. Sie können diese überflüssigen Leerzeichen entfernen, indem Sie die Trim-Funktion verwenden:

1	MyString = Trim(MyString)

Verwenden der Split-Funktion mit einem Trennzeichen

Wir können als Trennzeichen ein Semikolon (;) verwenden. Dies wird häufig in E-Mail-Adress-Strings gefunden, um die Adressen zu trennen. Möglicherweise erhalten Sie eine E-Mail, die mit mehreren Kollegen geteilt wird, und Sie möchten in Ihrem Arbeitsblatt eine Liste mit den Empfängern anzeigen. Sie können die E-Mail-Adressen ganz einfach aus den E-Mail-Feldern „An“ oder „Kopieren“ in Ihren Code kopieren.

123456789101112131415

Sub SplitBySemicolonExample()'Variablen definierenDim MyArray() As String, MyString As String, I As Variant, N As Integer'Beispielstring mit Semikolon-TrennzeichenMyString = "[email protected];[email protected];[email protected];[email protected]"'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString, ";")'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'durch das Array iterierenFür N = 0 an UBound(MyArray)'Tragen Sie jede E-Mail-Adresse in die erste Spalte des Arbeitsblatts einRange("A" & N + 1).Wert = MyArray(N)Nächste NEnd Sub

Beachten Sie, dass eine For… Next-Schleife verwendet wird, um das Array zu durchlaufen. Das erste Element im Array beginnt immer bei Null, und die Funktion Upper Bound wird verwendet, um die maximale Anzahl von Elementen zu erhalten.

Nachdem Sie diesen Code ausgeführt haben, sieht Ihr Arbeitsblatt wie folgt aus:

Verwenden eines Grenzwertparameters in einer Split-Funktion

Der Parameter limit ermöglicht eine bestimmte Anzahl von Aufteilungen ab dem Anfang der Zeichenfolge. Leider können Sie keine Startposition oder einen Bereich von durchzuführenden Splits angeben, daher ist es ziemlich einfach. Sie können Ihren eigenen VBA-Code erstellen, um eine Funktion dafür zu erstellen, und dies wird später in diesem Artikel erläutert.

123456789101112131415

Sub SplitWithLimitExample()'Variablen erstellenDim MyArray() As String, MyString As String, I As Variant, N As Integer'Beispielstring mit KommatrennzeichenMyString = "Eins,Zwei,Drei,Vier,Fünf,Sechs"'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString, ",", 4)'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'Durch das Array iterierenFür N = 0 an UBound(MyArray)'Jede Teilung in die erste Spalte des Arbeitsblattes setzenRange("A" & N + 1).Wert = MyArray(N)Nächste NEnd Sub

Nachdem Sie diesen Code ausgeführt haben, sieht Ihr Arbeitsblatt wie folgt aus:

Nur die ersten drei Splitwerte werden separat angezeigt. Die letzten drei Werte werden als eine lange Zeichenfolge angezeigt und werden nicht geteilt.

Wenn Sie einen Grenzwert wählen, der größer ist als die Anzahl der Trennzeichen innerhalb einer Zeichenfolge, führt dies nicht zu einem Fehler. Der String wird in alle seine Bestandteile zerlegt, als wäre der Grenzwert nicht angegeben.

Verwenden des Vergleichsparameters in einer Split-Funktion

Der Parameter Compare bestimmt, ob beim Trennzeichen die Groß-/Kleinschreibung beachtet wird oder nicht. Dies gilt nicht, wenn die Trennzeichen Kommas, Semikolons oder Doppelpunkte sind.

Hinweis: Stattdessen können Sie immer die Option Text vergleichen <> oben in Ihrem Modul platzieren, um die Groß-/Kleinschreibung für das gesamte Modul zu vermeiden.

123456789101112131415

Sub SplitByCompareExample()'Variablen erstellenDim MyArray() As String, MyString As String, I As Variant, N As Integer'Beispielstring mit X-TrennzeichenMyString = "OneXTwoXTreexFourXFivexSix"'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString, "X", , vbBinaryCompare)'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'durch das Array iterierenFür N = 0 an UBound(MyArray)'Jede Teilung in die erste Spalte des Arbeitsblattes setzenRange("A" & N + 1).Wert = MyArray(N)Nächste NEnd Sub

In diesem Beispiel verwendet die aufzuteilende Zeichenfolge das Zeichen „X“ als Trennzeichen. In dieser Zeichenfolge gibt es jedoch eine Mischung aus Groß- und Kleinbuchstaben „X“. Der Vergleichsparameter in der Split-Funktion verwendet ein „X“-Großbuchstaben.

Wenn der Parameter Compare auf vbBinaryCompare gesetzt ist, werden die Kleinbuchstaben „x“ ignoriert und Ihr Arbeitsblatt sieht wie folgt aus:

Wenn der Vergleichsparameter auf vbTextCompare gesetzt ist, werden die Kleinbuchstaben „x“ in der Aufteilung verwendet und Ihr Arbeitsblatt sieht wie folgt aus:

Beachten Sie, dass der Wert in Zelle A6 abgeschnitten wird, da er ein kleines „x“-Zeichen enthält. Da bei der Aufteilung die Groß-/Kleinschreibung nicht beachtet wird, führt jedes Trennzeichen, das Teil einer Teilzeichenfolge ist, zu einer Aufteilung.

Dies ist ein wichtiger Punkt, den Sie bei der Verwendung eines Texttrennzeichens und von vbTextCompare beachten sollten. Sie können leicht mit dem falschen Ergebnis enden.

Verwenden von nicht druckbaren Zeichen als Trennzeichen

Sie können nicht druckbare Zeichen als Trennzeichen verwenden, z. B. einen Wagenrücklauf (einen Zeilenumbruch).

Hier verwenden wir den vbCr, um einen Wagenrücklauf anzugeben <>

123456789101112131415

Sub SplitByNonPrintableExample()'Variablen erstellenDim MyArray() As String, MyString As String, I As Variant, N As Integer'Beispielzeichenfolge mit Wagenrücklauf-TrennzeichenMyString = "Eins" & vbCr & "Zwei" & vbCr & "Drei" & vbCr & "Vier" & vbCr & "Fünf" & vbCr & "Sechs"'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString, vbCr, , vbTextCompare)'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'Durch das Array iterierenFür N = 0 an UBound(MyArray)'Jede Teilung in die erste Spalte des Arbeitsblattes setzenRange("A" & N + 1).Wert = MyArray(N)Nächste NEnd Sub

In diesem Beispiel wird eine Zeichenfolge mit vbCr (Wagenrücklaufzeichen) als Trennzeichen aufgebaut.

Wenn dieser Code ausgeführt wird, sieht Ihr Arbeitsblatt wie folgt aus:

Verwenden der Join-Funktion zum Umkehren einer Teilung

Die Join-Funktion fügt alle Elemente eines Arrays erneut zusammen, jedoch unter Verwendung eines angegebenen Trennzeichens. Wenn kein Trennzeichen angegeben ist, wird ein Leerzeichen verwendet.

123456789101112131415

Sub JoinExample()'Variablen erstellenDim MyArray() As String, MyString As String, I As Variant, N As IntegerZiel als String dimmen'Beispielstring mit KommatrennzeichenMyString = "Eins,Zwei,Drei,Vier,Fünf,Sechs"'MyString in Zelle A1 platzierenRange("A1").Value = MyString'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString, ",")'Verwenden Sie die Join-Funktion, um die ursprüngliche Zeichenfolge mit einem Semikolon-Trennzeichen neu zu erstellenZiel = Join(MyArray“,;“)'Platziere die Ergebniszeichenfolge in Zelle A2Bereich("A2").Wert = ZielEnd Sub

Dieser Code teilt eine Zeichenfolge mit Kommas als Trennzeichen in ein Array auf und fügt sie mit Semikolon-Trennzeichen wieder zusammen.

Nachdem Sie diesen Code ausgeführt haben, sieht Ihr Arbeitsblatt so aus:

Zelle A1 enthält die ursprüngliche Zeichenfolge mit Komma-Trennzeichen und Zelle A2 enthält die neue verbundene Zeichenfolge mit Semikolon-Trennzeichen.

Verwenden der Split-Funktion zum Zählen von Wörtern

Wenn Sie bedenken, dass eine Zeichenfolgenvariable in Excel VBA bis zu 2 GB lang sein kann, können Sie die Aufteilungsfunktion verwenden, um Wörter in einem Textstück zu zählen. Natürlich macht Microsoft Word dies automatisch, aber dies kann für eine einfache Textdatei oder einen aus einer anderen Anwendung kopierten Text nützlich sein.

1234567891011121314

Sub NumberOfWordsExample()'Variablen erstellenDim MyArray() As String, MyString As String'Beispielstring mit Leerzeichen als TrennzeichenMyString = "Eins Zwei Drei Vier Fünf Sechs"'Alle doppelten Leerzeichen entfernenMyString = Ersetzen(MyString, " ", " ")'Entfernen Sie alle führenden oder abschließenden LeerzeichenMyString = Trim(MyString)'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString)'Anzahl der Wörter mit der UBound-Funktion anzeigenMsgBox "Anzahl der Wörter" & UBound(MyArray) + 1End Sub

Eine der Gefahren dieses Wortzählcodes besteht darin, dass er durch doppelte Leerzeichen und führende und nachfolgende Leerzeichen ausgelöst wird. Wenn diese vorhanden sind, werden sie als zusätzliche Wörter gezählt und die Wortzahl wird als ungenau gezählt.

Der Code verwendet die Funktionen Ersetzen und Trimmen, um diese zusätzlichen Leerzeichen zu entfernen.

Die letzte Codezeile zeigt die Anzahl der gefundenen Wörter an, indem die UBound-Funktion verwendet wird, um die maximale Elementnummer des Arrays zu ermitteln und diese dann um 1 zu erhöhen. Dies liegt daran, dass das erste Arrayelement bei Null beginnt.

Aufteilen einer Adresse in Arbeitsblattzellen

E-Mail-Adressen sind oft lange Textfolgen mit Kommas als Trennzeichen. Möglicherweise möchten Sie jeden Teil der Adresse in eine separate Zelle aufteilen.

123456789101112131415

UnteradresseBeispiel()'Variablen erstellenDim MyArray() As String, MyString As String, N As Integer'String mit Microsoft Corporation-Adresse einrichtenMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Verwenden Sie die Split-Funktion, um die Zeichenfolge mit einem Kommatrennzeichen zu teilenMyArray = Split(MyString, ",")'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'durch das Array iterierenFür N = 0 an UBound(MyArray)'Jede Teilung in die erste Spalte des Arbeitsblattes setzenRange("A" & N + 1).Wert = MyArray(N)Nächste NEnd Sub

Wenn Sie diesen Code ausführen, wird das Kommatrennzeichen verwendet, um jede Zeile der Adresse in eine separate Zelle einzufügen:

Wenn Sie nur die Postleitzahl (letztes Array-Element) zurückgeben möchten, können Sie den Code verwenden:

123456789101112

UnteradresseZipCodeBeispiel()'Variablen erstellenDim MyArray() As String, MyString As String, N As Integer, Temp As String'String mit Microsoft Corporation-Adresse einrichtenMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Verwenden Sie die Split-Funktion, um die Zeichenfolge mit einem Kommatrennzeichen zu teilenMyArray = Split(MyString, ",")'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'Geben Sie die Postleitzahl in Zelle A1 einRange("A1").Value = MyArray(UBound(MyArray))End Sub

Dadurch wird nur das letzte Element im Array verwendet, das mithilfe der UBound-Funktion gefunden wird.

Andererseits möchten Sie vielleicht alle Zeilen in einer Zelle sehen, damit sie auf ein Adressetikett gedruckt werden können:

1234567891011121314151617

UnteradresseBeispiel()'Variablen erstellenDim MyArray() As String, MyString As String, N As Integer, Temp As String'String mit Microsoft Corporation-Adresse einrichtenMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Verwenden Sie die Split-Funktion, um die Zeichenfolge mit einem Kommatrennzeichen zu teilenMyArray = Split(MyString, ",")'Löschen Sie das ArbeitsblattActiveSheet.UsedRange.Clear'durch das Array iterierenFür N = 0 an UBound(MyArray)'platziere jedes Array-Element plus ein Zeilenvorschub-Zeichen in einer ZeichenfolgeTemp = Temp & MyArray(N) & vbLfNächste N'Lege die Zeichenfolge auf das ArbeitsblattBereich("A1") = TempEnd Sub

Dieses Beispiel funktioniert genauso wie das vorherige, außer dass es eine temporäre Zeichenfolge aller Array-Elemente erstellt, jedoch nach jedem Element ein Zeilenvorschubzeichen einfügt.

Das Arbeitsblatt sieht nach der Ausführung des Codes wie folgt aus:

String in Arbeitsblattzellen aufteilen

Sie können das Split-Array mit nur einem Befehl in Arbeitsblattzellen <> kopieren:

12345678910

Unter CopyToRange()'Variablen erstellenDim MyArray() As String, MyString As String'Beispielstring mit Leerzeichen als TrennzeichenMyString = "Eins,Zwei,Drei,Vier,Fünf,Sechs"'Verwenden Sie die Split-Funktion, um die Bestandteile der Zeichenfolge aufzuteilenMyArray = Split(MyString, ",")'Kopiere das Array in das ArbeitsblattRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)End Sub

Wenn dieser Code ausgeführt wurde, sieht Ihr Arbeitsblatt wie folgt aus:

Erstellen einer neuen Funktion, um das Aufteilen von einem bestimmten Punkt zu ermöglichen

Mit dem Parameter Limit in der Split-Funktion können Sie nur eine Obergrenze festlegen, an der die Aufteilung beendet werden soll. Es beginnt immer am Anfang der Zeichenfolge.

Es wäre sehr nützlich, eine ähnliche Funktion zu haben, bei der Sie den Startpunkt der Teilung innerhalb der Zeichenfolge und die Anzahl der Teilungen angeben können, die Sie ab diesem Punkt sehen möchten. Es extrahiert auch nur die Splits, die Sie in das Array angegeben haben, anstatt einen enormen String-Wert als letztes Element im Array zu haben.

Sie können dazu ganz einfach eine Funktion (genannt SplitSlicer) in VBA selbst erstellen:

123456789101112131415161718192021222324

Funktion SplitSlicer(Target As String, Del As String, Start As Integer, N As Integer)'Array-Variable erstellenDim MyArray() As String'Erfassen Sie die Aufteilung mit der Startvariablen mit dem TrennzeichenMyArray = Split(Ziel, Entf, Start)‘Überprüfen Sie, ob der Startparameter größer als die Anzahl der Splits ist – dies kann zu Problemen führenWenn Start > UBound(MyArray) + 1 Dann‘Fehler anzeigen und Funktion verlassenMsgBox "Startparameter ist größer als Anzahl der verfügbaren Splits"SplitSlicer = MyArrayExit-FunktionEnde Wenn'Füge das letzte Array-Element in den String einZiel = MyArray(UBound(MyArray))'Teilen Sie die Zeichenfolge mit N als Grenzwert aufMyArray = Split(Target, Del, N)„Überprüfen Sie, ob die Obergrenze größer als Null ist, da der Code das letzte Element entfernt.Wenn UBound(MyArray) > 0 Then'Verwenden Sie ReDim, um das letzte Element des Arrays zu entfernenReDim Preserve MyArray(UBound(MyArray) - 1)Ende Wenn'Geben Sie das neue Array zurückSplitSlicer = MyArrayEndfunktion

Diese Funktion ist mit vier Parametern aufgebaut:

• Ziel - string - Dies ist die Eingabezeichenfolge, die Sie aufteilen möchten
• Del - Zeichenfolge oder nicht druckbares Zeichen - Dies ist das Trennzeichen, das Sie z.B. Komma, Doppelpunkt
• Start - Nummer - das ist der Startsplit für dein Slice
• n - number - Dies ist die Anzahl der Splits, die Sie in Ihrem Slice durchführen möchten

Keiner dieser Parameter ist optional oder hat Standardwerte, aber Sie können dies in den Code für die Funktion einarbeiten, wenn Sie ihn weiter erweitern möchten.

Die Funktion verwendet die Split-Funktion, um ein Array mit dem Start-Parameter als Limit zu erstellen. Dies bedeutet, dass die Array-Elemente die Aufteilungen bis zum Startparameter enthalten, der Rest der Zeichenfolge jedoch das letzte Element ist und nicht aufgeteilt wird.

Das letzte Element im Array wird mit der UBound-Funktion in einen String zurückübertragen, um zu bestimmen, um welches Element es sich handelt.

Der String wird dann wieder in das Array aufgeteilt, wobei N als Grenzvariable verwendet wird. Dies bedeutet, dass der String bis zur Position N aufgeteilt wird, wonach der Rest des Strings das letzte Element im Array bildet.

Die ReDim-Anweisung wird verwendet, um das letzte Element zu entfernen, da nur die spezifischen Elemente im Array verbleiben sollen. Beachten Sie, dass der Parameter Preserve verwendet wird, da sonst alle Daten im Array verloren gehen.

Das neue Array wird dann an den Code zurückgegeben, aus dem es aufgerufen wurde.

Beachten Sie, dass der Code „fehlersicher“ ist. Benutzer werden oft seltsame Dinge tun, die Sie nicht bedacht haben. Wenn sie beispielsweise versuchen, die Funktion mit einem Start- oder N-Parameter zu verwenden, der größer ist als die verfügbare Anzahl von Teilungen in der Zeichenfolge, führt dies wahrscheinlich dazu, dass die Funktion fehlschlägt.

Code ist enthalten, um den Start-Wert zu überprüfen und um sicherzustellen, dass ein Element entfernt werden kann, wenn die ReDim-Anweisung für das Array verwendet wird.

Hier der Code zum Testen der Funktion:

123456789101112

UntertestSplitSlicer()'Variablen erstellenDim MyArray() As String, MyString As String'Beispielstring mit Kommatrennzeichen definierenMyString = "Eins,Zwei,Drei,Vier,Fünf,Sechs,Sieben,Acht,Neun,Zehn"'Verwenden Sie die Splitslicer-Funktion, um ein neues Array zu definierenMyArray = SplitSlicer(MyString, ",", 4, 3)'Löschen Sie das aktive BlattActiveSheet.UsedRange.Clear'Kopiere das Array in das ArbeitsblattRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)End Sub

Führen Sie diesen Code aus und Ihr Arbeitsblatt sieht so aus: