Straße und Hausnummer mit Excel trennen Teil 1 (Variante ohne Makro)

Ein häufiges Problem ist die Übernahme von Daten, die nicht in der gewünschten Datenqualität vorliegen. Im folgenden Beitrag stelle ich eine Möglichkeit vor, wie Sie die in einer Spalte vorliegende Anschrift bestehend aus Straße und Hausnummer mit Excel trennen können und in zwei Spalten übernehmen. Die Lösung dieser Problematik ist sogar ganz ohne den Einsatz eines VBA Makros mit den Standard-Funktionen in Excel möglich.

Teil 1: Straße und Hausnummer mit Excel trennen – Variante ohne Makro
Teil 2: Straße und Hausnummer mit Excel trennen – Variante mit Makro

Vorbereitung und Datenqualität
Ich gehe für den nachfolgenden Lösungsweg davon aus, dass die Anschriften bestehend aus Straßenname und Hausnummer untereinander in Spalte A stehen. Die Spalten B bis F werden im folgenden für Hilfsspalten und die Ergebnisse benötigt, sollten daher vollständig leer sein. Am Ende des Beitrags steht eine Beispieldatei mit den Formeln zum Download als Muster bereit.

Insgesamt benötigen Sie 5 Formeln um ans Ziel zu kommen. Eine Besonderheit gibt es am Ende noch für Sonderformen der Hausnummer zu berücksichtigen. Darauf komme ich am Ende des Beitrags nochmal zurück. Ich gehe für die folgenden Schritte zunächst von der idealen Datenlage (z.B. “Hartenthaler Str. 22” oder “An der Promenade 6”) aus. Es können mehrere Leerzeichen im Straßenname vorhanden sein und es besteht zudem immer ein Leerzeichen zur Abgrenzung der Hausnummer.

Schritt für Schritt zum Ziel
1. Mit der ersten Formel entfernen Sie aus der Anschrift in Spalte A sämtliche Leerzeichen und geben die so gekürzte Anschrift in Spalte B aus. Dazu verwenden Sie die Funktion WECHSELN(Text;Alter_Text;Neuer_Text). Steht in Zelle A3 die Anschrift tragen Sie daneben in Zelle B3 die Formel

=WECHSELN(A3;" ";"")

ein. Die Anschrift wird dadurch um sämtliche Leerzeichen gekürzt.

2. Im zweiten Schritt zählen Sie die Zelllänge in den Spalten A und B und ziehen diese voneinander ab. Sie nutzen dafür die Funktion LÄNGE(Text). Tragen Sie in Zelle C3 die Formel

=LÄNGE(GLÄTTEN(A3))-LÄNGE(B3)

ein. Sie erhalten so die Anzahl der Leerzeichen ohne versehentliche Leerzeichen am Anfang oder Ende.

3. Im nächsten Schritt (gleichzeitig die letzte Hilfsspalte) wird das letzte Leerzeichen aus Spalte A in ein Prozentzeichen “%” umbenannt. Geben Sie hierfür die Formel

=WENN(C3>0;WECHSELN(A3;" ";"%";C3);"")

in Zelle D3 ein. Sie werden feststellen, dass nur jeweils das letzte Leerzeichen umbenannt wurde, während alle ggf. weiteren Leerzeichen im Straßennamen ignoriert werden.

4. In Spalte E weisen Sie mit der vorletzten Formel nun den Straßennamen ohne Hausnummer zu. Hierzu benötigen Sie zwei ineinander geschachtelte Funktionen. Mit der Funktion FINDEN(Suchtext;Text;Erstes_Zeichen) finden Sie zunächst die Stelle in der Anschrift an der das Prozentzeichen steht. Umschlossen wird diese Funktion mit der Funktion LINKS(Text;Anzahl_Zeichen). Die Funktion FINDEN() steht innerhalb der Funktion LINKS() an der Stelle für “Anzahl_Zeichen”. Damit am Ende das Prozentzeichen nicht mit dem Straßennamen in Spalte E ausgegeben wird, ergänzen Sie hinter der Funktion FINDEN() noch den Zusatz -1 und ziehen dadurch ein Zeichen ab. Tragen sie in Zelle E3 die Formel

=WENN(C3>0;LINKS(D3;FINDEN("%";D3)-1);B3)

ein.

5. Spalte F erhält im letzten Schritt noch die Hausnummer. Auch hier wird wieder eine ineinander geschachtelte Funktion benötigt. Zu den bekannten Funktionen LÄNGE() und FINDEN() wird für diese Formel noch die Funktion RECHTS(Text;Anzahl_Zeichen) benötigt. Das Prinzip für diese Formel ist, die Länge des Zellinhalts in Spalte D zu ermitteln und davon die Anzahl der Zeichen bis zum Prozentzeichen abzuziehen. Die verbliebene Anzahl an Zeichen wird beginnend vom Ende des Zellinhalts (also rechts) als Formelergebnis angezeigt. Tragen Sie in Zelle F3 die Formel

=WENN(C3>0;RECHTS(D3;LÄNGE(D3)-FINDEN("%";D3;C3));"")

ein.

Ergebnis und nachträgliche Optimierungen
Je nach Datenqualität in der Ausgangsspalte A werden so bis fast 100% der Anschriften richtig in Straßenname und Hausnummer getrennt. Nachträglich können Sie jetzt noch die Spalte E und F weiter analysieren und sehen, in welchen Fällen die Formeln zu einem nicht zufriedenstellenden Ergebnis geführt haben. Probleme gibt es wenn zwischen Straßenname und Hausnummer in Spalte A kein Leerzeichen stand (z.B. “Hauptstr.1”), im Anschluss an die Hausnummer noch getrennt von einem Leerzeichen ein Buchstabe steht (z.B. “1 a” statt “1a”) oder wenn sich die Anschrift mehrere Hausnummern erfasst und diese ebenfalls von Leerzeichen getrennt wurden (z.B. “1 – 3”, statt “1-3”).

In diesen Fällen ist nachträglich noch ein wenig Handarbeit notwendig. Ändern Sie den ursprünglichen Eintrag in Spalte A entsprechend den vorgenannten Beispielen ab, oder nutzen Sie die Makro-Variante im Teil 2. Beinhaltet die Anschrift keine Hausnummer bleibt die Hausnummer in Spalte F leer.

Um anschließend die Straßennamen und Hausnummern aus den Spalten E und F weiter verwenden zu können kopieren Sie die beiden Spalten und ersetzen über “Inhalte einfügen / Werte einfügen” die Formeln durch die Zellwerte.

Download Musterdatei

Straße und Hausnummer mit Excel trennen (ohne Makro)

Im zweiten Teil stelle ich Ihnen einen alternativen Lösungsansatz unter Einsatz eines Excel Makros vor. Diese Variante ist einfacher zu bedienen und nochmals genauer.

Weiterlesen…
Teil 2: Straße und Hausnummer mit Excel trennen – Variante mit Makro