Google Sheets-Kochbuch: Zeichenketten aufräumen

Wie entfernt man Leerzeichen aus Zeichenketten? Wie geht man mit Umlauten um? Wie extrahiert man Zahlen aus Zeichenketten?

Loginnamen aus Namen erstellen

Wir haben eine Reihe von Namen, und wollen für diese Namen Accounts erstellen. Das bedeutet:

Vornamen und Nachnamen trennen (sofern diese zusammen in einem String vorliegen)
Leerzeichen, Vorsilben, Umlaute und Sonderzeichen entfernen bzw. konvertieren
Loginnamen zusammensetzen

Das kann - je nach Komplexität der Eingangsdaten und Anforderungen an den Loginnamen - durchaus etwas langwieriger sein. Ich verwende hier die Beispieltabelle Liste Vornamen - Nachnamen.

Anschnitt der Beispieltabelle
Vorname	Nachname
Inès	García Sánchez
Aybüke	MacGavin
...	...

Wenn Sie sich die Beispieltabelle anschauen, werden Sie feststellen, dass einige Loginnamen sehr sehr lang werden würden. Sie müssen dann entscheiden, wie Sie damit umgehen: abschneiden/abkürzen/weglassen?

Hier gehe ich von folgender Konstruktion aus:

Bei Vornamen wird nur der erste Vorname verwendet, d.h. alles hinter dem ersten Leerzeichen wird verworfen.
Bindestriche werden in Vornamen wie Leerzeichen behandelt.
Bei Doppelnachnamen wird nur der erste Namensbestandteil verwendet (außer bei Namensvorsilben).
Namenvorsilben werden zusammengefügt (Al, Al-, El, O, Mac an, von)
Bindestriche in Nachnamen zählen als Leerzeichen.

Je nachdem mit welchen Eingabedaten Sie arbeiten müssen, kann diese Entscheidung noch komplizierter werden.

Zeichenkette an einer bestimmten Stelle trennen

In der Beispieltabelle liegen die Vornamen und Nachnamen bereits getrennt vor.

Wäre das nicht der Fall, haben Sie unter Umständen ein Problem. Sind Vorname und Nachname durch ein Leerzeichen getrennt, können Sie nicht erkennen, ob der jeweilige Namensbestandteil zum Vornamen oder zum Nachnamen gehört. Sowohl der Vorname kann aus mehreren Bestandteilen bestehen, als auch der Nachname. Daher enthält der zusammengesetzte Namen hoffentlich ein eindeutig nicht zum Namen gehörendes Trennzeichen.

A2:A4
Zusammengesetzter Namen
García Sánchez, Inès
MacGavin, Aybüke
...

=SPLIT(A2;",")
- Ist ein eindeutiges Trennzeichen vorhanden, können wir die Zeichenkette leicht in zwei Spalten trennen.
- Hier wird die Zelle A2 am Zeichen , (Komma) in zwei Spalten aufgeteilt.

Ab jetzt nehmen wir an, dass der entsprechende Namensbestandteil in der Zelle A2 steht.

Bindestriche durch Leerzeichen ersetzen

=SUBSTITUTE(A2;"-";" ")
- Das ist einfach, wir ersetzen alle Vorkommen des Bindestriches durch Leerzeichen.

In Kleinbuchstaben konvertieren

=LOWER(B2)
- Das könnte man natürlich auch mit einer der anderen Anweisungen kombinieren.

Vorsilben anfügen

Das trifft ja nur auf die Nachnamen zu, schadet aber bei den Vornamen nicht.

=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(C2;"^al ";"al");"^el ";"el");"^ibn ";"ibn");"^mac an ";"macan");"^o’";"o");"^von ";"von")
- Wir brauchen eine Möglichkeit, nur nach den Vorkommen am Anfang der Zeichenkette zu suchen. Das können wir mit REGEXREPLACE. Das Zeichen ^ bedeutet: am Anfang der Zeichenkette.
- Ich habe vermutlich nicht alle möglichen Varianten erwischt, diese müssten dann ergänzt werden.

Links vom Leerzeichen zurückgeben

=IFERROR(LEFT(D2;FIND(" ";D2)-1);D2)
- Wir suchen nach einem Leerzeichen. Finden wir eines, wird der Teil links vom Leerzeichen zurückgegeben. Wird keines gefunden, nehmen wir den ursprünglichen String.
- Da die Position des Leerzeichens gefunden wird, wir dieses aber nicht mit ausgeben wollen, rechnen wir FIND - 1.

