Wie man reguläre Ausdrücke (Regex) in Microsoft Excel verwendet, sowohl in der Zelle als auch in Schleifen

Question

Wie kann ich reguläre Ausdrücke in Excel verwenden und von der leistungsstarken Rasterstruktur von Excel zur Datenmanipulation profitieren?

• In-Zellen-Funktion, um ein übereinstimmendes Muster oder einen ersetzten Wert in einem String zurückzugeben.
• Sub zum Durchlaufen einer Spalte von Daten und Extrahieren von Übereinstimmungen in benachbarte Zellen.
• Welche Einrichtung ist erforderlich?
• Was sind die speziellen Zeichen von Excel für reguläre Ausdrücke?

---

Ich verstehe, dass Regex nicht für viele Situationen ideal ist (Verwenden oder nicht verwenden von regulären Ausdrücken?), da Excel Left, Mid, Right, Instr Befehle für ähnliche Manipulationen verwenden kann.

Answer 1

Um reguläre Ausdrücke direkt in Excel-Formeln zu verwenden, kann die folgende UDF (Benutzerdefinierte Funktion) hilfreich sein. Sie stellt die reguläre Ausdrucksfunktionalität mehr oder weniger direkt als Excel-Funktion zur Verfügung.

Wie es funktioniert

Es nimmt 2-3 Parameter entgegen.

1. Ein Text, auf den der reguläre Ausdruck angewendet werden soll.
2. Ein regulärer Ausdruck.
3. Ein Formatstring, der angibt, wie das Ergebnis aussehen soll. Er kann $0, $1, $2 und so weiter enthalten. $0 ist der gesamte Treffer, $1 und höher entsprechen den entsprechenden Treffern in dem regulären Ausdruck. Standardmäßig $0.

Einige Beispiele

Extrahieren einer E-Mail-Adresse:

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")

Ergebnis: some@email.com

Mehrere Teilstrings extrahieren:

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Ergebnis: E-Mail: some@email.com, Name: Peter Gordon

Ein kombiniertes Gedichtstück in einer einzigen Zelle in mehrere Zellen zerlegen:

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)

Ergebnis: Peter Gordon some@email.com ...

Wie man es verwendet

Um diese UDF zu verwenden, führen Sie folgendes aus (basierend grob auf dieser Microsoft-Seite. Sie haben dort einige gute zusätzliche Informationen!):

1. In Excel in einer makroaktivierten Datei ('.xlsm') drücken Sie ALT+F11, um den Microsoft Visual Basic for Applications Editor zu öffnen.

2. Fügen Sie eine VBA-Referenz zur RegExp-Bibliothek hinzu (unverschämterweise kopiert von Portland Runners++-Antwort):

   1. Klicken Sie auf Extras -> Verweise (entschuldigen Sie bitte den deutschen Screenshot)
   2. Suchen Sie Microsoft VBScript Regular Expressions 5.5 in der Liste und aktivieren Sie das Kontrollkästchen daneben.
   3. Klicken Sie auf OK.

3. Klicken Sie auf Modul einfügen. Wenn Sie Ihrem Modul einen anderen Namen geben, stellen Sie sicher, dass das Modul nicht denselben Namen wie die unten stehende UDF hat (z.B. verursacht die Benennung des Moduls Regex und der Funktion regex Fehler #NAME!).

   

4. Fügen Sie in das große Textfenster in der Mitte Folgendes ein:

   Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
       Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
       Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
       Dim replaceNumber As Integer
   
       With inputRegexObj
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = matchPattern
       End With
       With outputRegexObj
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = "\$(\d+)"
       End With
       With outReplaceRegexObj
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
       End With
   
       Set inputMatches = inputRegexObj.Execute(strInput)
       If inputMatches.Count = 0 Then
           regex = False
       Else
           Set replaceMatches = outputRegexObj.Execute(outputPattern)
           For Each replaceMatch In replaceMatches
               replaceNumber = replaceMatch.SubMatches(0)
               outReplaceRegexObj.Pattern = "\$" & replaceNumber
   
               If replaceNumber = 0 Then
                   outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
               Else
                   If replaceNumber > inputMatches(0).SubMatches.Count Then
                       'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                       regex = CVErr(xlErrValue)
                       Exit Function
                   Else
                       outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                   End If
               End If
           Next
           regex = outputPattern
       End If
   End Function

5. Speichern und schließen Sie das Fenster des Microsoft Visual Basic for Applications Editors.

Answer 2

Erweiterung von patszim's Antwort für diejenigen, die in Eile sind.

1. Öffnen Sie Excel-Arbeitsmappe.

2. Alt+F11 zum Öffnen des VBA/Macros-Fensters.

3. Fügen Sie einen Verweis auf Regex unter Extras dann Verweise hinzu
   [

4. und wählen Sie Microsoft VBScript Regular Expression 5.5
   [

5. Fügen Sie ein neues Modul ein (der Code muss im Modul platziert sein, da er sonst nicht funktioniert).
   [

6. In das neu eingefügte Modul,
   [

7. Fügen Sie den folgenden Code hinzu:

   Function RegxFunc(strInput As String, regexPattern As String) As String
       Dim regEx As New RegExp
       With regEx
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .pattern = regexPattern
       End With
   
       If regEx.Test(strInput) Then
           Set matches = regEx.Execute(strInput)
           RegxFunc = matches(0).Value
       Else
           RegxFunc = "nicht übereinstimmend"
       End If
   End Function

8. Das Regex-Muster wird in einer der Zellen platziert und es wird absolute Referenzierung darauf verwendet. [ Die Funktion wird an die Arbeitsmappe gebunden, in der sie erstellt wurde.
   Wenn sie in verschiedenen Arbeitsmappen verwendet werden soll, speichern Sie die Funktion in Personal.XLSB

Answer 3

Hier ist mein Versuch:

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp

    With regex
        .IgnoreCase = True  'ignoriert Groß- und Kleinschreibung während der Suche durch den Regex-Engine.
        .pattern = pattern  'deklariert Regex-Muster.
        .Global = False     'beschränkt die Suche des Regex nur auf das erste Vorkommen.

        If .Test(html) Then         'Überprüfung, ob das Muster übereinstimmt oder nicht
            mStr = .Execute(html)(0)        '.Execute(html)(0) liefert den String, der mit dem Regex übereinstimmt
            RegParse = .Replace(mStr, "$1") '.Replace-Funktion ersetzt den String durch den Inhalt der ersten geschweiften Klammern - $1.
        Else
            RegParse = "#N/A"
        End If 
    End With
End Function

Answer 4

Dies ist keine direkte Antwort, könnte aber eine effizientere Alternative für Ihre Überlegung bieten. Google Sheets hat mehrere integrierte Regex-Funktionen, die sehr praktisch sein können und einige der technischen Verfahren in Excel umgehen helfen. Natürlich gibt es Vorteile bei der Verwendung von Excel auf Ihrem PC, aber für die große Mehrheit der Benutzer bietet Google Sheets eine identische Erfahrung und kann einige Vorteile bei der Portabilität und Freigabe von Dokumenten bieten.

Sie bieten

REGEXEXTRACT: Extrahiert übereinstimmende Teilzeichenfolgen gemäß einem regulären Ausdruck.

REGEXREPLACE: Ersetzt einen Teil einer Textzeichenfolge durch eine andere Textzeichenfolge mit regulären Ausdrücken.

SUBSTITUTE: Ersetzt vorhandenen Text durch neuen Text in einer Zeichenfolge.

REPLACE: Ersetzt einen Teil einer Textzeichenfolge durch eine andere Textzeichenfolge.

Sie können diese direkt in eine Zelle wie folgt eingeben und es wird produzieren, was Sie möchten

=REGEXMATCH(A2, "[0-9]+")

Sie funktionieren auch gut in Kombination mit anderen Funktionen wie IF-Anweisungen, wie folgt:

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

Hoffentlich bietet dies eine einfache Lösung für Benutzer, die sich durch die VBS-Komponente von Excel eingeschüchtert fühlen.

Answer 5

Um zum wertvollen Inhalt beizutragen, möchte ich diese Erinnerung daran erstellen, warum RegEx innerhalb von VBA manchmal nicht ideal ist. Nicht alle Ausdrücke werden unterstützt, sondern können stattdessen einen Fehler 5017 auslösen und den Autor im Unklaren lassen (wovon ich selbst ein Opfer bin).

Obwohl wir einige Quellen darüber finden können, was unterstützt wird, wäre es hilfreich zu wissen, welche Metazeichen usw. nicht unterstützt werden. Eine ausführlichere Erklärung finden Sie hier. In dieser Quelle erwähnt:

"Obwohl "Die regulären Ausdrücke ... von VBScript in Version 5.5 eine Reihe wesentlicher Regex-Funktionen implementieren, die in früheren Versionen von VBScript fehlten. ... JavaScript und VBScript implementieren reguläre Ausdrücke im Perl-Stil. Ihnen fehlen jedoch viele erweiterte Funktionen, die in Perl und anderen modernen regulären Ausdrucksvarianten verfügbar sind:"

---

Also, nicht unterstützt werden:

• Start des String-Ankers \A, alternativ verwenden Sie das ^ Zeichen, um die Position vor dem ersten Zeichen im String abzugleichen
• Ende des String-Ankers \Z, alternativ verwenden Sie das $ Dollarzeichen, um die Position nach dem letzten Zeichen im String abzugleichen
• Positive LookBehind, z.B.: (?<=a)b (während der positive LookAhead unterstützt wird)
• Negative LookBehind, z.B.: (? (während der negative LookAhead **unterstützt** wird) ``* Atomare Gruppierung
• Besitzergreifende Quantoren
• Unicode z.B.: \{uFFFF}
• Benannte Erfassungsgruppen. Verwenden Sie alternativ Numerische Erfassungsgruppen
• Inline-Modifier, z.B.: /i (Groß-/Kleinschreibung) oder /g (global) usw. Legen Sie diese über die Eigenschaften des RegExp-Objekts fest > RegExp.Global = True und RegExp.IgnoreCase = True, wenn verfügbar.
• Bedingungen
• Kommentare zu regulären Ausdrücken. Fügen Sie diese mit regulären ' Kommentaren im Skript hinzu``

``

Ich bin bereits mehr als einmal an Grenzen gestoßen, wenn ich reguläre Ausdrücke in VBA verwendet habe. Normalerweise mit LookBehind, aber manchmal vergesse ich sogar die Modifier. Ich habe nicht alle oben genannten Hindernisse selbst erlebt, dachte aber, ich würde versuchen, umfangreich auf einige ausführlichere Informationen hinzuweisen. Fühlen Sie sich frei zu kommentieren/korrigieren/hinzuzufügen. Ein großes Dankeschön an regular-expressions.info für eine Fülle an Informationen.

P.S. Sie haben reguläre VBA-Methoden und -Funktionen erwähnt, und ich kann bestätigen, dass sie (zumindest mir) auf ihre eigene Weise hilfreich waren, wo RegEx versagen würde.

``