Suche mit regulären Ausdrücken in Google Tabellen

In diesem Tutorial zeige ich Euch wie Ihr eine Suche mit regulären Ausdrücken in Google Tabellen vornehmen könnt.
Das Thema ist sehr komplex und deshalb ist das Tutorial auch relativ lang. Ich werde erst einmal anhand von einfachen Beispielen versuchen so viel wie möglich davon rüber zu bringen.

Dieser Teil beschäftigt sich erst einmal nur mit der Suche von Einträgen innerhalb einer Google Tabelle mit Hilfe von regulären Ausdrücken. Wir werden etwas später natürlich auch Einträge ersetzen. Es gibt übrigens auch die Möglichkeit Teile aus den Suchergebnissen für das Ersetzen zu benutzen.

Kleine Beispiele für die erste Suche

Damit Ihr die Beispiele besser nachvollziehen könnt, benutze ich erst einmal eine kleine Tabelle. Ihr könnt Euch die kurze Tabelle einfach kopieren und anschließend selbst einmal testen wie das mit den regulären Ausdrücken funktioniert.

Claudia

Andrea

Michaela

Thomas

Regina

Angela

Peter

Gaby

Petra

Klaus

Micha

Thorsten

Wenn Ihr die Tabelle kopiert habt, dann sieht es bei Euch jetzt so aus:

Beispieltabelle für die Suche mit regulären Ausdrücken

Das Dialogfeld zum Suchen und Ersetzen

Aufrufen könnt Ihr die Suche über die Tastenkombination STRG + H oder wählt es über das Menü "Bearbeiten" (1) und wählt dann "Suchen und Ersetzen" (2).

Suchen und Ersetzen Google Tabellen aufrufen

Anschließend bekommt Ihr folgendes Dialogfenster

Suchen und Ersetzen Dialog Google Tabellen

Warum überhaupt reguläre Ausdrücke

Mit der normalen Suche könnt Ihr natürlich auch schon eine Menge finden und auch ersetzen. Ihr gebt einen Suchbegriff ein (1) und könnt dann je nach Einstellung (3) das gesuchte Wort innerhalb der Tabelle finden. Ihr könnt sogar entscheiden ob es sich bei der Suche um das Ergebnis oder die Formel handeln soll (7). Nehmen wir an Ihr habt in einer Zelle den Inhalt:
=3+5
Das Ergebnis ist 8. Bei der normalen Suche findet Ihr jetzt nur die 8 aber weder die 3 noch die 5. Hier könnt Ihr natürlich "Auch in Formeln suchen" aktivieren (7).

Wenn Ihr jetzt bei "Suchen" (1) eine 5 oder eine 3 eingebt findet Ihr genau diese Zelle mit der Formel. Ihr müsst für die 5 oder die 3 jeweils eine eigene Suche starten. Ihr könnt ohne die regulären Ausdrücke nicht nach beidem gleichzeitig suchen.

Reguläre Ausdrücke bieten bei der Suche eine Vielzahl von Möglichkeiten die mit der normalen Suche nicht möglich sind.

Stellt Euch vor Ihr sucht nach allen Zellen die mit einem a anfangen und einem z aufhören. Dazwischen kann alles stehen aber genau diese beiden Buchstaben müssen an den entsprechenden Positionen vorhanden sein. Oder Ihr sucht nach allen Zellen die Zahlen zwischen 1 und 5 enthalten. Oder Ihr sucht nach Zellen wo unbedingt zuerst 3 Buchstaben stehen müssen und die letzten 3 eine Zahl sein sollen dazwischen 4 beliebige Einträge. Ihr könnt sogar wie im oberen Beispiel nach den Zahlen 3 und 5 gleichzeitig suchen. Diese Beispiele sind aber wirklich nur ein Bruchteil von dem was alles möglich ist.

Reguläre Ausdrücke mit Beispielen:

Zuerst aktivieren wir die Suche mithilfe von regulären Ausdrücken. Dafür machen wir ein Häkchen bei "Suche mithilfe regulärer Ausdrücke"

Suchen mithilfe regulärer Ausdrücke Google