Umlaute und Sonderzeichen umwandeln

Eine schöne Möglichkeit für die Anwendung einer Ersetzungstabelle auf einen Text gibt es mit der Lambda-Hilfsfunktion REDUCE.

Sonst kann man auch dies hier verwenden:

Die Umlaute und Sonderzeichen sind nicht so einfach zu finden. Eine reine Substitution von ä/ö/ü genügt nicht, bereits in Kleinbuchstaben können 32 verschiedene Buchstaben vorkommen, je nach Ihrer Datenquelle natürlich noch mehr. (Umlaute und Sonderzeichen Ersetzungstabelle). Hier böte sich ein Google Apps Script an, aber wir nehmen hier eine sehr verschachtelte Liste von SUBSTITUTE (32 Stück). Und Sie müssen dann ja nur noch den Zellbezug ändern.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1;"à";"a");"á";"a");"â";"a");"ã";"a");"ä";"ae");"å";"a");"æ";"ae");"ç";"c");"è";"e");"é";"e");"ê";"e");"ë";"e");"ì";"i");"í";"i");"î";"i");"ï";"i");"ð";"dh");"þ";"th");"ñ";"n");"ò";"o");"ó";"o");"ô";"o");"õ";"o");"ö";"oe");"ø";"oe");"ù";"u");"ú";"u");"û";"u");"ü";"ue");"ý";"y");"ÿ";"y");"ß";"ss")
- Hier werden in der Zeichenkette aus der Zelle A1 32 verschiedene Sonderzeichen ersetzt. Voraussetzung ist hier, dass die Zeichenkette bereits in Kleinbuchstaben vorliegt, aber das könnten wir natürlich noch durch ein LOWER um den Zellbezug erledigen.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1;"À";"A");"Á";"A");"Â";"A");"Ã";"A");"Ä";"Ae");"Å";"A");"Æ";"Ae");"Ç";"C");"È";"E");"É";"E");"Ê";"E");"Ë";"E");"Ì";"I");"Í";"I");"Î";"I");"Ï";"I");"Ð";"Dh");"Þ";"Th");"Ñ";"N");"Ò";"O");"Ó";"O");"Ô";"O");"Õ";"O");"Ö";"Oe");"Ø";"Oe");"Ù";"U");"Ú";"U");"Û";"U");"Ü";"Ue");"Ý";"Y")
- Und das gleiche für die Großbuchstaben.

Unsere Tabelle sieht jetzt etwa so aus:

A1:F...
Nachname	Bindestriche durch Leerzeichen ersetzen	In Kleinbuchstaben konvertieren	Vorsilben anfügen	Links vom Leerzeichen zurückgeben	Sonderzeichen umwandeln
García Sánchez	=SUBSTITUTE(A2;"-";" ")	=LOWER(B2)	=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE( REGEXREPLACE(REGEXREPLACE(C2;"^al ";"al");"^el ";"el");"^ibn ";"ibn");"^mac an ";"macan");"^o’";"o");"^von ";"von")	=IFERROR(LEFT(D2;FIND(" ";D2)-1);D2)	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2;"à";"a");"á";"a");"â";"a");"ã";"a");"ä";"ae");"å";"a");"æ";"a");"ç";"c");"è";"e");"é";"e"); "ê";"e");"ë";"e");"ì";"i");"í";"i");"î";"i");"ï";"i");"ð";"dh");"þ";"th");"ñ";"n");"ò";"o");"ó";"o");"ô";"o");"õ";"o");"ö";"oe");"ø";"oe");"ù";"u");"ú";"u");"û";"u");"ü";"ue");"ý";"y");"ÿ";"y");"ß";"ss")
MacGavin	MacGavin	macgavin	macgavin	macgavin	macgavin
Mac an Rìgh	Mac an Rìgh	mac an rìgh	macanrìgh	macanrìgh	macanrigh
...	...	...	...	...	...

Eine Zeichenkette aus mehreren Teilen zusammensetzen

=F2&"."&F28&"@example.com"
- Hier werden die Zellinhalte aus F2 und F28 mit einem Punkt dazwischen verkettet, und anschließend die E-Mail-Adresse vervollständigt.

Es kann sein, dass der Loginname nicht eindeutig ist. Das können Sie entweder jetzt überprüfen, oder werden es beim Einfügen in eine Nutzerdatenbank merken, wenn der Loginname bereits vergeben ist.

Die Beispiele auf dieser Seite wurden getestet: 08/2024

↑ Inhaltsverzeichnis ↑

Index →