Beitrag aus SmartTools Excel Weekly
So nutzen Sie die neuen Excel-Funktionen für reguläre Ausdrücke, Teil 1
Excel 365
Excel erhält wieder ein paar neue Tabellenfunktionen – dieses Mal für "reguläre Ausdrücke" (englisch "Regular Expressions" oder kurz "RegEx" genannt). Das sagt Ihnen nichts? Dann sollten Sie unbedingt weiterlesen, denn mit regulären Ausdrücken können Sie Text-Suchmuster definieren, um Eingaben zu validieren, Textbestandteile aus dem Inhalt einer Zelle herauszufiltern oder um Teile eines Zellinhalts zu ersetzen. Das ist deutlich flexibler als Formelkonstruktionen aus FINDEN-, TEIL-, LINKS-, WECHSELN- oder anderen Textfunktionen. In unserem Tipp stellen wir Ihnen die neuen RegEx-Funktionen vor.
Insgesamt gibt es drei neue Tabellenfunktionen:
Sie erlauben die Überprüfung einer Zeichenfolge, das Extrahieren von Textbestandteilen und das Ersetzen einzelner Textelemente.
Aktuell sind die Funktionen noch in der Vorschauphase und sie stehen nur Microsoft 365-Kunden im Insider-Channel (Betakanal) zur Verfügung. Auf längere Sicht werden sie aber allen Microsoft 365-Abonnenten zugänglich sein.
Doch was sind reguläre Ausdrücke überhaupt? Und wie helfen sie Ihnen in Ihren Tabellenmodellen? Dazu stellen wir Ihnen verschiedene Beispiele vor, die zeigen, wie leistungsfähig die RegEx-Funktionen sind. Die Ausdrücke mögen auf den ersten Blick kompliziert erscheinen. Aber dank ChatGPT, Copilot oder anderer KI-Tools ist auch das kein Problem mehr. Darauf werden wir später noch eingehen.
Ein interessantes Einsatzgebiet regulärer Ausdrücke ist die Überprüfung von Texteingaben. Nehmen Sie dazu folgendes Beispiel: Sie haben eine Tabelle mit Personaldaten, in der Sie neben den Namen auch die Rentenversicherungsnummern erfassen. Diese Nummern sind nach einem festen Schema aufgebaut:
- 2 Ziffern für die Region des Versicherungsträgers
- 6 Ziffern für das Geburtsdatum in der Form "TTMMJJ"
- Der Anfangsbuchstabe des Geburtsnamens
- 3 Ziffern aus Seriennummer und Prüfziffer
Sie wollen nun verhindern, dass formal falsche Versicherungsnummern eingegeben werden. Das ist ein perfekter Fall für einen regulären Ausdruck. Denn damit können Sie überprüfen, ob ein Text einem bestimmten Suchmuster entspricht. Dafür stellen reguläre Ausdrücke eine Vielzahl von Platzhaltern und syntaktischen Methoden zur Verfügung. Eine Kurzreferenz finden Sie zum Beispiel dieser englischsprachigen Webseite.
In Excel können Sie mit der neuen Funktion REGEXTEST prüfen, ob ein Text bzw. ein Zellinhalt mit einem bestimmten Suchmuster übereinstimmt. Die Funktion liefert entweder wahr oder falsch und hat folgende Syntax:
REGEXTEST(<Text>;<Suchmuster>;[<Groß-/Kleinschreibung>])
<Text> ist der Text, den Sie untersuchen wollen. <Suchmuster> ist der reguläre Ausdruck mit der Schemadefinition. Und der optionale Parameter <Groß-/Kleinschreibung> legt fest, ob die Schreibweise berücksichtigt werden soll oder nicht.
In unserem Beispiel mit der Rentenversicherungsnummer müssen Sie also einen regulären Ausdruck erstellen, der das oben genannte Schema der Nummer beschreibt. Der sieht folgendermaßen aus:
^\d{2}[\s-]?\d{6}[\s-]?[A-Z][\s-]?\d{3}$
Diese zunächst kryptisch wirkende Zeichenfolge erklärt sich wie folgt:
^- Beginnt die Suche direkt am Anfang des Textes.\d{2}- "\d" ist der Platzhalter für eine Ziffer, und der Wert in geschweiften Klammern gibt an, dass genau 2 Ziffern vorkommen müssen.[\s-]?- Die eckigen Klammern umgeben eine Gruppe von Zeichen, die an dieser Stelle zulässig sind – in diesem Fall mit "\s" einen Leerraum (beispielsweise ein Leerzeichen) sowie alternativ ein Bindestrich. Das nachfolgende Fragezeichen heißt, dass das Zeichen einmal oder gar nicht vorkommen kann.\d{6}- Wie oben folgen an dieser Stelle Ziffern, und zwar genau 6.[\s-]?- Wie oben sind hier ein Leerraum oder ein Bindestrich zulässig, entweder einmal oder keinmal.[A-Z]- Die eckigen Klammern kennzeichnen wieder eine Gruppe zulässiger Zeichen. Der Bindestrich zwischen zwei Zeichen beschreibt einen Zeichenbereich – hier den Bereich von A bis Z, wobei reguläre Ausdrücke zwischen Groß- und Kleinschreibung unterscheiden. Zulässig sind an dieser Stelle also nur die Großbuchstaben von A bis Z.[\s-]?- siehe oben\d{3}- Es müssen wieder Ziffern folgen, dieses Mal genau 3.$- Definiert, dass die zu untersuchende Zeichenkette an dieser Stelle enden muss.
Um zu überprüfen, ob der in Zelle C4 eingegebene Wert dem Schema einer Rentenversicherungsnummer entspricht, könnten Sie in Zelle D4 folgende Formel einsetzen:
=REGEXTEST(C4;"^\d{2}[\s-]?\d{6}[\s-]?[A-Z][\s-]?\d{3}$";0)
Wenn der Wert in C4 dem Schema entspricht, liefert die Formel WAHR. Wenn die Eingabe in C4 nicht passt, gibt die Formel FALSCH aus.
Anhand der Wahrheitswerte zu erkennen, ob eine Eingabe fehlerbehaftet ist, ist schon praktisch. Sie können die RegEx-Funktion aber auch in bedingten Formatierungen oder in einer Gültigkeitsprüfung einsetzen. Mehr dazu erfahren Sie im zweiten Teil dieses Tipps.