Verwendung von regulären Ausdrücken (Regex) in Microsoft Excel sowohl in Zellen als auch in Schleifen

Um reguläre Ausdrücke direkt in Excel-Formeln zu verwenden, kann die folgende benutzerdefinierte Funktion (UDF) hilfreich sein. Die Funktionalität für reguläre Ausdrücke wird mehr oder weniger direkt als Excel-Funktion verfügbar gemacht.

Wie es funktioniert

Es werden 2-3 Parameter benötigt.

1. Ein Text, für den der reguläre Ausdruck verwendet werden soll.
2. Ein regulärer Ausdruck.
3. Eine Formatzeichenfolge, die angibt, wie das Ergebnis aussehen soll. Es kann $0, $1, $2 und so weiter enthalten. $0 ist die gesamte Übereinstimmung, $1 und höher entsprechen den jeweiligen Übereinstimmungsgruppen im regulären Ausdruck. Der Standardwert ist $0.

Einige Beispiele

E-Mail-Adresse extrahieren:

=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+", "$0")

Ergebnisse in: [email protected]

Mehrere Teilstrings extrahieren:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Ergebnisse in: E-Mail: [email protected], Name: Peter Gordon

So zerlegen Sie eine kombinierte Zeichenfolge in einer einzelnen Zelle in ihre Komponenten in mehreren Zellen:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

Ergebnisse in: Peter Gordon[email protected] ...

Wie benutzt man

Um diese UDF zu verwenden, gehen Sie wie folgt vor (ungefähr basierend auf diese Microsoft-Seite . Sie haben dort einige gute zusätzliche Informationen!):

1. In Excel in einer makrofähigen Datei ('.xlsm') ALT+F11 drücken, um den Microsoft Visual Basic für Applikationen Editor zu öffnen.
2. Fügen Sie der Bibliothek für reguläre Ausdrücke einen VBA-Verweis hinzu (schamlos kopiert aus Portland Runners ++ - Antwort ):
   1. Klicken Sie auf Extras -> Referenzen (bitte entschuldigen Sie 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, vergewissern Sie sich, dass das Modul nicht ​​den gleichen Namen wie die unten stehende UDF hat (z. B. bewirkt die Benennung des Moduls Regex und der Funktion regex# NAME! Fehler).

4. Geben Sie im großen 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 Microsoft Visual Basic für Applikationen Editor.

Erweitern auf patszim 's Antwort für die in einem Ansturm.

1. Öffnen Sie die Excel-Arbeitsmappe.
2. Alt+F11 um das VBA/Macros-Fenster zu öffnen.
3. Verweis auf Regex hinzufügen unter Tools dann Referenzen
   
4. und Auswählen von Microsoft VBScript Regular Expression 5.5
   
5. Fügen Sie ein neues Modul ein (Code muss sich im Modul befinden, sonst funktioniert er nicht).
   
6. Im neu eingefügten 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 = "not matched"
       End If
   End Function
   

8. Das Regex-Muster wird in eine der Zellen gestellt und mit absoluter Referenzierung versehen. Die Funktion ist an die Arbeitsmappe gebunden, in der sie erstellt wurde.
   Wenn die Verwendung in verschiedenen Arbeitsmappen erforderlich ist, speichern Sie die Funktion in Personal.XLSB .

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  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.

        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
        Else
            RegParse = "#N/A"
        End If

    End With
End Function

Ich musste dies als Zellenfunktion verwenden (wie SUM oder VLOOKUP) und stellte fest, dass es einfach war:

1. Stellen Sie sicher, dass Sie sich in einer makrofähigen Excel-Datei befinden (als xlsm speichern).
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 in der Arbeitsmappe oder in einem 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 in Zelle mit =REGPLACE(B1, "(\w) (\d+)", "$1$2") (Beispiel: "A 243" bis "A243")

Hier ist eine regex_subst() Funktion. Beispiele:

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Hier ist der vereinfachte Code (für mich sowieso einfacher). Ich konnte nicht herausfinden, wie ich ein geeignetes Ausgabemuster mit den oben genannten Methoden erstellen kann, um wie in meinen Beispielen zu funktionieren:

Function 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

Ich möchte keine Referenzbibliothek aktivieren müssen, da meine Skripte portierbar sein müssen. Die Zeile Dim foo As New VBScript_RegExp_55.RegExp verursachte User Defined Type Not Defined Fehler, aber ich fand eine Lösung, die für mich funktionierte.

Sie möchten einfach eine Beispielzeichenfolge in die Zelle A1 einfügen und dann Ihr strPattern testen. Sobald dies funktioniert, stellen Sie rng wie gewünscht ein.

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 'Caused "User Defined Type Not Defined" Error
    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")

    For Each rcell In rng.Cells

        strPattern = "([a-z]{2})([0-9]{8})"
        'Search for 2 ## then 8 Digits Eg: XY12345678 = Matched

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

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

            If regexp.test(strInput) Then
                MsgBox rcell & " Matched in Cell " & rcell.Address
            Else
                MsgBox "No Matches!"
            End If
        End If
    Next
End Sub