Excel Regex Tutorial: Mustervergleiche mit regulären Ausdrücken beherrschen

RegEx in Excel verwenden

Excel unterstützt RegEx zwar nicht von Haus aus, aber du kannst es aktivieren, indem du eine eigene Definition mit einem VBA-Skript (für Windows) schreibst oder Add-ins (für Mac) aktivierst. Wir zeigen dir, wie du eine Regex-Funktion aktivierst, um Text aus einer Spalte in Excel zu extrahieren.

Für Windows

Um loszulegen, drücke Alt+F11, um den Visual Basic Editor zu öffnen. Du kannst auch über Extras > Makro > Visual Basic Editor dorthin navigieren. Dann klickst du auf Einfügen > Modul.

Füge die folgende Funktion in das Modul ein:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Speichere die Funktion und navigiere zurück zum Tabellenblatt. Jetzt speicherst du die Arbeitsmappe als makroaktivierte Arbeitsmappe mit der Dateierweiterung .xlsm.

Verwendung der Funktion in einem Excel-Arbeitsblatt

Wenn du Makros in deiner Excel-Arbeitsmappe aktivierst, kannst du die Funktion CustomRegExpExtract wie eine reguläre Funktion in Excel verwenden, um komplexe Zeichenfolgenmuster zu analysieren:

• inputText ist die ursprüngliche Zeichenkette, die geparst werden soll;

• regexPattern ist das Muster des regulären Ausdrucks, das zum Parsen des inputTextes verwendet wird;

• instanceNumber gibt standardmäßig alle Instanzen des Musters zurück, aber es kann eine ganze Zahl eingegeben werden, um die Anzahl der Instanzen festzulegen, die zurückgegeben werden sollen; und

• matchCase gibt an, ob die Groß- und Kleinschreibung beim Abgleich berücksichtigt wird (TRUE oder weggelassen) oder ob sie ignoriert wird (FALSE).

Nehmen wir an, wir möchten Telefonnummern aus einem Feld extrahieren. Wir können das Regex-Muster in eine beliebige Zelle eingeben (im folgenden Fall steht es in Zelle A2). In Zelle A6 steht dein Beispiel für eine Freiform-Zeichenkette: "Sophia, 1111111111". In Zelle B6 geben wir =CustomRegExpExtract(A6,$A$2) ein. Die Funktion extrahiert die Telefonnummer, indem sie die Zeichenkette mit dem Regex-Muster in Zelle A2 vergleicht, und gibt wie erwartet "1111111111" zurück.

Für Mac OS

Leider erkennt Mac OS die MS VBScript Regular Expressions 5.5 Bibliothek nicht. Um komplexe Musterabgleiche in Excel auf einem Mac zu ermöglichen, gibt es zwei Möglichkeiten: die Verwendung von erweiterten Filtern oder die Installation von Paketen.

Erweiterte Filter verwenden

Erweiterte Filter in Excel helfen dabei, komplexe Datenfilteroperationen auf der Grundlage mehrerer Kriterien durchzuführen. Diese Funktion ist besonders nützlich, wenn du mit großen Datensätzen arbeitest. Hier erfährst du, wie du erweiterte Filter in Excel verwenden kannst:

• Richte deine Daten ein: Achte darauf, dass deine Daten in einem tabellenartigen Format mit klaren Überschriften vorliegen.

• Erstelle einen Kriterienbereich: Richte einen separaten Bereich in deinem Arbeitsblatt ein, um die Kriterien für die Filterung festzulegen. Dieser Bereich sollte die gleichen Überschriften haben wie die Spalten, die du filtern willst. Unterhalb dieser Überschriften gibst du die Kriterien für die Filterung an. Du kannst mehrere Kriterien unter der gleichen Überschrift für eine "ODER"-Logik oder in separaten Zeilen für eine "UND"-Logik eingeben.

• Wähle die Daten aus: Klicke auf eine Zelle in deinem Datensatz oder wähle den gesamten Bereich aus, den du filtern möchtest.

• Richte den Filter ein: Klicke auf die Registerkarte Daten im Excel-Ribbon und navigiere zu der Option Erweiterter Filter. Klicke in der Gruppe " Sortieren & Filtern" auf " Erweitert". Im Dialogfeld Erweiterter Filter wählst du aus, ob du die Liste an Ort und Stelle filtern oder die Ergebnisse an einen anderen Ort kopieren möchtest. Gib den Listenbereich an (deine Daten). Gib den Kriterienbereich an (in dem du deine Filterbedingungen einrichtest). Klicke auf OK, um den Filter anzuwenden.

Angenommen, du hast einen Datensatz mit den Spalten Name, Alter und Stadt. Du möchtest Personen über 30 Jahren in New York oder Personen in Los Angeles anzeigen lassen. Dein Kriterienkatalog könnte folgendermaßen aussehen:

Installieren von Add-ins

• Kutools für Excel: Kutools ist ein umfassendes Add-in, das viele Funktionen enthält. Eine davon ist die Möglichkeit, Daten mithilfe von Regex zu suchen und zu ersetzen. Es fügt dem Excel-Band eine neue Registerkarte mit einer Vielzahl von Tools hinzu, darunter auch Regex-Funktionen.

• RegEx Suchen/Ersetzen: Dies ist ein Add-in für Excel, mit dem du Text mit regulären Ausdrücken suchen und ersetzen kannst. Es lässt sich in Excel integrieren und bietet eine erweiterte Suchen/Ersetzen-Funktion als die integrierte Option.

• PowerGREP: PowerGREP ist zwar kein direktes Excel-Add-in, kann aber mit Excel-Dateien arbeiten. Es ist ein leistungsstarkes Grep-Tool für Windows, das komplexe Regex-Operationen mit Text- und Binärdateien ermöglicht. Es ist eher eine eigenständige Anwendung, kann aber in Verbindung mit Excel für erweiterte Regex-Verarbeitung verwendet werden. Es ist allerdings nicht billig. Eine Einzelplatzlizenz kostet 159 US-Dollar und der Preis steigt auf 5.300 US-Dollar für eine 100-Benutzer-Lizenz.

Einige Add-ins kannst du direkt in Excel über die Registerkarte Einfügen > Add-ins abrufen.

Andere müssen von einer externen Quelle heruntergeladen werden und deren Installationsanweisungen folgen.

Ein paar Überlegungen:

• Kompatibilität: Stelle sicher, dass das Add-in mit deiner Excel-Version und deinem Betriebssystem kompatibel ist.
• Sicherheit: Lade nur Add-Ins aus seriösen Quellen herunter und installiere sie.
• Funktionalität braucht: Überlege dir, welche speziellen Regex-Aufgaben du erledigen musst, und wähle ein Add-in, das diese Anforderungen am besten erfüllt.

Fazit

In den meisten Fällen helfen dir die nativen Funktionen wie FINDEN, SUCHEN und ERSETZEN bei 90 % deiner Textbearbeitungsanforderungen in Excel. Bei den verbleibenden 10 %, die komplexere String-Muster enthalten, kann Regex jedoch eine große Hilfe sein. Wenn du mehr über die Anwendung regulärer Ausdrücke in gängigen Programmiersprachen erfahren möchtest, schau dir unsere Kurse Regular Expressions in Python und Intermediate Regular Expressions in R an.

Um in Sachen Excel auf den neuesten Stand zu kommen, schau dir unseren Excel-Grundlagen-Kurs an, der dir die wichtigsten Fähigkeiten vermittelt, die du brauchst, um das Programm zu beherrschen.