Das Häkchen für die Groß-/Kleinschreibung wird automatisch mit aktiviert. Deshalb wird bei der Suche auch zwischen den Klein und Großbuchstaben unterschieden. Wenn Euch das nicht so wichtig ist, dann könnt Ihr das Häkchen weg nehmen.

Einfache Buchstaben

Nehmen wir an Ihr würdet jetzt im Suchfeld also bei "Suchen"
oma
eintragen. Dann würdet Ihr folgenden Eintrag finden.
Thomas
denn dieser Name enthält die Buchstaben oma.

Würden wir im Suchfeld
ela
eintagen, würdet Ihr die Namen
Michaela und Angela finden.

Den Anfang festlegen

Ich möchte jetzt zum Beispiel nach einem Buchstaben Suchen der direkt am Anfang steht. Würden wir jetzt bei der normalen Suche einfach nur ein großes A reinschreiben dann würde die Suche die Namen Andrea und Angela natürlich auch finden, da diese Namen ein großes A enthalten und wir in meinem Beispiel auch die Beachtung der Groß- und Kleinschreibung ausgewählt haben. Was ist aber wenn alle Buchtaben groß wären. also ANDREA, ANGELA und so weiter. Da würden wir mit der normalen Suche nicht weiter kommen und sehr viel mehr Ergebnisse bekommen. Also werden wir jetzt festlegen dass der Buchstabe A direkt am Anfang vorkommen muss. Dafür gibt es ein besonderes Zeichen. Das Zirkumflex ^
Schreiben wir jetzt in dem Suchfeld
^A
bedeutet dass, das wir jetzt ein großes A am Anfang suchen. Natürlich könnt Ihr auch mehr als ein für Zeichen für den Anfang angeben. Sucht Ihr nach Anfängen wie An, Tho oder Ga dann könnt Ihr dass natürlich ebenfalls festlegen indem Ihr dieses Zirkumflex davor setzt (^An, ^Tho oder ^Ga).
Hinweis: Das funktioniert mit Zahlen oder gemischten Einträgen ebenfalls.

Das Ende festlegen

Möchte ich nur Ergebnisse erhalten wo der letzte Buchstabe ein a ist kann ich das Dollarzeichen $ benutzen.
Gebe ich jetzt als Suchbegriff
a$
ein, werden nur Ergebnisse angezeigt die als letztes Zeichen ein a enthalten. 7 Ergebnisse werden gefunden. Würden wir das $ Zeichen weg lassen würden wir auf 10 Ergebnisse kommen, da Thomas, Gaby und Klaus ebenfalls ein a enthalten, welches aber nicht am Ende steht. Auch hier könnt Ihr natürlich mehr als nur ein Zeichen angeben. Sucht Ihr zum Beispiel nach Endeungen wie ung, en oder ein, dann könnt Ihr das genau so eintragen und hinten nur das Dollarzeichen anhängen (ung$, en$ oder ein$)

Den Anfang und das Ende festlegen

Jetzt wissen wir ja bereits wie wir den Anfang und das Ende festlegen können. Den Anfang mit einem Zirkumflex also ^ und das Ende mit einem Dollarzeichen $.
Wenn wir jetzt beides gleichzeitig festlegen wollen stehen wir momentan natürlich vor einem Problem. Wir brauchen noch einen Eintrag das für beliebig viele Zeichen oder kein Zeichen dazwischen steht, da wir ja möglicherweise mehr als nur einen Eintrag mit 2 Zeichen suchen. Für ein beliebiges Zeichen können wir jetzt den Punkt . benutzen. Würden wir den Punkt jetzt dazwischen setzen finden wir allerdings nur Einträge mit 3 Zeichen (), also müssen wir jetzt noch ein Sternchen * oder das + Symbol benutzen. Das Sternchen gibt an das entweder keines oder beliebig viele Zeichen vorkommen dürfen. Würden wir also .* wählen, steht das für keins oder beliebig viele Zeichen. Nehmen wir .+ muss mindestens ein Zeichen vorhanden sein oder ebenfalls beliebig viele.
Möchte ich jetzt Einträge suchen die mit einem M anfangen und einem a aufhören kann ich die Suche folgendermaßen beschreiben:
^M.*a$
^M bedeutet großes M am Anfang
.* keins oder beliebig viele Zeichen
a$ heißt, dass ich am Ende ein a suche.
Gefunden werden jetzt die Einträge Michaela und Micha

