Wenn Sie mit VBA in Excel arbeiten, müssen Sie möglicherweise eine Zeichenfolge basierend auf einem Trennzeichen in verschiedene Teile aufteilen.
Wenn Sie beispielsweise eine Adresse haben, können Sie die VBA-Split-Funktion verwenden, um verschiedene Teile der Adresse zu erhalten, die durch ein Komma (das in diesem Fall das Trennzeichen wäre) getrennt sind.
SPLIT ist eine integrierte Zeichenfolgenfunktion in Excel VBA, mit der Sie eine Textzeichenfolge basierend auf dem Trennzeichen teilen können.
Excel VBA SPLIT-Funktion - Syntax
Teilen ( Ausdruck, [Trennzeichen], [Limit], [Vergleichen] )
- Ausdruck: Dies ist die Zeichenfolge, die Sie basierend auf dem Trennzeichen teilen möchten. Im Adressbeispiel wäre beispielsweise die gesamte Adresse der „Ausdruck“. Falls dies eine Zeichenfolge der Länge Null ist (""), würde die SPLIT-Funktion ein leeres Array zurückgeben.
- Trennzeichen: Dies ist ein optionales Argument. Dies ist das Trennzeichen, das verwendet wird, um das Argument "Ausdruck" aufzuteilen. In unserem Adressbeispiel ist ein Komma ein Trennzeichen, das verwendet wird, um die Adresse in verschiedene Teile aufzuteilen. Wenn Sie dieses Argument nicht angeben, wird ein Leerzeichen als Standardtrennzeichen betrachtet. Wenn Sie eine Zeichenfolge der Länge Null ("") angeben, wird die gesamte Zeichenfolge "Ausdruck" von der Funktion zurückgegeben.
- Grenze: Dies ist ein optionales Argument. Hier geben Sie die Gesamtzahl der Teilstrings an, die Sie zurückgeben möchten. Wenn Sie beispielsweise nur die ersten drei Teilzeichenfolgen des Arguments „Ausdruck“ zurückgeben möchten, wäre dies 3. Wenn Sie dieses Argument nicht angeben, ist der Standardwert -1, wodurch alle Teilzeichenfolgen zurückgegeben werden.
- Vergleichen: Dies ist ein optionales Argument. Hier geben Sie die Art des Vergleichs an, den die SPLIT-Funktion bei der Auswertung der Teilstrings durchführen soll. Folgende Optionen stehen zur Verfügung:
- Wenn Vergleich 0 ist: Dies ist ein binärer Vergleich. Das heißt, wenn Ihr Trennzeichen eine Textzeichenfolge ist (sagen wir ABC), dann muss die Groß-/Kleinschreibung beachtet werden. „ABC“ wäre nicht gleich „abc“.
- Wenn Vergleichen 1 ist: Dies ist ein Textvergleich. Dies bedeutet, dass, wenn Ihr Trennzeichen eine Textzeichenfolge ist (sagen wir ABC), selbst wenn Sie „abc“ in der Zeichenfolge „Ausdruck“ haben, es als Trennzeichen betrachtet wird.
Nachdem wir nun die Grundlagen der SPLIT-Funktion behandelt haben, sehen wir uns einige praktische Beispiele an.
Beispiel 1 – Teilen Sie die Wörter in einem Satz auf
Angenommen, ich habe den Text - "Der schnelle braune Fuchs springt über den faulen Hund".
Ich kann die SPLIT-Funktion verwenden, um jedes Wort dieses Satzes als separates Element in einem Array einzufügen.
Der folgende Code würde dazu:
Sub SplitWords() Dim TextStrng As String Dim Result() As String TextStrng = "Der schnelle braune Fuchs springt über den faulen Hund" Result() = Split(TextStrng) End Sub
Der Code macht zwar nichts Nützliches, hilft Ihnen jedoch zu verstehen, was die Split-Funktion in VBA tut.
Die Split-Funktion teilt die Textzeichenfolge und weist jedes Wort dem Ergebnis-Array zu.
Also in diesem Fall:
- Ergebnis(0) speichert den Wert „The“
- Ergebnis(1) speichert den Wert „Quick“
- Result(2) speichert den Wert „Brown“ und so weiter.
In diesem Beispiel haben wir nur das erste Argument angegeben - den zu teilenden Text. Da kein Trennzeichen angegeben wurde, werden Leerzeichen als Standardtrennzeichen verwendet.
Wichtiger Hinweis:
- Die VBA SPLIT-Funktion gibt ein Array zurück, das mit der Basis 0 beginnt.
- Wenn das Ergebnis der SPLIT-Funktion einem Array zugewiesen wird, muss dieses Array als String-Datentyp deklariert werden. Wenn Sie ihn als Variant-Datentyp deklarieren, wird ein Typkonfliktfehler angezeigt). Beachten Sie im obigen Beispiel, dass ich Result() als String-Datentyp deklariert habe.
Beispiel 2 – Zählen Sie die Anzahl der Wörter in einem Satz
Sie können die SPLIT-Funktion verwenden, um die Gesamtzahl der Wörter in einem Satz zu ermitteln. Der Trick besteht darin, die Anzahl der Elemente im Array zu zählen, die Sie erhalten, wenn Sie den Text teilen.
Der folgende Code würde ein Meldungsfeld mit der Wortanzahl anzeigen:
Sub WordCount() Dim TextStrng As String Dim WordCount As Integer Dim Result() As String TextStrng = "Der schnelle braune Fuchs springt über den faulen Hund" Result = Split(TextStrng) WordCount = UBound(Result()) + 1 MsgBox "The Word Count ist " & WordCount End Sub
In diesem Fall teilt uns die UBound-Funktion die obere Grenze des Arrays mit (d. h. die maximale Anzahl von Elementen, die das Array hat). Da die Basis des Arrays 0 ist, wird 1 addiert, um die Gesamtzahl der Wörter zu erhalten.
Sie können einen ähnlichen Code verwenden, um eine benutzerdefinierte Funktion in VBA zu erstellen, die den Text als Eingabe verwendet und die Wortanzahl zurückgibt.
Der folgende Code erstellt diese Funktion:
Funktion WordCount(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1 End Function
Nach der Erstellung können Sie die WordCount-Funktion wie jede andere reguläre Funktion verwenden.
Diese Funktion verarbeitet auch führende, nachgestellte und doppelte Leerzeichen zwischen Wörtern. Dies wurde durch die Verwendung der TRIM-Funktion im VBA-Code ermöglicht.
Wenn Sie mehr darüber erfahren möchten, wie diese Formel zum Zählen der Wörter in einem Satz funktioniert, oder eine Nicht-VBA-Formelmethode zum Ermitteln der Wortanzahl kennenlernen möchten, sehen Sie sich dieses Tutorial an.
Beispiel 3 – Verwenden eines anderen Trennzeichens als Leerzeichen
In den beiden vorherigen Beispielen haben wir nur ein Argument in der SPLIT-Funktion verwendet, der Rest waren die Standardargumente.
Wenn Sie ein anderes Trennzeichen verwenden, müssen Sie dieses in der SPLIT-Formel angeben.
Im folgenden Code gibt die SPLIT-Funktion ein Array basierend auf einem Komma als Trennzeichen zurück und zeigt dann eine Nachricht mit jedem Wort in einer separaten Zeile an.
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(Result()) To UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Next i MsgBox DisplayText End Sub
Im obigen Code habe ich die For Next-Schleife verwendet, um jedes Element des 'Result'-Arrays zu durchlaufen und es der 'DisplayText'-Variablen zuzuweisen.
Beispiel 4 – Teilen Sie eine Adresse in drei Teile
Mit der SPLIT-Funktion können Sie angeben, wie viele Splits Sie erhalten möchten. Wenn ich beispielsweise nichts angebe, wird jede Instanz des Trennzeichens verwendet, um die Zeichenfolge aufzuteilen.
Aber wenn ich 3 als Grenze angebe, wird die Zeichenfolge nur in drei Teile geteilt.
Wenn ich zum Beispiel folgende Adresse habe:
2703 Winifred Way, Indianapolis, Indiana, 46204
Ich kann die Split-Funktion in VBA verwenden, um diese Adresse in drei Teile aufzuteilen.
Es teilt die ersten beiden basierend auf dem Kommatrennzeichen und der verbleibende Teil wird zum dritten Element des Arrays.
Der folgende Code würde die Adresse in drei verschiedenen Zeilen in einem Meldungsfeld anzeigen:
Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Ergebnis = Split(TextStrng, ",", 3) For i = LBound(Result( )) Zu UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Next i MsgBox DisplayText End Sub
Eine praktische Anwendung könnte dies sein, wenn Sie eine einzeilige Adresse in das im Meldungsfeld angezeigte Format unterteilen möchten. Dann können Sie eine benutzerdefinierte Funktion erstellen, die die Adresse in drei Teile unterteilt zurückgibt (wobei jeder Teil in einer neuen Zeile steht).
Der folgende Code würde dies tun:
Funktion 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(Result(i)) & vbNewLine Next i ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1) End Function
Sobald Sie diesen Code im Modul haben, können Sie die Funktion (ThreePartAddress) in der Arbeitsmappe wie jede andere Excel-Funktion verwenden.
Diese Funktion benötigt ein Argument - den Zellbezug mit der Adresse.
Beachten Sie, dass Sie das Zeilenumbruchformat auf die Zellen anwenden müssen, damit die resultierende Adresse in drei verschiedenen Zeilen angezeigt wird (es befindet sich auf der Registerkarte Start in der Gruppe Ausrichtung). Wenn das Format „Text umbrechen“ nicht aktiviert ist, sehen Sie die gesamte Adresse als eine einzige Zeile.
Beispiel 5 – Holen Sie sich den Stadtnamen aus der Adresse
Mit der Split-Funktion in VBA können Sie angeben, welchen Teil des resultierenden Arrays Sie verwenden möchten.
Angenommen, ich teile die folgende Adresse basierend auf dem Komma als Trennzeichen auf:
2703 Winifred Way, Indianapolis, Indiana, 46204
Das resultierende Array würde wie folgt aussehen:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Da dies ein Array ist, kann ich einen bestimmten Teil dieses Arrays anzeigen oder zurückgeben.
Unten ist ein Code für eine benutzerdefinierte Funktion, in der Sie eine Zahl angeben können und dieses Element aus dem Array zurückgibt. Wenn ich beispielsweise den Zustandsnamen möchte, kann ich 3 angeben (da es das dritte Element im Array ist).
Funktion ReturnNthElement(CellRef As Range, ElementNumber As Integer) Dim Result() As String Result = Split(CellRef, ",") ReturnNthElement = Result(ElementNumber - 1) End Function
Die obige Funktion benötigt zwei Argumente, den Zellbezug mit der Adresse und die Elementnummer, die Sie zurückgeben möchten. Die Funktion Split teilt die Adresselemente und weist sie der Variablen Result zu.
Dann gibt es die Elementnummer zurück, die Sie als zweites Argument angegeben haben. Beachten Sie, dass ElementNumber-1 verwendet wird, um den richtigen Teil der Adresse zurückzugeben, da die Basis 0 ist.
Wenn Sie den Städtenamen wünschen, können Sie 2 als zweites Argument verwenden. Falls Sie eine Zahl verwenden, die höher ist als die Gesamtzahl der Elemente, wird #VALUE! Error.
Sie können den Code wie unten gezeigt weiter vereinfachen:
Funktion ReturnNthElement(CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split(CellRef, ",")(ElementNumber - 1) End Function
Im obigen Code wird, anstatt die Ergebnisvariable zu verwenden, nur die angegebene Elementnummer zurückgegeben.
Wenn Sie also Split("Guten Morgen")(0) haben, wird nur das erste Element zurückgegeben, das "Gut" ist.
Ebenso gibt es im obigen Code nur die angegebene Elementnummer zurück.
