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 mit der VBA-Split-Funktion verschiedene Teile der Adresse abrufen, die durch ein Komma getrennt sind (in diesem Fall das Trennzeichen).

SPLIT ist eine eingebaute Zeichenfolgenfunktion in Excel VBA, mit der Sie eine Textzeichenfolge basierend auf dem Trennzeichen aufteilen können.,

Dieses Tutorial behandelt:

Excel VBA-SPLIT-Funktion – Syntax 

Split ( Expression, , , )
  • Expression: Dies ist die Zeichenfolge, die Sie teilen möchten, basierend auf dem Trennzeichen. Im Falle des Adressbeispiels wäre beispielsweise die gesamte Adresse der „Ausdruck“. Falls dies eine Zeichenfolge mit null Länge ( „“ ) 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 ‚Expression‘ aufzuteilen., Im Falle unseres Adressbeispiels 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 mit null Länge ( „“ ) angeben, wird die gesamte Zeichenfolge ‚Expression‘ von der Funktion zurückgegeben.
  • Limit: Dies ist ein optionales argument. Hier geben Sie die Gesamtzahl der Teilzeichenfolgen an, die Sie zurückgeben möchten. Wenn Sie beispielsweise nur die ersten drei Teilzeichenfolgen aus dem Argument ‚Expression‘ zurückgeben möchten, wäre dies 3., Wenn Sie dieses Argument nicht angeben, ist der Standardwert -1, der alle Teilzeichenfolgen zurückgibt.
  • Vergleichen: Dies ist ein optionales Argument. Hier geben Sie die Art des Vergleichs an, den die SPLIT-Funktion bei der Auswertung der Teilzeichenfolgen ausführen soll. Die folgenden Optionen stehen zur Verfügung:
    • Wenn Compare 0 ist: Dies ist ein binärer Vergleich. Dies bedeutet, dass, wenn Ihr Trennzeichen eine Textzeichenfolge ist (sagen wir ABC), dies Groß-und Kleinschreibung beachten würde. ‚ABC‘ wäre nicht gleich ‚abc‘.
    • Wenn Vergleichen ist 1: Dies ist ein Text vergleich., Dies bedeutet, dass, wenn Ihr Trennzeichen eine Textzeichenfolge ist (sagen wir ABC), selbst wenn Sie ‚abc‘ in der Zeichenfolge ‚Expression‘ 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

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 abzurufen.,

Der folgende Code würde Folgendes bedeuten:

Sub SplitWords()Dim TextStrng As StringDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result() = Split(TextStrng)End Sub

Während der Code nichts Nützliches tut, hilft er Ihnen zu verstehen, was die Split-Funktion in VBA tut.

Die Split-Funktion teilt die Textzeichenfolge auf und weist jedes Wort dem Ergebnisarray zu.

Also in diesem Fall:

  • Result(0) speichert den Wert „The“
  • Result(1) speichert den Wert „Quick“
  • Result (2) speichert den Wert „Brown“ und so weiter.

In diesem Beispiel haben wir nur das erste Argument angegeben-nämlich den zu teilenden Text., Da kein Trennzeichen angegeben wurde, nimmt es Leerzeichen als Standardtrennzeichen.

Wichtiger Hinweis:

  1. Die VBA-SPLIT-Funktion gibt ein Array zurück, das von der Basis 0 ausgeht.
  2. Wenn das Ergebnis der SPLIT-Funktion einem Array zugewiesen wird, muss dieses Array als String-Datentyp deklariert werden. Wenn Sie es als Variantendatentyp deklarieren,wird ein Typfehlerübereinstimmungsfehler 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 erhalten. Der Trick hier ist, 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 Wortzahl anzeigen:

Sub WordCount()Dim TextStrng As StringDim WordCount As IntegerDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result = Split(TextStrng)WordCount = UBound(Result()) + 1MsgBox "The Word Count is " & WordCountEnd Sub

In diesem Fall teilt uns die UBound-Funktion die Obergrenze des Arrays mit (dh die maximale Anzahl von Elementen, die das Array hat)., Da die Basis des Arrays 0 ist, wird 1 hinzugefügt, 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 Wortzahl zurückgibt.

Der folgende Code erstellt diese Funktion:

Nach der Erstellung können Sie die WordCount-Funktion wie jede andere reguläre Funktion verwenden.

Diese Funktion behandelt auch führende, nachgestellte und doppelte Leerzeichen zwischen Wörtern. Dies wurde durch die Verwendung der TRIM-Funktion im VBA-Code ermöglicht.,

