Beim Import von Adressen aus externen Datenquellen in ein Excel-Tabellenblatt kann es vorkommen, dass die Postleitzahl und der Ort sich in einer Spalte befinden. Sie können die PLZ mit der Funktion LINKS recht einfach aus der Adresse herausziehen, wenn es sich ausschließlich um Adressen aus Deutschland handelt, die alle mit 5 Ziffern beginnen. Wenn sich in der Liste ausländische Adressen mit weniger als 5 Ziffern für die Postleitzahl befinden, muss die Formel anders lauten.
In unserem Tipp erfahren Sie, wie Sie die Formel mit einer neuen Excel-Funktion erstellen, um die Postleitzahl für alle Adressen herauszuziehen.
Tabelle mit Adressen
Die Tabelle mit den Adressen sieht folgendermaßen aus:

In der Tabelle befinden sich Adressen aus Deutschland, Österreich und der Schweiz. Bei einigen ausländischen Adressen steht noch die Länderkennung vor der Postleitzahl.
Funktion REGEXEXTRAHIEREN
Wenn Sie Microsoft 365 abonniert haben, können Sie die neue Funktion REGEXTRAHIEREN nutzen, um die PLZ aus der Adresse herauszuziehen.
Die Funktion REGEXTRAHIEREN ist einer der 3 neuen Funktionen, die mit REGEX beginnen.
Die beiden anderen Funktion lauten:
REGEXTESTEN
REGEXERSETZEN
Das gemeinsame Präfix REGEX ist eine Abkürzung von regular expressions, zu Deutsch: reguläre Ausdrücke.
Reguläre Ausdrücke gibt es seit langem in vielen Programmiersprachen. Jetzt können Sie sie auch in Excel nutzen. Mit regulären Ausdrücken suchen sie nach Mustern in einem Text oder prüfen, ob ein Text ein entsprechendes Muster aufweist. Beispielsweise überprüfen Sie mit regulären Ausdrücken, ob es sich bei einer Zeichenkette um eine gültige E-Mail-Adresse, IBAN oder Rentenversicherungsnummer handelt. Bei einer deutschen IBAN prüfen Sie, ob eine Zeichenkette 22 Zeichen hat, mit DE beginnt, gefolgt von 20 Ziffern.
Die Funktion REGEXTRAHIEREN kennt 4 Argumente. Die beiden ersten Argumente müssen angegeben werden:
Die Formel in der Zelle B2 zum Extrahieren der Postleitzahl aus der Adresse sieht so aus:
=REGEXEXTRAHIEREN(A2;“\d{4,5}“;0;1)
Zur Erläuterung:
Als erstes Argument geben Sie einen Text oder einen Zellbezug ein, der auf ein Muster geprüft werden soll.
Das zweite Argument ist das Muster oder Pattern, auf das der Text geprüft werden soll. Es steht in Anführungszeichen. In unserem Beispiel lautet das Muster:
\d{4,5}
Es soll geprüft werden, ob sich in der Adresse hintereinander 4 oder 5 Ziffern befinden.
Der Ausdruck \d steht für eine Prüfung von Ziffern (d = digit). Mit dem Ausdruck in den geschweiften Klammern geben Sie an, dass mindestens 4 und maximal 5 Ziffern folgen müssen.
Mit dem dritten Argument legen Sie fest, ob nur die erste oder alle Übereinstimmungen herausgezogen werden sollen, Die Ziffer 0 steht für die erste Übereinstimmung.
Im vierten und letzten Argument können Sie angeben, ob die Groß- und Kleinschreibung beachtet werden soll. Diese Ziffer 1 steht für die Nichtbeachtung:
Wenn Sie die Formel aus Zelle B2 nach unten kopieren, sieht die Tabelle so aus:

Alle Postleitzahlen wurden erkannt und aus der Adresse herausgezogen.
Land und Ort ermitteln
Für das Extrahieren des Landes können Sie folgende Funktion nutzen:
=TEXTVOR(A2;“-„;1;;;““)
Die Funktion TEXTVOR sucht nach dem ersten Bindestrich und zieht den Text vor dem Bindestrich heraus: Existiert kein Bindestrich in der Adresse, wird ein Leerstring ausgegeben.
Den Ort können Sie mit folgender Formel aus der Adresse herausziehen:
= =TEXTNACH(A2;“ „;1)
Die Funktion zieht den Text nach dem ersten Leerzeichen aus der Adresse heraus.

Alternative Formel
Wenn Sie eine Version von Excel einsetzen, die die Funktion REGEXTRAHIEREN nicht kennt, können Sie mit der folgenden Formel in der Zelle B2 die Postleitzahlen extrahieren:
=TEXTNACH(TEXTVOR(A2;“ „);“-„;;;;TEXTVOR(A2;“ „))
Die Formel sucht mit der Funktion TEXTVOR zunächst nach dem Text vor dem ersten Leerzeichen, das vor dem Ort steht. Das Ergebnis ist eine Postleitzahl mit oder ohne Länderkennung. Danach wird nach einem Text hinter einem Bindestrich gesucht. Wenn der Bindestrich existiert, wird mit der Funktion TEXTNACH die Postleitzahl ausgeben. Falls die Funktion TEXTNACH einen Fehler auswirft, weil kein Bindestrich existiert, wird mit TEXTVOR der Text vor dem Leerzeichen gesucht.
Fazit
Mit der neuen Funktion REGHEXTRAHIEREN ziehen Sie sehr variabel bestimmte Muster aus einer Zeichenfolge heraus.
0 Kommentare