Google Sheets-Kochbuch: Reguläre Ausdrücke

Reguläre Ausdrücke dienen dazu, Muster in Zeichenketten zu suchen.Sie können mit regulären Ausdrücken sehr komplizierte Bedingungen formulieren, die auf ein Muster zutreffen sollen. Der reguläre Ausdruck muss in Hochkommata eingeschlossen werden, da es sich ebenfalls um eine Zeichenkette handelt. Reine Zahlen müssen Sie als Zeichenkette formatieren, sonst erhalten Sie eine Fehlermeldung.

Reguläre Ausdrücke können Sie in verschiedenen Befehlen verwenden:

REGEXMATCH(Text; regulärer Ausdruck) -> liefert TRUE zurück, wenn der Ausdruck gefunden wird
REGEXEXTRACT(Text; regulärer Ausdruck) -> liefert als Ergebnis den ersten übereinstimmenden Teilstring, bei Verwendung von speichernden Gruppen ist das Ergebnis ein Zeilenvektor mit den entsprechenden Teilstrings (siehe den Abschnitt Gruppierung).
REGEXREPLACE(Text; regulärer Ausdruck; Ersatztext) -> ersetzt alle übereinstimmenden Teilstrings mit dem Ersetzungstext
QUERY (als Argument für die matches Option)

Außerdem können Sie reguläre Ausdrücke bei der Suche über das Menü verwenden (Bearbeiten -> Suchen und ersetzen -> Suche mithilfe regulärer Ausdrücke).

Über reguläre Ausdrücke kann man eigene Bücher schreiben, und es gibt eine große Anzahl von Tutorials und Büchern über reguläre Ausdrücke. Hier soll es nur eine kleine Einführung mit den wichtigsten Beispielen geben.

Die Implementierung in Google Sheets heißt RE2. Sie unterscheidet sich in Teilen von den sehr häufig beschriebenen PCRE (Perl Compatible Regular Expressions). Das bedeutet leider, dass Sie einige der Beispiele auf Webseiten für reguläre Ausdrücke nicht so verwenden können. RE2 unterstützt insbesondere keinen Lookahead oder Lookbehind.

Die übersetzte und für Google Sheets angepasste Referenz finden Sie unter RE2 Syntax für Google Sheets. Sie finden dort die vollständige Auflistung der verschiedenen Zeichen, z.B. der Quantifier, auf dieser Seite hier besprechen wir nur eine Auswahl.

Google Sheets arbeitet nicht mit Unicode-Charakter-Klassen (Stand 02/2022).

Beispiel 1:

=REGEXMATCH($A2;"Man")
- Diese Anweisung sucht nach der Zeichenkette "Man" in der Zelle A2. Sie findet also "Manfred", "Mann", "Der bewegte Mann" usw.
- Lehmann wird nicht gefunden, da hier ein Großbuchstabe verlangt ist.
- Mit dem Flag (?i) ist der folgende Suchstring case insensitive, mit (?-i) würde man case sensitive wieder einschalten.

A1:C5
Zeichenkette	Ausdruck	Ergebnis
Manfred	=REGEXMATCH($A2;"Man")	TRUE
Mann	=REGEXMATCH($A3;"Man")	TRUE
Der bewegte Mann.	=REGEXMATCH($A4;"Man")	TRUE
Lehmann	=REGEXMATCH($A5;"Man")	FALSE
Lehmann	=REGEXMATCH($A6;"(?i)Man")	TRUE

Elemente regulärer Ausdrücke

Ein regulärer Ausdruck in Google Sheets besteht aus den folgenden Elementen:

den zu suchenden Zeichen
wie häufig diese Zeichen vorkommen sollen
an welcher Stelle die Zeichen in der Zeichenkette vorkommen sollen
bestimmten Flags, dabei ist der wichtigste Flag die Kennzeichnung "i", das bedeutet Case insensitive, es wird also nach Groß- und Kleinbuchstaben gesucht.

