✅ Excel VBA InStr-Funktion - mit Beispielen erklärt

Inhaltsverzeichnis

Excel VBA InStr-Funktion

Gestern habe ich eine E-Mail von einem meiner Leser bekommen - Juni.

Sie wollte wissen, wie man das fette Schriftformat auf einen bestimmten Teil einer Zeichenfolge innerhalb einer Zelle anwendet. Wenden Sie beispielsweise das fette Format nur auf das Wort „Hello“ aus „Hello World“ an.

Und sie wollte dies für Hunderte von Zellen gleichzeitig tun.

Da es in Excel keine eingebauten Funktionen gibt, die dies tun können, habe ich ein einfaches Makro erstellt, das die Excel VBA InStr-Funktion (Sie werden in Beispiel 4 in diesem Tutorial sehen, wie Sie dies tun).

Aber zuerst sehen wir uns an, wie die Excel VBA InStr-Funktion funktioniert!

Excel VBA InStr-Funktion

In diesem Tutorial werde ich die Verwendung der InStr-Funktion in Excel VBA erklären und einige praktische Beispiele sehen, wo sie verwendet werden kann.

Excel VBA InStr-Funktion - Einführung

Die InStr-Funktion findet die Position einer angegebenen Teilzeichenfolge innerhalb der Zeichenfolge und gibt die erste Position ihres Vorkommens zurück.

Wenn Sie beispielsweise die Position von "x" in "Excel" finden möchten, würde die Verwendung der Excel-VBA-InStr-Funktion 2 zurückgeben.

Syntax der InStr-Funktion

InStr( [Start], String1, String2, [Vergleichen])

[Start] - (optionales Argument) Dies ist ein ganzzahliger Wert, der der InStr-Funktion die Startposition mitteilt, von der aus sie mit der Suche beginnen soll. Wenn ich beispielsweise möchte, dass die Suche von vorne beginnt, gebe ich den Wert 1 ein. Wenn ich möchte, dass sie ab dem dritten Zeichen beginnt, verwende ich 3. Wenn ich es weglasse, wird der Standardwert 1 verwendet.
Zeichenfolge1 - Dies ist die Hauptzeichenfolge (oder die übergeordnete Zeichenfolge), in der Sie suchen möchten. Wenn Sie beispielsweise nach der Position von x in Excel suchen, wäre String 1 „Excel“.
String2 - Dies ist der gesuchte Teilstring. Wenn Sie beispielsweise nach der Position von x in Excel suchen, wäre String2 x.
[Vergleichen] - (optionales Argument) Sie können einen der folgenden drei Werte für das [compare]-Argument angeben:
- vbBinaryVergleichen - Dies würde einen Zeichen-für-Zeichen-Vergleich durchführen. Wenn Sie beispielsweise in „Excel“ nach „x“ suchen, wird 2 zurückgegeben, aber wenn Sie in „Excel“ nach „X“ suchen, wird 0 zurückgegeben, da X in Großbuchstaben steht. Sie können auch 0 anstelle von vbBinaryCompare verwenden. Wenn das Argument [Compare] weggelassen wird, wird dies als Standard verwendet.
- vbTextVergleichen - Dies würde einen Textvergleich machen. Wenn Sie beispielsweise in Excel nach „x“ oder „X“ suchen, wird in beiden Fällen 2 zurückgegeben. Dieses Argument ignoriert die Groß-/Kleinschreibung. Sie können auch 1 anstelle von vbTextCompare verwenden.
- vbDatenbankVergleichen - Dies wird nur für Microsoft Access verwendet. Es verwendet die Informationen in der Datenbank, um den Vergleich durchzuführen. Sie können auch 2 anstelle von vbDatabaseCompare verwenden.

Zusätzliche Hinweise zur Excel VBA InStr-Funktion:

InStr ist eine VBA-Funktion und keine Arbeitsblattfunktion. Dies bedeutet, dass Sie es nicht innerhalb des Arbeitsblatts verwenden können.
Wenn String2 (das ist der Teilstring, dessen Position Sie suchen) leer ist, würde die Funktion den Wert des [Start]-Arguments zurückgeben.
Wenn die InStr-Funktion die Teilzeichenfolge innerhalb der Hauptzeichenfolge nicht finden kann, würde sie 0 zurückgeben.

Sehen wir uns nun ein Beispiel für die Verwendung der Excel VBA InStr-Funktion an

Beispiel 1 - Finden der Position von Anfang an

In diesem Beispiel verwende ich die InStr-Funktion, um die Position von ‚V‘ in ‚Excel VBA‘ von Anfang an zu finden.