Dies "ODER" Das finden

Falls ich mehrere Einträge suche, kann ich einen senkrechten Strich verwenden. Dieser Strich | steht für ODER. Suche ich nach Einträge die mit Cl oder Mi anfangen kann ich die Suche folgendermaßen beschreiben:
^(Cl|Mi)
Gefunden werden die Einträge Claudia, Micha und Michaela.
Hinweis: Die Runden Klammern sind wichtig weil das Zirkumflex auch für die zweite Bedingung gelten sollen. Würdet Ihr die Klammer weglassen, dann würdet Ihr jetzt nach Cl suchen welches am Anfang steht oder nach Mi wobei Mi nicht am Anfang stehen müsste.

Möchte ich Einträge finden die am Ende ein as oder er haben kann ich die Suche folgendermaßen festlegen.
(as|er)$
Auch hier sind die runden klammern wieder wichtig weil as oder er am Ende stehen soll. Würdet Ihr die runden Klammern weg lassen wird nur geprüft ob er am Ende steht. Ihr würdet dann aber auch alle Zellen finden wo irgendwo ein as vorkommt auch wenn es nicht am Ende steht.
Ihr könnt übrigens mehr als nur einen senkrechten Schrägstrich benutzen und das ganze auch kombinieren.

Oder und Bereiche finden

Für einzelne Buchstaben bei der Suche mit regulären Ausdrücken in Google Tabellen, können wir die Suche auch anders beschreiben. Ihr könnt dafür zum Beispiel eckige Klammern benutzen.
Wir suchen jetzt mal Namen die mit A, C oder G anfangen und mit a oder y aufhören. Die Suche könnte in etwa so aussehen:

^[ACG].*[ay]$
^[ACG] findet die Anfangsbuchstaben A, C oder G
.* dazwischen keins oder beliebig viele Zeichen
[ay]$ gibt an dass wir ein a oder y zum Schluss erwarten.

Das coole ist, dass Ihr sogar Bereiche angeben könnt. Ihr könnt zum Beispiel auch angeben dass Ihr die Buchstaben von A bis G suchen wollt.
Dafür könnt Ihr die Suche folgendermaßen angeben.
[a-g]
Sollen die am Anfang stehen und groß sein dann sieht es so aus:
^[A-G]
Hiermit wird alles gefunden was am Anfang ein A, B, C, D, E oder G hat.
Das gleiche funktioniert auch mit Zahlen. Sucht Ihr nach Zahlen zwischen 4 und 8 könnt Ihr einfach [4-8] angeben.

Bestimmte Anzahl von Zeichen

Suchen wir zum Beispiel Einträge mit 5 Zeichen können wir das folgendermaßen machen.
Zum Einen können wir 5 beliebige Zeichen benutzen und einfach nur 5 Punkte benutzen, dann würde die Suche so aussehen:
^…..$
Würden wir nur die 5 Punkte benutzen würden wir auch Einträge finden die mehr als 5 Zeichen beinhalten. Mit ^ und $ legen wir aber fest dass nichts davor oder dahinter vorkommen soll.
Wir können die Suche auch so beschreiben:
^.{5}$
Hier geben wir in den geschweiften Klammern einfach nur an wie viele Zeichen davon vorhanden sein sollen. Das lässt sich sogar noch etwas flexibler gestalten. Wir können nämlich auch angeben dass zwischen 4 und 5 Zeichen vorhanden sein sollen. Dafür geben wir die Anzahl mit einem Komma an.
^.{4,5}$

Suche und ersetzen mit regulären Ausdrücken in Google Tabellen