Reguläre Ausdrücke werden von links nach rechts gelesen. Die Auswertung erfolgt in folgender Reihenfolge: erst Wiederholung, dann Zusammenfügen, dann der Oder-Operator (so wie Rechenregeln "Punkt vor Strich"). Man kann die Reihenfolge durch Klammersetzung verändern.

Zeichen

Fast jedes Zeichen steht für sich selbst. Ein A sucht nach einem großen A, ein %-Zeichen sucht nach einem Prozent-Zeichen usw. Das gilt auch für Leerzeichen. Die Suche ist case sensitive (es wird zwischen Groß- und Kleinbuchstaben unterschieden), Sie können den Flag (?i) verwenden, um die Suche nach Klein- und Großbuchstaben durchzuführen.

Bestimmte Steuerzeichen müssen allerdings mit einem \ (Backslash) maskiert (escaped) werden, wenn nach dem Zeichen selbst gesucht werden soll. Diese Steuerzeichen sind:

. * + ? | [ ] ( ) { } \ ^ $ :
- Wenn Sie also z.B. nach einem "." suchen wollen, müssen Sie "\." verwenden.
- In einer positiven Zeichenklasse müssen Sie diese Zeichen nicht escapen, außer dem Backslash und der Negation (also dem Escape-Zeichen am Anfang).

.
- Der Punkt steht für jedes beliebige Zeichen, außer dem Zeilenumbruch.
- Der Flag s lässt den Punkt auch den Zeilenumbruch matchen.
\w
- Buchstaben a-z und A-Z, Ziffern 0-9 und der Unterstrich _
\W
- Negation von \w
\d
- Ziffern 0-9
\D
- Negation von \d, also alles, was keine Ziffer ist (und kein leerer String).

Selbstdefinierte Zeichenklassen

Eine Zeichenklasse ist eine Gruppe von Zeichen, z.B. alle Buchstaben oder alle Zahlen. Es gibt fertige Zeichenklassen, wie \w oder \d, und selbsterstellte Zeichenklassen. Diese werden mit der eckigen Klammer definiert []. Anmerkung: Stand 02/2022 arbeitet Google Sheets nicht mit Unicode-Charakter-Klassen.