Der Code dafür wäre:

Sub FindFromBeginning() Dim Position As Integer Position = InStr(1, "Excel VBA", "V", vbBinaryCompare) MsgBox Position End Sub

Wenn Sie diesen Code ausführen, wird ein Meldungsfeld mit dem Wert 7 angezeigt, der die Position von "V" in der Zeichenfolge "Excel VBA" ist.

Beispiel 2 – Finden der Position vom Anfang des zweiten Wortes

Angenommen, ich möchte die Position von "the" im Satz finden - "Der schnelle braune Fuchs springt über den faulen Hund"

Ich möchte jedoch, dass die Suche ab dem zweiten Wort beginnt.

In diesem Fall müssen wir das Argument [Start] ändern, um sicherzustellen, dass es die Position angibt, an der das zweite Wort beginnt.

Hier ist der Code, der dies tut:

Sub FindFromSecondWord() Dim Position As Integer Position = InStr(4, "Der schnelle braune Fuchs springt über den faulen Hund", "the", vbBinaryCompare) MsgBox Position End Sub

Dieser Code zeigt die Messagebox mit dem Wert 32 an, da wir die Startposition als 4 angegeben haben. Daher ignoriert er das erste „The“ und findet das zweite „the“ im Satz.

Wenn Sie ihn dynamischer gestalten möchten, können Sie den Code so erweitern, dass er automatisch das erste Wort ignoriert.

Hier ist der erweiterte Code, der dies tut:

Sub FindFromSecondWord() Dim StartingPosition As Integer Dim Position As Integer StartingPosition = InStr(1, "Der schnelle braune Fuchs springt über den faulen Hund", " ", vbBinaryCompare) Position = InStr(StartingPosition, "Der schnelle braune Fuchs springt über den faulen dog", "the", vbBinaryCompare) MsgBox Position End Sub

Dieser Code findet zuerst die Position eines Leerzeichens und speichert sie in der Variablen StartingPosition.

Es verwendet dann diese Variable als Startposition, um nach dem Wort „the“ zu suchen.

Daher gibt es 32 zurück (das ist die Startposition von ‚the‘ nach dem ersten Wort).

Beispiel 3 – Ermitteln der Position von @ in der E-Mail-Adresse

Mit der Excel VBA InStr-Funktion können Sie ganz einfach eine benutzerdefinierte Funktion erstellen, um die Position von @ in einer E-Mail-Adresse zu finden.

Hier ist der Code zum Erstellen der benutzerdefinierten Funktion:

Funktion FindPosition(Ref As Range) As Integer Dim Position As Integer Position = InStr(1, Ref, "@") FindPosition = Position End Function

Jetzt können Sie diese benutzerdefinierte Funktion wie jede andere Arbeitsblattfunktion verwenden. Es nimmt einen Zellbezug als Eingabe und gibt Ihnen die Position von @ darin an.

Auf ähnliche Weise können Sie eine benutzerdefinierte Funktion erstellen, um die Position einer beliebigen Teilzeichenfolge innerhalb der Hauptzeichenfolge zu ermitteln.

Beispiel 4 – Hervorheben eines Teils von String innerhalb von Zellen

Dies ist die Abfrage, die von June gestellt wurde (mein Leser, der mich auch dazu inspiriert hat, dieses Tutorial zu schreiben).

Hier sind Beispieldaten im Format, die mir Juni geschickt hat:

Ihre Frage war, die Zahlen außerhalb der Klammer fett zu machen.

Hier ist der Code, den ich erstellt habe, der dies tut:

Sub Bold() Dim rCell As Range Dim Char As Integer für jede rCell In Selection CharCount = Len(rCell) Char = InStr(1, rCell, "(") rCell.Characters(1, Char - 1).Font.Bold = True Next rCell End Sub

Der obige Code verwendet die For Each-Schleife, um jede der Zellen in der Auswahl zu durchlaufen. Es identifiziert die Position des öffnenden Klammerzeichens mithilfe der InStr-Funktion. Es ändert dann die Schriftart des Textes vor der Klammer.

Um diesen Code zu verwenden, müssen Sie ein Modul im VB-Editor kopieren und einfügen.

Nachdem Sie den Code kopiert und eingefügt haben, wählen Sie die Zellen aus, in denen Sie diese Formatierung durchführen möchten, und führen Sie das Makro aus (wie unten gezeigt).

Möglicherweise gefallen Ihnen auch die folgenden Excel VBA-Tutorials: