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.

Straße Hausnummer trennen mit Excel

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

Musterdatei: Straße Hausnummer trennen mit ExcelStraß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

 
Gefällt Ihnen der Beitrag?

16 Gedanken zu „Straße und Hausnummer mit Excel trennen Teil 1 (Variante ohne Makro)“

  1. SUPER!
    Am Anfang bekam ich immer wieder Fehlermeldungen, bis ich feststellte, dass das Semikolonzeichen von meinem excel (2007) nicht erkannt wurde. Nach Austausch der Zeichen bekam ich ein 100% iges Ergebnis!

    KLASSE!!!!! Vielen Dank! Somit ersparte ich mir 400 Datensätze handschriftlich zu überarbeiten!

    Antworten
  2. Erstmal vielen Dank für diese tolle Hilfe. Leider gibt es noch ein Beispiel wo die Formeln nicht mitspielen wollen. Bei „Kolpingstr. 4-6 “ rechnet er am Ende 4-6=2

    Gibt es da noch eine Lösung?

    Antworten
  3. Für solche Fälle gibt es auch ein Add-In – hierbei werden die Adressen sogar korrigiert und mit Geokoordinaten versehen.

    Hier ist das Video: http://youtu.be/gRHEFnzDU0U

    (Edit Admin: Die hier erwähnte Lösung ist ein kostenpflichtiges Produkt der METHIS Dialogmarketing GmbH. Der Autor dieses Kommentars arbeitet, wie an der Email erkennbar ist, offensichtlich für die Firma.)

    Antworten
    • @Klickmeister: Zunächst einmal danke für den Kommentar. Allerdings wäre ein deutlicher Hinweis, dass Sie für die Firma arbeiten und das Excel Add-In eine kostenpflichtige kommerzielle Lösung darstellt, schon angebracht!

      Die Funktionen im Video sehen in der Tat interessant aus. Nur ist die Lösung höchstens für Firmen interessant, die regelmäßig vor diesem Problem stehen und nicht schnell mal eben ein paar Daten trennen wollen. Weitere Voraussetzung für die Nutzung des Add-Ins ist, dass die beiden Programme Microsoft .NET Framework 4.5.1 oder höher und Microsoft Visual Studio Runtime für Office installiert sind.

      Antworten
    • @Jenny: Es funktioniert auch mit der Hausnummer 1. Siehe Beispiel in der Mustermappe. Es funktioniert jedoch nicht, wenn Straßenname und Hausnummer nicht mit einem Leerzeichen getrennt sind. In diesem Fall muss man sich alternative Trennmethoden speziell für die vorliegende Datengrundlage suchen.

      Antworten
    • @CH: Die Formeln können nicht alle Eventualitäten berücksichtigen. Um dennoch ans Ziel zu kommen würde ich im ersten Schritt die Trennung mit den vorhandenen Formel vornehmen und anschließend diese Sonderfälle filtern. In Ihrem Fall soll nicht das letzte Leerzeichen, sondern das vorletzte Leerzeichen für die Trennung genutzt werden. Das können Sie in der Beispieldatei am Beispiel der Zeile 12 nachstellen. Ändern Sie hier die Formel in Spalte C12 auf =LÄNGE(A12)-LÄNGE(B12)-1 ab. Dann erhalten Sie in Spalte F12 die korrekte Hausnummer.

      Antworten

Schreibe einen Kommentar

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.