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

Ich musste dies als Zellenfunktion verwenden (wie SUMME oder VERWEIS) und fand es einfach zu:

1. Stellen Sie sicher, dass Sie sich in einer Makro-fähigen Excel-Datei befinden (speichern Sie sie als xlsm).

2. Öffnen Sie die Entwicklertools Alt + F11

3. Fügen Sie Microsoft VBScript Regular Expressions 5.5 wie in anderen Antworten hinzu

4. Erstellen Sie die folgende Funktion entweder im Arbeitsbuch oder in seinem eigenen Modul:

   Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
       Dim regex As New VBScript_RegExp_55.RegExp
       Dim strInput As String
   
       strInput = myRange.Value
   
       With regex
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = matchPattern
       End With
   
       REGPLACE = regex.Replace(strInput, outputPattern)
   
   End Function

5. Dann können Sie es in der Zelle verwenden mit =REGPLACE(B1, "(\w) (\d+)", "$1$2") (z.B. "A 243" zu "A243")

Answer 2

Hier ist eine regex_subst() Funktion. Beispiele:

=regex_subst("Wassermelone", "[aeiou]", "")
---> Wsrmeln
=regex_subst("Wassermelone", "[^aeiou]", "")
---> aeoeo

Hier ist der vereinfachte Code (einfacher für mich jedenfalls). Ich konnte nicht herausfinden, wie ich ein geeignetes Ausgabemuster erstellen kann, das wie meine Beispiele funktioniert:

Funktion regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function

Answer 3

Ich möchte keine Verweisbibliothek aktivieren müssen, da meine Skripte portabel sein müssen. Die Zeile Dim foo As New VBScript_RegExp_55.RegExp verursachte Fehler vom Typ Benutzerdefinierter Typ nicht definiert, aber ich habe eine Lösung gefunden, die für mich funktioniert hat.

Aktualisierung der Kommentare zu RE mit @chrisneilsen :

Ich war der Meinung, dass das Aktivieren einer Verweisbibliothek mit den Einstellungen des lokalen Computers verbunden war, aber tatsächlich ist sie direkt mit der Arbeitsmappe verbunden. Sie können also eine Verweisbibliothek aktivieren, eine makrofähige Arbeitsmappe freigeben und der Endbenutzer müsste die Bibliothek nicht ebenfalls aktivieren. Einschränkung: Der Vorteil von spätem Binden besteht darin, dass der Entwickler sich keine Gedanken darüber machen muss, ob die falsche Version einer Objektbibliothek auf dem Computer des Benutzers installiert ist. Dies wäre wahrscheinlich kein Problem mit der VBScript_RegExp_55.RegExp-Bibliothek, aber ich bin nicht davon überzeugt, dass der "Leistungs" -Vorteil für mich zu diesem Zeitpunkt von Bedeutung ist, da wir von unmerklichen Millisekunden in meinem Code sprechen. Ich fand, dass dies ein Update verdient, um anderen zu helfen, es zu verstehen. Wenn Sie die Verweisbibliothek aktivieren, können Sie "früh binden" verwenden, aber wenn nicht, wird der Code meiner Meinung nach gut funktionieren, aber Sie müssen "spät binden" und auf einige Leistungs-/Debug-Funktionen verzichten.

Quelle: https://peltiertech.com/Excel/EarlyLateBinding.html

Was Sie tun möchten, ist ein Beispielstring in Zelle A1 einzufügen und dann Ihr strPattern zu testen. Wenn das funktioniert, passen Sie dann rng nach Bedarf an.

Public Sub RegExSearch()
'https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
'https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/
'https://www..vitoshacademy.com/vba-regex-in-excel/
    Dim regexp As Object
    'Dim regex As New VBScript_RegExp_55.regexp 'Verursachte einen Fehler "Benutzerdefinierter Typ nicht definiert"
    Dim rng As Range, rcell As Range
    Dim strInput As String, strPattern As String

    Set regexp = CreateObject("vbscript.regexp")
    Set rng = ActiveSheet.Range("A1:A1")

    strPattern = "([a-z]{2})([0-9]{8})"
    'Suche nach 2 Buchstaben und dann 8 Zahlen z.B.: XY12345678 = Übereinstimmung

    With regexp
        .Global = False
        .MultiLine = False
        .ignoreCase = True
        .Pattern = strPattern
    End With

    For Each rcell In rng.Cells

        If strPattern <> "" Then
            strInput = rcell.Value

            If regexp.test(strInput) Then
                MsgBox rcell & " Übereinstimmung in Zelle " & rcell.Address
            Else
                MsgBox "Keine Übereinstimmungen!"
            End If
        End If
    Next
End Sub