Würdet Ihr jetzt die Suche benutzen um etwas zu ersetzen, müsst Ihr im Bereich "Finden" Eure Suchanfrage natürlich entsprechend beschreiben. Sobald etwas gefunden wird, werden die gefunden Einträge durch den Teil den Ihr im Bereich "Ersetzen durch" zu stehen habt ersetzt.
Sucht Ihr also zum Beispiel nur nach einem bestimmten Anfangsbuchstaben, wird dieser bei vorhanden sein gefunden und auch nur dieser dann ersetzt. Also müsst Ihr die Suchanfrage eventuell genauer Beschreiben. Wenn ihr jetzt nach einem bestimmten Anfangsbuchstaben sucht aber der Rest der Zelle soll beim Ersetzen ebenfalls entfernt werden könnt Ihr wieder einen Punkt und ein Sternchen hinten anhängen.
Nehmen wir jetzt mal Claudia. Wir wollen jetzt Claudia durch Klaudi ersetzen. Natürlich könnte man jetzt das ganze Wort Claudia in das Eingabefeld für "Finden" schreiben und "Klaudi" in das "Ersetzen durch" Feld. Ich will das jetzt aber nur über den Anfangsbuchstaben lösen um das einfach nur mal kurz zu verdeutlichen.
Würde ich jetzt nur in das Feld "Finden":
^C
eintragen und in das "Ersetzen durch" Feld Klaudi würden wir folgendes Ergebnis bekommen:
Klaudilaudia
was natürlich nicht das Ergebnis ist, welches wir benötigen. Also müssen wir die Frage umformulieren. Nehmen wir jetzt:
^C.*
Durch den Punkt und dem Sternchen erwarten wir nach dem großen C kein oder beliebig viele Zeichen. Dadurch werden die Zeichen nach dem großen C ebenfalls gefunden und ersetzt.
Jetzt erhalten wir das korrekte Ergebnis nämlich:
Klaudi

Ersetzen mit Ergebnissen aus der Suche

Die Suche mit regulären Ausdrücken in Google Tabellen ist sehr möchtig. Wenn Ihr reguläre Ausdrücke benutzt um eine Suchanfrage zu beschreiben, könnt Ihr die gefundenen Einträge auch im "Ersetzen durch" Feld mit ausgeben lassen. Das bietet natürlich viele Möglichkeiten.
Nehmen wir ein ganz simples Beispiel. Wir haben ja unsere kleine Namensliste und stellt Euch vor wir werden einfach vor jedem Namen jetzt den Anfangsbuchstaben, einen Punkt, ein Leerzeichen und dann den ganzen Vornamen schreiben.
Das bedeutet wir beschreiben zuerst den ersten Buchstaben:
^(.)
Das heißt ein beliebiges Zeichen am Anfang. Die Klammern die wir gesetzt haben gibt den ersten Bereich an wo wir das Ergebnis im "Ersetzen durch" Feld nutzen könnten.
$1
Dieses $1 würde jetzt den ersten Buchstaben ausgeben. Würden wir mehrere Klammern benutzen wird einfach nur durchnummeriert ($1, $2, $3 …).
Jetzt brauchen wir natürlich noch den Rest von den Namen, da wir ja erst einmal nur den ersten Buchstaben finden würden. Um den Rest des Namens noch nutzen zu können muss die Suchanfrage folgendermaßen aussehen:
^(.)(.*)
Hiermit haben wir jetzt zwei Bereiche, welche wir mit den Klammern festgelegt haben. Ein Zeichen am Anfang ^(.) welches wir mit $1 auslesen können und anschließend kein oder beliebig viele Zeichen (.*) welches wir mit $2 auslesen können.
Im "Ersetzen durch" Feld können wir jetzt folgendes eintragen:
$1. $1$2
Dadurch wird der Inhalt durch den ersten Buchstaben ersetzt. Anschließend kommt ein Punkt und ein Leerzeichen und danach benötigen wir wieder den ersten Buchstaben und den Rest des Namens.

Probiert es einfach mal aus und testet auch einmal Eure eigenen Varianten 😉

… dieses Thema wird noch erweitert

Euch gefällt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉

Suche mit regulären Ausdrücken in Google Tabellen