Die neuen RegEx-Funktionen: Mustererkennung auf dem nächsten Level

Du kämpfst mit TEIL, FINDEN & Co., um Daten zu zerpflücken?
Du möchtest Telefonnummern in unterschiedlichsten Schreibweisen standardisieren?
Du musst aus einem Text zuverlässig enthaltene E-Mail-Adressen extrahieren?
Du willst prüfen, ob eine Artikelnummer einem vorgegebenen Muster entspricht?

Wenn du Excel aus Microsoft 365 im Einsatz hast, dann habe ich eine gute Nachricht: Mit den neuen REGEX-Funktionen ist das alles (und noch viel mehr) möglich!

Was hinter den Funktionen mit diesen merkwürdigen Namen steckt und wie sie funktionieren, zeige ich dir in diesem Artikel!

Beispieldatei herunterladen

Inhaltsverzeichnis

REGEX: Was ist das überhaupt?

Die Abkürzung „REGEX“ steht für „Regular Expression“ (deutsch: regulärer Ausdruck) und geht laut Wikipedia auf den Mathematiker Stephen Kleene zurück. Im Wesentlichen geht es dabei um Mustererkennung in Zeichenketten, die auch in vielen Programmiersprachen eingesetzt wird.

Dabei kommen ziemlich kryptische Zeichen zum Einsatz, die es dem geneigten Anwender am Anfang ziemlich schwer machen, solche Ausdrücke zu verstehen. Hier ist eine kleine Kostprobe:
^[a-z]+\.[0-9]{3,4}\.\w{3}$

Nein, das ist nicht Klingonisch. Dieser Ausdruck sucht nach Zeichenketten, die:

Mit einem oder mehreren Kleinbuchstaben beginnen ^[a-z]
gefolgt von einem Punkt \.
dann drei oder vier Ziffern [0-9]{3,4}
dann wieder ein Punkt \.
und abschließend genau drei alphanumerische Zeichen \w{3}

Das würde beispielsweise zutreffen auf:
abc.123.xyz
test.1234.abc
x.1234.123

Keine Treffer hingegen wären beispielsweise:
abc.123.xy (nur zwei Zeichen am Ende)
ABC.123.xyz (Großbuchstaben am Anfang)
abc.12.xyz (nur zwei Ziffern)
abc.1234.xyZ1 (vier Zeichen am Ende)

Das war doch jetzt wirklich nicht so schwer, oder? 🤔🙈

Ich persönlich hatte in grauer Vorzeit während meines Studiums im Rahmen einer UNIX-Vorlesung zum ersten Mal Kontakt mit dieser wilden Schreibweise und war damals genauso überfordert, wie du es jetzt wahrscheinlich bist.

Im Vergleich zu damals hast du heute jedoch einen entscheidenden Vorteil: Es gibt KI!

Man kann sich also vom Chat-Bot seiner Wahl jederzeit einen solchen Ausdruck erstellen oder übersetzen lassen und muss nicht zwingend alles selbst können. Trotzdem schadet es nicht, wenn man ein wenig Grundverständnis dafür aufbaut. Daher habe ich eine Übersicht der wichtigsten REGEX-Bausteine in einer kleinen Kurzübersicht vorbereitet, die du dir hier herunterladen kannst:
REGEX-Kurzübersicht (PDF)

Nachdem das geklärt ist, schauen wir uns diese Funktionen mal näher an.

REGEXERSETZEN

Mit dieser Funktion wird ein Text in einer Zeichenfolge durch einen anderen Text auf Basis eines regulären Ausdrucks ersetzt. Wofür kann man so etwas brauchen?

Beispielsweise dazu, um Telefonnummern, die in den unterschiedlichsten Schreibweisen vorliegen, in ein einheitliches Format zu bringen:

Die Telefonnummern im Originalzustand

