Excels versteckte REGEX- und SCAN-Funktionen können viel mehr, als man erwartet

Excel verfügt über Formeln für fast alles - XMATCH zur Suche nach Daten, IF für logische Operationen und SUMIF zur Berechnung von Summen unter bestimmten Bedingungen. Doch wahrscheinlich sind Sie ins Stocken geraten, wenn Sie jemals bestimmte Muster aus Texten extrahieren, Formate überprüfen oder Zeichenketten auf komplexe Weise verarbeiten mussten. Genau hier kommt REGEX ins Spiel.

Es kann Musterabgleichungen und Textmanipulationen mit Präzision ausführen. Wenn man es jedoch mit der SCAN-Funktion kombiniert, lassen sich Probleme lösen, für die ansonsten mehrere Hilfsspalten oder verschachtelte Formeln erforderlich wären. Falls Sie also mit unstrukturierten Daten arbeiten oder bestimmte Informationen aus Textzeichenfolgen extrahieren müssen, lohnt es sich, sich mit diesen Funktionen vertraut zu machen.

Zuerst verstehen wir uns auf die REGEX-Funktion.

Es ist die Mustervergleiche, die Sinn ergeben, sobald man sie ausprobiert.

REGEX (Abkürzung für Regular Expressions) ist eine Musterabgleichungssprache, die dazu verwendet wird, Text basierend auf bestimmten Regeln zu suchen, zu extrahieren oder zu ersetzen. Anstatt nach einem exakten Text wie „Apfel“ zu suchen, können Sie nach Mustern suchen - beispielsweise „jedes Wort, das mit (a) beginnt und mit (e) endet“ oder „alle E-Mail-Adressen in einer Zelle“.

REGEX ist eine intelligentere Variante der EXCEL-FIND-Funktion, die sich gut für einfache Suchvorgänge eignet. REGEX bewältigt jedoch auch komplexere Szenarien. Wenn Sie beispielsweise Telefonnummern aus unübersichtlichem Text extrahieren müssen, alles zwischen Klammern herausfiltern oder E-Mail-Formate überprüfen wollen, dann kommen Sie an REGEX nicht vorbei.

Falls Sie bereits mit Musterabgleich in anderen Programmiersprachen oder Werkzeugen gearbeitet haben, funktioniert die REGEX-Funktion in Excel ähnlich. Sie durchsucht Text nach von Ihnen definierten Mustern und führt anschließend Aktionen basierend auf dem Gefundenen aus. Die Muster selbst verwenden spezielle Zeichen und Syntax - beispielsweise \d für Ziffern oder [A-Z] für Großbuchstaben. Zunächst erscheinen diese Zeichen und Regeln vielleicht rätselhaft, doch mit der Übung werden sie intuitiv verständlich.

Excel verfügt über drei REGEX-Funktionen, die Sie verwenden können.

Sie bewältigen verschiedene Aufgaben gut.

Im Jahr 2024 führte Excel drei REGEX-Funktionen ein: REGEXTEST, REGEXEXTRACT und REGEXREPLACE. Jede davon übernimmt eine andere Aufgabe - es ist also entscheidend zu wissen, wann welche Funktion zu verwenden ist.

REGEXTEST

Es prüft, ob ein Muster im Text vorkommt, und gibt TRUE oder FALSE zurück. Die Syntax lautet:

=REGEXTEST(text, pattern, [mode])

• Text: Die Zelle oder Zeichenfolge, die Sie überprüfen möchten.
• Muster: Das REGEX-Muster, nach dem gesucht werden soll.
• mode (optional): Steuert die Groß-/Kleinschreibung. Verwenden Sie „i“ für eine Groß-/Kleinschreibung-unabhängige Übereinstimmung.

Angenommen, Sie haben in der Verkaufsdaten-Tabelle eine Spalte mit Produktcodes und möchten Einträge markieren, die mindestens drei aufeinanderfolgende Ziffern enthalten. Sie können Folgendes verwenden:

=REGEXTEST(A2, "\d{3}")