Falls Sie mehr darüber erfahren möchten, wie diese Formel funktioniert, um die Anzahl der Wörter in einem Satz zu zählen, oder mehr über eine Nicht-VBA-Formel zum Abrufen der Wortzahl erfahren möchten, lesen Sie dieses Tutorial.

Beispiel 3-Mit einem anderen Trennzeichen als Leerzeichen

In den beiden vorherigen Beispielen haben wir nur ein Argument in der SPLIT-Funktion verwendet, und der Rest waren die Standardargumente.

Wenn Sie ein anderes Trennzeichen verwenden, müssen Sie dies in der GETEILTEN 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.

Im obigen Code habe ich die For Next-Schleife verwendet, um jedes Element des Arrays ‚Result‘ zu durchlaufen Weisen Sie es der Variablen ‚displayText‘ zu.

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 spezifiziere, wird jede Instanz des Trennzeichens zum Teilen der Zeichenfolge verwendet.,

Aber wenn ich 3 als Limit spezifiziere, wird die Zeichenfolge nur in drei Teile geteilt.

Wenn ich beispielsweise die folgende Adresse habe:

2703 Winifred Way, Indianapolis, Indiana, 46204

Ich kann die Split-Funktion in VBA verwenden, um diese Adresse in drei Teile zu unterteilen.

Es teilt die ersten beiden basierend auf dem Kommatrennzeichen auf 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:

Eine der praktischen Anwendungen könnte sein, wenn Sie eine einzelne Zeilenadresse in das im Meldungsfeld angezeigte Format aufteilen möchten. Dann können Sie eine benutzerdefinierte Funktion erstellen, die die Adresse in drei Teile unterteilt zurückgibt (mit jedem Teil in einer neuen Zeile).

Der folgende Code würde dies tun:

Sobald Sie diesen Code im Modul haben, können Sie die Funktion (ThreePartAddress) in der Arbeitsmappe wie jede andere Excel-Funktion verwenden.,

Diese Funktion verwendet ein Argument – die Zellreferenz mit der Adresse.

Beachten Sie, dass Sie das Wrap-Textformat auf die Zellen anwenden müssen, damit die resultierende Adresse in drei verschiedenen Zeilen angezeigt wird (sie befindet sich auf der Registerkarte Start in der Ausrichtungsgruppe). Wenn das Format „Text umbrechen“ nicht aktiviert ist, wird die gesamte Adresse als eine einzige Zeile angezeigt.

Beispiel 5-Holen Sie sich den Namen der Stadt von der Adresse

Mit 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 es sich um ein Array handelt, kann ich einen bestimmten Teil dieses Arrays anzeigen oder zurückgeben.

Unten finden Sie einen Code für eine benutzerdefinierte Funktion, in dem Sie eine Zahl angeben können, die dieses Element aus dem Array zurückgibt. Wenn ich beispielsweise den Statusnamen haben möchte, kann ich 3 angeben (da es sich um das dritte Element im Array handelt).,

Die obige Funktion verwendet zwei Argumente, die Zellenreferenz mit der Adresse und die Elementnummer, die Sie zurückgeben möchten. Die Split-Funktion teilt die Adresselemente auf und weist sie der Ergebnisvariablen zu.

Dann wird die Elementnummer zurückgegeben, 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.

Diese benutzerdefinierte Formel ist am besten geeignet, wenn Sie ein konsistentes Format in allen Adressen haben – dh die Stadt wird immer nach den beiden Kommas erwähnt., Wenn die Daten nicht konsistent sind, erhalten Sie nicht das gewünschte Ergebnis.

Falls Sie den Stadtnamen möchten, können Sie 2 als zweites Argument verwenden. Wenn Sie eine Zahl verwenden, die höher als die Gesamtzahl der Elemente ist, wird der WERT #zurückgegeben! Fehlermeldung.

Sie können den Code wie unten gezeigt weiter vereinfachen:

Im obigen Code wird anstelle der Ergebnisvariablen nur die angegebene Elementnummer zurückgegeben.

Wenn Sie also Split(„Guten Morgen“)(0) haben, wird nur das erste Element zurückgegeben, das „Gut“ist.,

In ähnlicher Weise gibt es im obigen Code nur die angegebene Elementnummer zurück.

Sie können auch die folgenden Excel-Tutorials mögen:

  • Excel VBA InStr-Funktion-Erklärt mit Beispielen.
  • So sortieren Sie Daten in Excel mit VBA (Eine Schritt-für-Schritt-Anleitung).
  • 7 Erstaunliche Dinge, die Excel Text to Columns für Sie tun kann.
  • So erhalten Sie die Wortzahl in Excel.
  • VBA die TRIM-Funktion.