Du hast solche Telefonnummern sicher schon zigmal gesehen. Mit den bisherigen Excel-Funktionen lassen sich solche Unterschiede aber kaum vereinheitlichen. Mit der REGEXERSETZEN-Funktionen hingegen schon.

Die allgemeine Syntax lautet:
=REGEXERSETZEN(text;muster;ersatz_text;[vorkommen];[beachten_groß_klein])

Dabei sind die beiden letzten Argumente optional: Mit dem vierten Argument lässt sich festlegen, ob nur das erste Vorkommen des gesuchten Ausdrucks geprüft werden soll, oder sämtliche Vorkommen. Und der letzte Parameter legt fest, ob Groß- und Kleinschreibung unterschieden werden soll.

In meinem Beispiel mit den Telefonnummern soll in einem ersten Schritt alles verschwinden, was keine Zahl oder kein Pluszeichen ist:
=REGEXERSETZEN(B9;"[^0-9+]";"")

Schritt 1 entfernt alles außer Ziffern und einem Pluszeichen

In den eckigen Klammern werden alle Zeichen aufgeführt, die beibehalten werden sollen. Durch das vorangestellte ^-Zeichen innerhalb der eckigen Klammer wird diese Liste negiert. Das heißt, es wird alles gefunden, was NICHT im Bereich von 0 bis 9 liegt oder ein Pluszeichen ist.

WICHTIG:
Das ^-Zeichen muss dafür INNERHALB der eckigen Klammern stehen. VOR der Klammer hätte es eine andere Bedeutung. Das werden wir noch gleich sehen.

Im zweiten Schritt wird in dieser schon bereinigten Telefonnummer geprüft, ob sie mit der Ziffer 0 beginnt. In dem Fall wird die 0 durch die Landeskennzahl +49 ersetzt (der Einfachheit halber gehe ich davon aus, dass es sich um lauter deutsche Telefonnummern handelt).
=REGEXERSETZEN(C9;"^0";"+49")

Schritt 2: ersetzt eine führende 0 durch das Länderkennzeichen

Und schon haben wir schöne standardisierte Telefonnummern.

Die beiden Teilschritte lassen sich natürlich auch in einem Rutsch ausführen:
=REGEXERSETZEN(REGEXERSETZEN(B9;"[^0-9+]";"");"^0";"+49")

Beide Schritte zusammengefasst

Ziemlich genial, oder?

REGEXEXTRAHIEREN

Diese Funktion kommt dann zum Einsatz, wenn man aus einem Text bestimmte Teile herauslösen möchte. Die Syntax lautet:
=REGEXEXTRAHIEREN(text;muster;[rückgabe_modus];[beachten_groß_klein])

Nehmen wir mal an, wir haben einen Text, in dem an den unterschiedlichsten Stellen eine E-Mail-Adresse enthalten ist:

Texte mit einer oder mehreren E-Mail-Adressen

Diese E-Mail-Adressen sollen nur für eine Weiterverarbeitung herausgelöst werden:
=REGEXEXTRAHIEREN(B7;"[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,};1;1")

Die extrahierten E-Mail-Adressen

Ja, da wird der reguläre Ausdruck schon deutlich wilder. Die einzelnen Bausteine bewirken Folgendes:

[a-z0-9._%+-]+
Prüft den lokalen Teil (also vor dem @-Zeichen).
Das Pluszeichen nach der schließenden eckigen Klammer besagt, dass mindestens eines oder mehrere der in den Klammern genannten Zeichen erlaubt sind:
Kleinbuchstaben (a–z), Ziffern (0–9), Punkt ., Unterstrich _, Prozent %, Plus +, Minus -.

Gefolgt von einem @-Zeichen.

[a-z0-9.-]+\.
Das prüft die Domain ohne die Top-Level-Domain.
Nach dem @-Zeichen sind nur Buchstaben, Zahlen, Punkt . oder Bindestrich – erlaubt. Es muss mindestens eines dieser Zeichen sein (wieder wegen des Pluszeichens nach der Klammer)
Anschließend muss ein Punkt kommen. Da der Punkt eine spezielle Bedeutung in regulären Ausdrücken hat, muss er mit dem Backslash „maskiert“ werden, so dass der Punkt auch als Punkt interpretiert wird.