Wenn Zelle A2 den Inhalt „PRD-12345-X“ enthält, gibt die Funktion in Spalte B den Wert TRUE zurück, da sie drei aufeinanderfolgende Ziffern gefunden hat.

REGEXEXTRACT

Es extrahiert bestimmten Text aus einer Zeichenkette anhand eines Musters. Die Syntax lautet:

=REGEXEXTRACT(text, pattern, [mode], [instance])

• Text: Der Quelltext.
• Muster: Das REGEX-Muster, das definiert, was extrahiert werden soll.
• mode (optional): Steuerung der Groß-/Kleinschreibung („i“ für ignorieren).
• Instance (optional): Welches Ergebnis zurückgegeben werden soll, wenn es mehrere gibt (1 für das erste, 2 für das zweite usw.).

In den Verkaufsdaten enthält Spalte C Kunden-E-Mails, die mit anderen Texten vermischt sind. Um nur die E-Mails zu extrahieren, verwenden wir:

=REGEXEXTRACT(B2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

Dieses Muster erkennt Standard-E-Mail-Formate und extrahiert sie ordentlich in Spalte D.

REGEXREPLACE

Es ersetzt Text, der einem Muster entspricht, durch etwas anderes. Die Syntax lautet:

=REGEXREPLACE(text, pattern, replacement, [mode], [instance])

• Text: Der Originaltext.
• Muster: Was gesucht werden soll.
• Ersatz: Womit es ersetzt werden soll.
• mode (optional): Groß-/Kleinschreibung.
• Instanz (optional): Welche Instanz ersetzt werden soll (leer lassen, um alle zu ersetzen).

Falls Spalte E Telefonnummern in verschiedenen Formaten enthält - einige mit Bindestrichen, andere mit Klammern - können Sie sie standardisieren, indem Sie die folgende Formel verwenden, um alles außer Ziffern zu entfernen:

=REGEXREPLACE(C2, "[^0-9]", "") 

Das Muster [^0-9] bedeutet „alles, was keine Zahl ist“, und das Ersetzen dieses Musters durch eine leere Zeichenkette lässt nur die Ziffern übrig.

Diese drei Funktionen decken die meisten Anforderungen an die Textbearbeitung ab. Sie können sie jedoch auch mit der SCAN-Funktion kombinieren, um noch mehr Flexibilität zu erhalten - insbesondere beim Verarbeiten von Daten über mehrere Zeilen hinweg oder beim Extrahieren wiederkehrender Muster.

Die SCAN-Funktion macht REGEX noch besser

Ich kombiniere beides, um komplexe Probleme zu lösen.

SCAN verarbeitet Arrays Zeile für Zeile und gibt kumulierte Ergebnisse zurück. Es durchläuft den gesamten Bereich, wendet eine Funktion auf jeden Wert an und behält dabei den Verlauf der Berechnungen im Auge - im Gegensatz zu Standardfunktionen, die nur mit einzelnen Zellen arbeiten.

Es verwendet die folgende Syntax:

=SCAN([initial_value], array, lambda)

Lassen Sie uns die Parameter aufschlüsseln:

• initial_value (optional): Dient dazu, den Startpunkt für die Berechnung festzulegen. Wird dieser Parameter nicht angegeben, verwendet SCAN das erste Element des Arrays als Anfangswert des Akkumulators; die Operationen der Lambda-Funktion beginnen somit bei diesem Element.
• Array: Der Bereich der Zellen, der verarbeitet werden soll.
• Lambda: Eine benutzerdefinierte Funktion, die festlegt, was mit jedem Wert geschehen soll. Mit Lambda können Sie Formeln so schreiben, als würden Sie es von Hand tun - die Funktion nimmt zwei Argumente entgegen: das bisherige Ergebnis sowie den aktuellen Wert.

Allein genutzt ist SCAN nützlich, um laufende Summen zu berechnen oder bedingte Verarbeitungen durchzuführen. Doch in Kombination mit der REGEX-Funktion lässt sich mit einer einzigen Formel Muster aus ganzen Spalten extrahieren sowie unstrukturierte Daten bereinigen.

Schauen wir uns ein praktisches Beispiel an. In der Tabellenkalkulation mit den Verkaufsdaten enthält Spalte G Beschreibungen der Transaktionen, in denen die Beträge im Text versteckt sind - beispielsweise „Zahlung in Höhe von 250 $erhalten“ oder „Rückerstattung von 75,50 $bearbeitet“. Sie benötigen lediglich die Dollarbeträge.

Eine Standard-REGEXEXTRACT-Formel funktioniert für einzelne Zellen:

=REGEXEXTRACT(D2, "\$\d+\.?\d*")

Um jedoch die gesamte Spalte zu verarbeiten, würde man normalerweise die Formel hunderte Zeilen nach unten ziehen. Mit SCAN erledigen Sie das alles auf einmal - verwenden Sie einfach:

=SCAN("", G2:G11, LAMBDA(acc, curr, REGEXEXTRACT(curr, "\$[\d,]+\.?\d*")))

Diese Formel durchläuft die Zellen G2 bis G11, extrahiert den Dollarbetrag aus jeder Zelle und gibt das gesamte Array zurück. Der akkumulierte Wert (acc) ist hier nicht erforderlich, da wir lediglich die Werte extrahieren - doch SCAN verlangt ihn dennoch in der Lambda-Struktur.

Es ist nützlich, wenn man auf vorherigen Ergebnissen aufbauen muss. Angenommen, Spalte I enthält Bestellnummern, die unterschiedlich formatiert sind - manche haben Präfixe, andere nicht. Wenn man nun den numerischen Teil extrahieren und eine fortlaufende Zählung der gültigen IDs erstellen möchte, verwendet man folgendes:

=SCAN(0, E2:E100, LAMBDA(count, id, IF(REGEXTEST(id, "\d{4,}"), count+1, count)))

Die obige Formel prüft mit der Funktion REGEXTEST jede Zelle darauf, ob darin mindestens vier aufeinanderfolgende Ziffern vorhanden sind. Falls dies der Fall ist, wird der Zähler erhöht; andernfalls bleibt der vorherige Wert in Spalte J erhalten. Das Ergebnis ist eine Spalte, die die kumulierte Anzahl der gültigen IDs angibt - je weiter man in der Liste nach unten geht.

SCAN bewältigt auch mehrstufige REGEX-Operationen in einer einzigen Formel. Wenn Sie Text extrahieren, validieren und transformieren müssen - alles auf einmal - genügt es, die REGEX-Funktion in die SCAN-Funktion einzubetten. Dadbei sind keine Hilfsspalten erforderlich. Das ist besonders nützlich bei der Arbeit mit großen Datensätzen, bei denen zusätzliche Spalten die Verarbeitung verlangsamen können.

Wann sollte man diese Funktionen anstelle der Standardformeln verwenden?

REGEX und SCAN ersetzen weder VLOOKUP noch IF - sie sind jedoch nützliche Werkzeuge, wenn es darum geht, Textmuster zu erkennen, anstatt auf exakte Übereinstimmungen zu achten. Wenn Sie importierte Daten bereinigen oder bestimmte Informationen aus unstrukturiertem Text extrahieren müssen, sparen Sie mit diesen Funktionen Zeit im Vergleich zu manuellen Bearbeitungen oder der Verwendung mehrerer TEXT-Funktionen.

Die Syntax mag auf den ersten Blick abschreckend wirken, doch sobald Sie einige Muster beherrschen - wie die Validierung von E-Mail-Adressen, das Extrahieren von Telefonnummern oder das Aufteilen von Namen - können Sie diese in verschiedenen Projekten wiederverwenden. Idealerweise beginnen Sie mit REGEXTEST, um Muster zu überprüfen, und wechseln anschließend zu REGEXEXTRACT, wenn Sie den tatsächlichen Text benötigen. Verwenden Sie SCAN, wenn Sie ganze Spalten verarbeiten und die Ergebnisse auswerten müssen.