Excel kommt oft zum Einsatz, wenn Adressenbestände für den Import in ein CRM- oder ERP-System aufbereitet werden müssen. Nun sehen manche Zielsysteme für den Straßennamen und die Hausnummer zwei getrennte Felder vor, in vielen Adressenlisten ist diese Aufteilung aber nicht gegeben. Was läge also näher, als die Straße in Excel in zwei Felder aufzuteilen.
Was sich zunächst einfach anhört, stellt sich jedoch schnell als ziemlich kniffelig heraus. Denn es gibt ja gefühlt unendlich viele Variationen, wie eine Hausnummer in einer Adressenangabe aussehen kann.
In diesem Artikel werde ich ein paar Formeln vorstellen, um zumindest einen Großteil der vielen Varianten in den Griff zu bekommen und vom Straßennamen zu trennen.
Mach‘ dich auf einiges gefasst!
Start und Ziel
Um die Problematik zu verdeutlichen, habe ich eine kleine Straßenliste vorbereitet. In Spalte A stehen die vollständigen Straßennamen samt Hausnummer in einer einzigen Zelle. Das Endergebnis sollte aber aussehen wie in den Spalten C und D.
Ausgangsliste und gewünschtes Endergebnis
Du siehst schon, es ist nicht ganz so einfach zu bestimmen, an welcher Stelle genau die Trennung erfolgen muss. Sehen wir uns mal verschiedene Möglichkeiten in Excel an.
Der Textkonvertierungs-Assistent
Es gibt eine grundsätzlich sehr praktische Funktion, um den Inhalt einer einzigen Spalte in mehrere Spalten aufzuteilen. Du markierst die betreffenden Zellen und rufst folgendes Menü auf: Daten | Text in Spalten
Textkonvertierungs-Assistent, Schritt 1
Damit wird der sogenannte Textkonvertierungs-Assistent aufgerufen, denn so mancher Excel-Anwender häufig beim Import von Textdateien verwendet hat. Im ersten Schritt bieten sich zwei grundsätzliche Möglichkeiten für die Teilung: Entweder „Getrennt“ oder „Feste Breite“.
Da Straßennamen naturgemäß sehr unterschiedlich lang sind, bleiben wir also bei „Getrennt“ und klicken auf „Weiter“.
Im Schritt 2 heißt es nun, ein geeignetes Trennzeichen zu identifizieren. Am ehesten bietet sich in unserem Fall das Leerzeichen an, denn die Hausnummer ist in jedem Fall über ein Leerzeichen vom Straßennamen getrennt. Aber das Problem wird bereits in der Datenvorschau sichtbar:
Textkonvertierungs-Assistent, Schritt 2
Was in der ersten Zeile für die Albusstraße 9 noch funktioniert, passt für alle anderen Einträge überhaupt nicht mehr. Denn es gibt halt in vielen Straßenangaben mehr als nur ein einziges Leerzeichen und damit würden auch entsprechend mehr neue Spalten erstellt. Somit können wir den Textkonvertierungs-Assistenten gleich abbrechen.
Textfunktionen
Excel bietet eine ganze Reihe an Textfunktionen, wie zum Beispiel LINKS, RECHTS und TEIL, um bestimmte Teile einer Textkette herauszulösen (falls du noch nicht sattelfest in diesen Funktionen bist, findest du hier eine kleine Einführung).
Über die RECHTS-Funktion kann man beispielsweise eine bestimmte Anzahl an Zeichen vom Ende her beginnend herauslösen. Aber auch hier stellt sich das Problem: Wie viele Zeichen umfasst die Hausnummer? Oder anders gefragt:
An welcher Stelle im Straßennamen beginnt die Hausnummer?
Die Antwort auf diese letzte Frage ist der Einstieg in die Lösung.
(Falls du die Lösung gar nicht mehr abwarten kannst, dann lade dir jetzt die Beispieldatei herunter. Falls du aber etwas lernen möchtest, dann lies erst einmal weiter 😉 )
Die Startposition der Hausnummer
In den meisten Fällen liegt relativ sicher mit der Annahme, dass die Hausnummer an der Stelle beginnt, wo die erste Ziffer auftaucht. Es gibt natürlich auch hier Ausnahmen (zum Beispiel „Straße des 17. Juni“), aber wir wollen uns hier auf die überwiegende Mehrzahl der Fälle konzentrieren.
Im ersten Beispiel „Albusstr. 9“ suchen wir also nach der Position der Ziffer 9. Dafür gibt es entweder die FINDEN-Funktion:
=FINDEN(9;A2)
Startposition bestimmten mit FINDEN
Alternativ kannst du auch die Funktion SUCHEN verwenden. Die beiden Funktionen arbeiten sehr ähnlich, die Unterschiede habe ich im Artikel Suchst Du noch oder findest Du schon? beschrieben. Für unsere Zwecke spielt es aber keine Rolle und ich habe mich für die FINDEN-Funktion entschieden.
Das Ergebnis 11 besagt also, dass die Ziffer 9 an der 11. Stelle gefunden wurde. Im zweiten Beispiel „Bockenheimer Landstr. 2 – 4“ in Zelle A3 würden wir dann nach der Ziffer 2 suchen, die Formel lautet
=FINDEN(2;A3)
Nun soll das Ganze natürlich flexibel funktionieren, ohne dass wir für jede einzelne Zeile zu Fuß die Formel anpassen müssen. Wir brauchen also einen Weg, um der FINDEN-Funktion alle Ziffern von 1 – 9 zu übergeben. Dazu listet man alle Ziffern, getrennt durch ein Semikolon innerhalb von zwei geschweiften Klammern auf.
=FINDEN({1;2;3;4;5;6;7;8;9};A3)
Vorsicht, Falle!
Die FINDEN-Funktion kann grundsätzlich immer nur mit einer einzigen Ziffer umgehen kann, wir wollen aber in einem Rutsch gleich nach allen 9 Ziffern suche. Und das geht nur über eine Array-Funktion. Das weitere Vorgehen hängt von deiner Excel-Version ab.
Excel aus Microsoft 365/Office 365
Hier sind bereits neue Mechanismen zum Umgang mit Arrays integriert. Wenn du die oben genannte Formel eingibst, erkennt Excel automatisch, dass es sich um ein Array handelt und die Formel läuft sozusagen in die darunterliegenden Zellen über:
FINDEN als Array (in Microsoft 365)
Jede Zelle im Array entspricht dabei dem Ergebnis für eine der Ziffern 1 – 9. Da wir die Formel auf die Albusstr. 9 angewendet haben liefert erst der letzte Eintrag – und damit für die Ziffer 9 – einen Wert, nämlich die Position 11. Alle anderen Ziffern werden nicht in unserer Albusstr. 9 gefunden und ergeben damit einen #WERT!-Fehler.
Alle anderen Excel-Versionen
Um die Ergebnisse für alle Ziffern zu sehen, musst du erst die neun untereinanderliegenden Zellen markieren und dann die Eingabe mit der Tastenkombination Strg+Umschalt+Enter abschließen:
FINDEN als Array (alle anderen Excel-Versionen)
Die geschweiften Klammern außen um die Formel werden automatisch mit dieser Tastenkombination hinzugefügt. Und nur dann werden die Ergebnisse für alle 9 Ziffern berechnet!
Wenn wir testweise eine Straße mit mehr als nur einer Ziffer in die Zelle A2 kopieren, werden auch entsprechend mehr Ergebnisse geliefert, nämlich die jeweils erste Position der gefundenen Ziffer:
Startposition für jede gesuchte Ziffer
Um die #WERT-Fehler zu unterdrücken, kommt jetzt noch die Funktion WENNFEHLER zum Einsatz:
=WENNFEHLER(FINDEN({1;2;3;4;5;6;7;8;9};A2);"")
Bitte auch hier wieder zuerst die 9 Zellen markieren und die Eingabe mit Strg+Umschalt+Eingabe abschließen. Danach sieht die Liste so aus:
Bereinigung mit der WENNFEHLER-Funktion
Und was hilft uns das jetzt? Schließlich werden ja jetzt 9 Zeilen für nur eine einzige Straße belegt…
Das war nur ein Zwischenschritt, um dir das Prinzip hinter der Formel zu verdeutlichen. Was wir brauchen, ist ja die erste Position überhaupt, egal welche Ziffer das ist. Mit anderen Worten: Das Minimum aus der Ergebnisliste unserer Array-Formel (Stichwort: MIN-Funktion). Und außerdem wollen wir natürlich nur eine einzige Zelle dafür verwenden, und nicht 9.
Nachdem du jetzt gesehen hast, wie das Array funktioniert, markiere jetzt alle 9 Zellen und lösche die Formeln mit der Entf-Taste.
Und dann gibst du nur in die Zelle B2 diese um die MIN-Funktion erweiterte Formel ein und schließt sie wieder mit STRG+Umschalt+Eingabe ab:
=MIN(WENNFEHLER(FINDEN({1;2;3;4;5;6;7;8;9};A2);""))
Allererste Position mit MIN-Funktion
Das Array wird im Hintergrund weiterhin für alle 9 Ziffern berechnet, aber es wird eben nur der Minimum-Wert angezeigt.
Anschließend kopierst du diese Formel nach unten in alle anderen Zellen. Der Zeilenbezug passt sich jetzt automatisch an, so dass für jede einzelne Straße die Startposition der Hausnummer berechnet wird:
Array-Formel kopiert in alle Zellen
Puh, das war doch aufwändiger als gedacht, oder? Falls du bis hierher durchgehalten hast und nicht schon völlig verzweifelt ausgestiegen bist, kommt jetzt die gute Nachricht: Den schlimmsten Teil haben wir hinter uns!
Der reine Straßenname
Um nur den Straßennamen ohne Hausnummer zu erhalten, nehmen wir einfach mit Hilfe der LINKS-Funktion alle Zeichen bis zur gefundenen Hausnummernposition, die wir eben in Spalte B berechnet haben:
=LINKS(A2;B2-1)
Allerdings gibt es hier noch ein Problem, falls wir mal eine Straße ganz ohne Hausnummer haben, wie in diesem angepassten Beispiel in Zeile 8:
Fehler bei Straßen ohne Hausnummer
Also fangen wir diesen Fehler noch mit einer WENN-Funktion ab:
=LINKS(A8;WENN(B8>0;B8-1;LÄNGE(A8)))
Fehler vermeiden mit WENN-Funktion
Wenn der Wert in Spalte B größer als Null ist (d.h. es gibt eine Hausnummer), dann nehmen wir den Wert aus Spalte B. Ansonsten nehmen wir so viele Zeichen, wie der komplette Straßenname eben lang ist.
Die Hausnummer
Bleibt noch die Hausnummer, die wir auf ähnliche Weise, diesmal jedoch mit der RECHTS-Funktion bestimmen:
=RECHTS(A2;WENN(B2>0;LÄNGE(A2)-B2+1;0))
Hausnummer mit RECHTS-Funktion bestimmen
Du bist immer noch dabei? Ich bin stolz auf dich! Hier ist nochmal der Link zur Beispieldatei . Dort findest du dann auch noch eine Variante, die ganz ohne Hilfsspalte auskommt.
Wie du gesehen hast, steckt hinter dieser vermeintlich harmlosen Aufgabe doch etwas mehr Aufwand, als gedacht. Aber wenn es einfach wäre, könnte es ja auch jeder 😉
Und ja, es gibt sicherlich noch Fälle, bei denen auch diese Lösung fehlschlägt. Aber für einen Großteil der Straßennamen sollte es damit funktionieren.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Es ist in der Tat kein ganz einfaches Problem, Adressen zu trennen. Du greifst das Problem akribisch auf und zeigst Lösungswege. Letztlich wird man im Einzelfall sich durchwurschteln müssen, aber mit Deiner Anleitung wird es einfacher.
Danke für den Beitrag.
Hallo Gerhard,
ja, die vorgestellte Lösung deckt nicht alle Fälle ab und manuelle Nacharbeit wird trotzdem in bestimmten Fällen notwendig sein.
Schöne Grüße,
Martin
Eine Sache übersiehst du hier leider völlig, die blödeste Stadt für Straßennamen überhaupt: Mannheim. Die Straßennamen in der Innenstadt von Mannheim sind der absolute Tod für jeden automatischen Prozess. Diese gehen nämlich von A 1 bis U 6.
Hallo FlorensD,
ja, das stimmt, für das Koordinatensystem in Mannheim ist diese Lösung wirklich ungeeignet 🙁
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für das wunderbare Beispiel für Array in Excel und Matrixformeln die sicherlich einen Großteil von Anschriften erfolgreich anwendbar ist. Immerhin hat das auch in der Früh bei meiner Frau und mir die Überlegung ausgelöst, wie die Formel noch etwas erweitert genutzt werden kann, wenn man nicht gerade Anschriften aus Mannheim nutzt… (Stichwort: Die Straße des 17. Juni).
Hier war die Überlegung: String umdrehen und ab dem ersten Auftreten von 3 Buchstaben ist dieses eine Straße. Nun stellt sich nur die Frage, ob dieses auch mit Excel möglich ist 🙂
Insgesamt aber eine wirklich schönes Beispiel und kann direkt dafür genutzt werden um die Arbeitsweise von Array zu verstehen 🙂
Hallo Andreas,
danke für dein Feedback. Man könnte die Formeln sicherlich noch etwas aufbohren, um weitere Spezialfälle abzudecken. Es wird halt nur immer unübersichtlicher 😉
Schöne Grüße,
Martin
Wenn deutsche und ausländische Adressen in einer gemeinsamen Tabelle stehen und ausländische Adressen dabei sind, die die Hausnummer vorne haben (z. B. USA oder Frankreich), funktioniert es auch nicht mehr so einfach. Dann müsste man erst nachsehen, ob ein Nationalitäten-Kennzeichen enthalten ist und danach filtern. Wenn ich Adressen wie „22 B Rue Pradier“ und „Straße des 18. Oktober 11 A“ in einer gemeinsamen Tabelle habe, könnte es kompliziert werden, zu erkennen, an welcher Stelle die Hausnummer und der Hausnummernzusatz stehen…
Hallo Krissie,
richtig, bei Adressen aus unterschiedlichen Ländern wäre der erste Schritt, danach zu filtern bzw. separate Listen zu erstellen.
Schöne Grüße,
Martin
Hallo Martin,
heute kommt leider keine Begeisterung bei dieser Ausführung auf. Warum keine Alternativbeschreibung mit der Funktion „Blitzvorschau“ i:V. mit Zahlenformat?
Bis zum nächsten Newsletter
Hallo Bodo,
stimmt, bei solchen Formeln kommt selten Begeisterung auf. Und ja, die Blitzvorschau ist eine gute Alternative, die ich mir tatsächlich für den nächsten Artikel aufgehoben hatte 🙂
Schöne Grüße,
Martin
Das wäre ein VBA-Lösung
Sub SplitStrasseNummer()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim rg As Range
Set rg = sh.Range(„A1:C8000“)
Dim arrStrassen As Variant
arrStrassen = rg.Value
Dim i As Long, k As Long, Str As String, Nr As String
For i = LBound(arrStrassen) To UBound(arrStrassen)
For k = 1 To Len(arrStrassen(i, 1))
If Mid(arrStrassen(i, 1), k, 1) Like „#“ Then
Str = Left(arrStrassen(i, 1), k – 1)
Nr = Mid(arrStrassen(i, 1), k)
arrStrassen(i, 2) = Str
arrStrassen(i, 3) = Nr
‚Debug.Print Str, Nr
Exit For
End If
Next k
Next i
rg.Value = arrStrassen
End Sub
Hallo Silas,
vielen Dank für deine alternative Variante.
Schöne Grüße,
Martin
Danke für den tollen Beitrag zum Thema: „Trennen von Strasse und Hausnr“ . Deine Beschreibung war prima nachzuvollziehen.
Du hast mir damit sehr geholfen und Zeit gespart.
Hallo Rudolf,
gern geschehen, freut mich sehr, wenn der Artikel weitergeholfen hat.
Schöne Grüße,
Martin
eine deutliche Verbesserung erbringt die Formel
=GLÄTTEN(LINKS(A2;LÄNGE(A2)-VERWEIS(2;1/LINKS(RECHTS(A2&1;SPALTE($A$1:$Z$1)))/ISTFEHLER(SUCHEN(".";RECHTS(A2&0;SPALTE($A$1:$Z$1))));SPALTE($A$1:$Z$1)-1)))
(mit STRG+Umschalt+Eingabe speichern)
Die Formel meistert fast alle Problemfälle aus der Liste und sogar die Berliner Adressen
Platz des 4. Juni
Str. des 17. Juni 135
jedoch nicht
Sandweg 108 2. Hinterhaus
Cargo City Süd, Geb. 537, Raum 2.78
Geb. 537 Zi. 1.43/1.45
Halle C Schalter 128 R, Flughafen Frankfurt am Main 1 Terminal 1
Industriegebiet 00
M 2, 15 a
Berliner Str. 12 c/o Dr. Kerstin Ullrich
sowie den Mannheimer Adressen im historischen Stadtkern „Quadratestadt“:
C3 14
hat mir absolut geholfen. dankeschön.
Das freut mich zu hören, gern geschehen!
Schöne Grüße,
Martin