[a-z]{2,}
Das ist die Prüfung der Top-Level-Domain. Also der Teil ganz am Ende nach dem letzten Punkt.
Erlaubt sind hier nur Buchstaben, und zwar mindestens 2 davon.

Der optionale 3. Parameter in der Funktion („1“) findet nicht nur die erste, sondern sämtliche vorkommende E-Mail-Adressen. Und der ebenfalls optionale 4. Parameter (wieder „1“) sorgt dafür, dass Groß- und Kleinschreibung keine Rolle spielt. Stattdessen hätte man auch den Ausdruck [a-z,A-Z] verwenden können.

Der in diesem Beispiel verwendete reguläre Ausdruck fängt natürlich nicht sämtliche Sonderfälle und formalen Anforderungen an eine E-Mail-Adresse ab, aber zumindest einen guten Teil davon.

Kommen wir zur letzten der drei neuen Funktionen.

REGEXTESTEN

Mit dieser Funktion wird geprüft, ob ein Text einen bestimmten regulären Ausdruck enthält. Abhängig vom Ergebnis liefert die Funktion dann ein logisches WAHR oder FALSCH zurück.

Damit lässt sich beispielsweise prüfen, ob formale Anforderungen an einen Ausdruck eingehalten werden:

handelt es sich um eine gültige E-Mail-Adresse
entspricht eine Artikelnummern den internen Anforderungen
erfüllt ein Passwort die formalen Bedingungen (Länge, Sonderzeichen…)

Bei etwas Nachdenken fallen einem hier sicherlich viele weitere Beispiele ein.

Im folgenden Beispiel prüfe ich eine Artikelnummer auf formale Bedingungen. Dabei müssen diese Regeln eingehalten werden:

Genau 4 Großbuchstaben am Anfang, gefolgt von einem Bindestrich
Danach müssen genau 4 Ziffern kommen, wieder gefolgt von einem Bindestrich
Am Ende müssen 3 oder mehr Großbuchstaben oder Ziffern stehen

Und so sieht das Ganze dann aus:
=REGEXTESTEN(B8;"^[A-Z]{4}-\d{4}-[A-Z,0-9]{3,}$")

Artikelnummern werden geprüft

^[A-Z]{4}-
Muss mit genau 4 Großbuchstaben beginnen, gefolgt von einem Bindestrich.

\d{4}-
Danach müssen genau 4 Ziffern kommen, gefolgt von einem Bindestrich. Anstatt \d hätte man auch die Schreibweise [0-9] verwenden können.

[A-Z,0-9]{3,}$
Die Zeichenkette muss dann mit 3 oder mehr Großbuchstaben oder Ziffern enden.

Fazit

Ich gebe zu, wenn man zum ersten Mal mit diesen Ausdrücken in Berührung kommt, ist man ziemlich abgeschreckt. Diese kryptische Schreibweise sieht aus wie vom andern Stern. Und es stimmt ja auch: einfache Prüfungen lassen sich natürlich durch geschicktes Kombinieren der altbekannten Funktionen LINKS, RECHTS, TEIL, LÄNGE und so weiter realisieren.

Sobald die Bedingungen aber ein bisschen komplexer werden, stößt man hier schnell an Grenzen. Und spätestens dann lohnt es sich, sich mit diesen Funktionen und den regulären Ausdrucken ein wenig zu beschäftigen. Und wie schon weiter oben erwähnt:
Im Zweifelsfall kann man sich dafür ja auch von der KI unter die Arme greifen lassen.

Wie sieht’s bei dir aus:
Hattest du schon Kontakt zu regulären Ausdrücken? Fallen dir weiterer praktische Anwendungsfälle ein?
Dann lass‘ es uns in den Kommentaren wissen!