[adgh]
- Findet die Zeichen a, d, g oder h. Es wird immer das erste passende Zeichen gefunden.
[^adgh]
- Das Dach ^ direkt hinter der eckigen Klammer [ ist das Negationszeichen. Es wird alles gefunden außer diesen Zeichen und dem leeren String. Findet aber z.B. einen Zeilenumbruch.
- Findet alles außer den Zeichen a, d, g oder h und einem leeren String.
[a-u]
- Findet die Zeichen a bis u, also a, b, c, ..., u
[a-zA-Z0-9]
- Findet die Zeichen a-z, A-Z und 0-9
[a-zäöüßA-ZÄÖÜ]
- Findet die Buchstaben des deutschen Alphabetes.
[a-zàáâãäåæçèéêëìíîïðþñòóôõöøùúûüýÿßA-ZÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÞÑÒÓÔÕÖØÙÚÛÜÝ]
- Findet die ASCII-Buchstaben (Basis-Lateinisch) und die Buchstaben aus Unicode, Lateinisch 1, Ergänzung.

Müssen Sie häufiger die Unicode Zeichen verwenden, empfiehlt es sich, dafür einen benannten Bereich mit dem entsprechenden Inhalt zu erzeugen. Der reguläre Ausdruck wird sonst sehr unübersichtlich. Existiert der benannte Bereich "wle" mit dem String \wàáâãäåæçèéêëìíîïðþñòóôõöøùúûüýÿßÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÞÑÒÓÔÕÖØÙÚÛÜÝ, können wir den benannten Bereich wie folgt verwenden:

REGEXMATCH(A1;"["&wle&"]")
- Es wird im Feld A1 gesucht.
- Die entsprechende Zeichenklasse fügen wir uns aus den geschweiften Klammern und dem benannten Bereich zusammen.

Wiederholung (Quantifier)

Quantifier geben an, wie häufig das vorstehende Zeichen oder Zeichenklasse [] oder Gruppe () vorkommen soll.

*
- Das vorstehende Zeichen soll 0 mal bis beliebig oft hintereinander vorkommen (0-∞).
+
- Das vorstehende Zeichen muss 1 mal bis beliebig oft hintereinander vorkommen (1-∞).
?
- Das vorstehende Zeichen kann, muss aber nicht vorkommen (0-1).
x{n}
- Das vorstehende Zeichen muss genau n-mal vorkommen.
x{n,m}
- Das vorstehende Zeichen muss n-mal, darf aber höchstens m-mal vorkommen (n-m).

Alternativen

Um entweder die eine oder die andere Zeichengruppe zu suchen, kann man den oder-Operator verwenden. Das ist der | senkrechte Strich.

a|b
- Findet das Zeichen a oder das Zeichen b.
- Es wird das Zeichen gefunden, das im Text am weitesten links steht, also z.B. das b von "blau".

Ort des Vorkommens (Anchor)

^
- Der Anfang des Textes oder der Zeile.
$
- Das Ende des Textes oder der Zeile.

Gruppierung

Sie können Gruppen verwenden um Teile eines Strings zu erkennen, und den String anschließend neu zusammensetzen. Da REGEXREPLACE alle Vorkommen in einem String ersetzt, müssen Sie allerdings darauf achten dass Ihre Gruppen groß genug sind, also nicht mehrfach angewendet werden.

Es gibt speichernde Gruppen, deren Inhalt im Ersatztext verwendet werden kann. Speichernde Gruppen werden durch runde Klammern erzeugt. (re) - dabei steht re für regular expression. Die Gruppen werden von links nach rechts in der Reihenfolge der öffnenden Klammern nummeriert (maximal 9). Es gibt auch nicht speichernde Gruppen, deren Inhalt verworfen werden kann (und meistens auch soll). Nicht speichernde Gruppen werden durch (?:re) erzeugt.

Die Speichergruppe sprechen Sie in REGEXREPLACE über $<nummer> an, also z.B. $1. Die Gruppe $0 ist der ursprüngliche String.
In REGEXEXTRACT werden die speichernden Gruppen als Zeilenvektor ausgegeben. Haben Sie nur eine speichernde Gruppe, wird das Ergebnis in eine Zelle ausgegeben, bei mehr speichernden Gruppen als Zeilenvektor in mehrere Spalten nebeneinander.

=REGEXEXTRACT("In diesem Text kommen keine 38 Kommas vor.";"(?:\s|^)(\d+)\s(\w+)\s")
- Wir suchen nach einem Leerzeichen oder einem Zeilenanfang. Wegen dem Oder-Operator müssen wir Klammern verwenden.
- Da die Gruppe nicht speichernd ist, wird das Leerzeichen bzw. der Zeilenanfang nicht ausgegeben.
- Dann kommt eine speichernde Gruppe mit einer ganzen Zahl.
- Darauf folgt ein Leerzeichen.
- Darauf folgt ein Wort (ohne Sonderzeichen).
- Das Ergebnis wird als Zeilenvektor ausgegeben, also "38" in die erste Spalte, "Kommas" in die zweite Spalte.

Nachname, Vorname vertauschen

In Zelle A1 steht der String "Müller, Petra". Wir wollen den Namen als "Petra Müller" ausgeben.

=REGEXREPLACE(A1;"([\wäöüßÄÖÜ]+)(?:, )([\wäöüßÄÖÜ]+)";"$2 $1")
- Die erste speichernde Gruppe ([\wäöüßÄÖÜ]+) sucht nach einem oder mehreren Wortzeichen und den deutschen Umlauten. Haben Sie auch Zeichen aus anderen Zeichensätzen, gilt wieder das oben im Abschnitt Zeichenklassen gesagte.
- Die folgende nicht speichernde Gruppe (?:, ) sucht nach einem Komma, gefolgt von einem Leerzeichen. Der Inhalt dieser Gruppe wird verworfen.
- Die zweite speichernde Gruppe sucht wieder nach einem Wort.
- Im Ersatztext wird erst die zweite Gruppe, dann die erste Gruppe eingefügt.

Typische Aufgaben

Nutzernamen nach dem Schema name.name verifizieren.

^[a-z0-9][a-z0-9_]*([.][a-z0-9_]+)*$
- ^ - Am Anfang des Strings darf
- [a-z0-9] - ein Buchstabe oder eine Zahl stehen
- [a-z0-9_]* - gefolgt von keinem oder mehr Kleinbuchstaben, Zahlen oder Unterstrichen
- [.] - gefolgt von einem Punkt
- [a-z0-9_]+ - einem oder mehr Kleinbuchstaben, Zahlen oder Unterstrichen
- $ - am Ende des Strings

Nutzernamen nach dem Schema name[.name] verifizieren. Der zweite Teil ist optional und darf sich beliebig oft wiederholen.

^[a-z0-9][a-z0-9_]*([.][a-z0-9_]+)*$
- ^ - Am Anfang des Strings darf
- [a-z0-9] - ein Buchstabe oder eine Zahl stehen
- [a-z0-9_]* - gefolgt von keinem oder mehr Kleinbuchstaben, Zahlen oder Unterstrichen
- ([.][a-z0-9_]+)* - keine oder mehr Folgen von
- [.] - einem Punkt
- [a-z0-9_]+ - einem oder mehr Kleinbuchstaben, Zahlen oder Unterstrichen
- $ - am Ende des Strings

Suchen von Zahlen

Sie können mit regulären Ausdrücken nur in Zeichenketten suchen, nicht in Zahlen. Bei Bedarf müssen Sie die Zahl in eine Zeichenkette umwandeln (mit TEXT).

Ein regulärer Ausdruck liefert immer eine Zeichenkette zurück. Mit VALUE wandeln Sie die Zeichenkette wieder in eine Zahl.

Es gibt sehr viele verschiedene Möglichkeiten Zahlen darzustellen. Der reguläre Ausdruck muss entsprechend angepasst werden. Zunächst betrachten wir nur das Dezimalsystem.

Mir ist keine Möglichkeit bekannt, mit RE2 alle üblichen und gültigen Zahlausdrücke im Dezimalsystem durch einen regulären Ausdruck zu finden, und alle ungültigen Ausdrücke auszuschließen. (Das muss nichts heißen, vielleicht kennt jemand anderes einen.) Wenn VALUE aber einen ungültigen Wert zurückliefert, wurde kein gültiger Zahlausdruck erkannt. Alternativ könnten Sie zwei geschachtelte reguläre Ausdrücke verwenden.

Unter üblichen und gültigen Zahlausdrücken im Dezimalsystem verstehe ich:

Ganze Zahlen (786, -238917), rationale Zahlen (sog. Kommazahlen, also z.B. 34,24), Zahlen mit Tausendertrennzeichen (34.243.243,4356).

Ganze Zahlen

[-]?\d+
- Findet das erste Vorkommen von ganzen Zahlen, mit oder ohne negatives Vorzeichen.
- Findet "Peter ist 15" (15). "Das Ergebnis ist 17 - 15" (17). "Es sind -13." (-13). "+13" (13).

Rationale Zahlen

[-]?\d+[,]?\d*
- Der Ausdruck sucht nach einer Zahl mit oder ohne negatives Vorzeichen, gefolgt von anderen Zahlen oder einem Komma, gefolgt von anderen Zahlen.
- Er findet allerdings auch den Ausdruck 23,423,23 - der keine Zahl ist. Erkannt wird 23,423.
- Bei "Ich bin 17, aber ..." wird "17," erkannt. Das ist nicht schlimm, da VALUE den String "17," als Zahl erkennt.

Zahlen mit Tausendertrennzeichen

... tbd ...

Weblinks

... weitere gute Tutorials hier verlinken ...

Es würde sich lohnen, ein richtig gutes Tutorial zu regulären Ausdrücken in Google Sheets zu schreiben... aber das wäre vielleicht ein neues Buch ...

Die Beispiele auf dieser Seite wurden getestet: 02/2022

↑ Inhaltsverzeichnis ↑

Index →