Kapitel 17 · 17.11
Straße, Hausnummer und Zusatz aus einer Adressspalte trennen
Adressen aus einem Webshop, einer Mitgliederverwaltung oder einer alten Kundendatei stehen oft in einer einzigen Spalte: "Dorpsstraat 12-2". Für Versandetiketten, ein Mailing oder einen Import bei einem Versanddienstleister müssen Straße, Hausnummer und Zusatz getrennt sein. Mit Text in Spalten gelingt das nicht gut, denn Straßennamen enthalten selbst Leerzeichen. Wie Sie Vor- und Nachnamen trennen, lesen Sie in Vorname und Nachname aus einer Spalte trennen; Adressen sind schwieriger. Euro-Office bietet dafür die Funktionen REGEXEXTRAHIEREN und REGEXERSETZEN, die mit einem Suchmuster arbeiten.
Als Beispiel nehmen wir die Kundenliste der Bloemisterij Het Zonnetje, mit folgenden Einträgen in Spalte A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Schritt 1: die Hausnummer mit Zusatz
Geben Sie in B2 Folgendes ein:
=REGEXEXTRAHIEREN(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Das Muster zwischen den Anführungszeichen bedeutet: eine oder mehrere Ziffern (\d+), danach optional Zeichen ohne Leerzeichen (\S*, etwa -2 oder B), danach optional ein Leerzeichen mit 1 bis 3 Buchstaben (etwa " a" oder " bis"), und das ganz am Ende des Textes ($). Ergebnis: 15 a, 12-2, 8B, 7 und 120 bis. Die Jahreszahlen in "Laan 1940-1945" bleiben unberührt, weil nur der letzte Teil der Adresse zählt.
Schritt 2: die Straße
Geben Sie in C2 Folgendes ein:
=GLÄTTEN(REGEXERSETZEN(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXERSETZEN ersetzt denselben Teil durch nichts, und GLÄTTEN entfernt das Leerzeichen am Ende. Übrig bleiben Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 und so weiter.
Schritt 3: Nummer und Zusatz getrennt
Sollen auch Nummer und Zusatz getrennt werden, arbeiten Sie mit dem Ergebnis aus Schritt 1 in B2 weiter:
- Nur die Nummer, in D2: =REGEXEXTRAHIEREN(B2;"^\d+") (die Ziffern am Anfang): 15, 12, 8, 7, 120.
- Der Zusatz, in E2: =GLÄTTEN(REGEXERSETZEN(B2;"^\d+[- ]?";"")): a, 2, B, nichts, bis.
Ziehen Sie die Formeln anschließend bis zur letzten Zeile nach unten. Oder arbeiten Sie spaltenweise: Markieren Sie zum Beispiel B2:B6, geben Sie die Formel ein und drücken Sie Strg+Enter; dann steht sie auf einmal in allen markierten Zellen.

Eine Adresse mit Kommas
Steht die gesamte Adresse in einer Zelle, etwa "Dorpsstraat 12, 1234 AB Utrecht", dann teilt =TEXTTEILEN(A9;", ") sie am Komma auf: "Dorpsstraat 12" landet in der Zelle selbst und "1234 AB Utrecht" in der Zelle daneben. Postleitzahl und Ort trennen Sie danach mit TEXTVOR und TEXTNACH, die den Text vor oder nach dem n-ten Trennzeichen zurückgeben. Steht "1234 AB Utrecht" in C9:
- Postleitzahl: =TEXTVOR(C9;" ";2) ergibt 1234 AB (alles vor dem zweiten Leerzeichen).
- Ort: =TEXTNACH(C9;" ";2) ergibt Utrecht.
Fertig? Machen Sie feste Werte daraus
Die aufgeteilten Spalten sind Formeln, die auf Spalte A verweisen. Möchten Sie Spalte A löschen oder die Datei an jemanden mit einer älteren Excel-Version senden, wandeln Sie die Ergebnisse in normalen Text um: Markieren Sie die Spalten, kopieren Sie sie mit Strg+C und fügen Sie sie an derselben Stelle mit Strg+V ein. Unter den eingefügten Zellen erscheint eine kleine Schaltfläche (Strg). Klicken Sie darauf und wählen Sie unter Wert die Option Nur die Werte einfügen.

Stolperfallen
- Ausnahmen gibt es weiterhin: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Setzen Sie einen Filter auf die Spalten und prüfen Sie leere oder seltsame Ergebnisse, bevor Sie die Liste verwenden.
- Groß- und Kleinschreibung: Das Muster [A-Za-z] erfasst Klein- und Großbuchstaben. Verwenden Sie nur [a-z], fällt 8B durchs Raster.
- #NV bei REGEXEXTRAHIEREN bedeutet: Das Muster kommt nicht vor, zum Beispiel bei einer Adresse ohne Hausnummer.
- Andere Tabellenkalkulationen: Excel kennt diese REGEX-Funktionen nur in aktuellen Versionen von Microsoft 365. Eine Kollegin oder ein Kollege mit einer älteren Version sieht #NAME?. Wandeln Sie die Ergebnisse dann zuerst in Werte um.
- Ohne REGEX geht es auch, allerdings eingeschränkter: =TEXTNACH(A2;" ";-1) gibt alles nach dem letzten Leerzeichen zurück. Das funktioniert bei "Kerkplein 7" und "Dorpsstraat 12-2", bei "15 a" erhalten Sie jedoch nur das a.
