Ein durchaus gebräuchlicher Anwendungsfall in Excel: Wie trennt man in einer Adressenangabe die Hausnummer vom Straßennamen?
Die Aufgabenstellung erscheint nur auf den ersten Blick trivial, hat es aber durchaus in sich. Ich hatte in vor längerer Zeit eine Formellösung dafür vorgestellt, die aber komplex war. Und selbst damit lassen sich nicht alle Fälle behandeln.
Im heutigen Beitrag möchte ich dir eine alternative Lösung vorstellen. Auch die führt nicht zu einer 100%-Lösung, hat aber einen großen Vorteil: Sie kommt ohne eine einzige Formel aus und dürfte daher für viele Anwender deutlich attraktiver sein.
Lass dich also überraschen!
Beispieldatei herunterladen
Die Ausgangslage
Bevor ich in die Lösung einsteige: Wozu sollte man so etwas überhaupt brauchen? Ausgefeilte Datenbanksystem, wozu auch ERP- und CRM-System gehören, sehen häufig getrennte Felder für den Straßennamen und die Hausnummer vor. In vielen vorhandenen Adressenbeständen sind diese Informationen aber in einem einzigen Feld enthalten.
Ausgangslage und gewünschtes Ergebnis
Vor einer etwaigen Datenmigration steht man also vor einer mühsamen Aufgabe. Alles „zu Fuß“ bereinigen ist natürlich die zuverlässigste Lösung. Setzt aber voraus, dass die zu bearbeitenden Datenmengen überschaubar sind und die Kapazitäten für die manuelle Korrektur zur Verfügung stehen.
Excel kann dabei zumindest in einem gewissen Rahmen unterstützen. Eine mögliche Formellösung kannst du in dem oben schon erwähnten Artikel nachlesen.
Aber es geht auch einfacher.
Künstliche Intelligenz versus natürliche Dummheit
Das Thema „KI“ ist ja mittlerweile in aller Munde und auch Excel bringt seit langer Zeit (seit Excel-Version 2013!) eine gerne übersehene, aber manchmal geradezu magische KI-Funktion mit:
Die Blitzvorschau.
Sollte dir dieses Feature bisher entgangen sein, findest du hier einen kleinen Einführungsartikel. Die Blitzvorschau interpretiert manuelle Eingaben und versucht darin ein Muster zu erkennen. Dieses Muster wird dann automatisch auf alle anderen Zellen in der Spalte übertragen. Je mehr manuelle Eingaben vorliegen, desto genauer arbeitet die Mustererkennung und desto besser werden die Ergebnisse.
Damit die Blitzvorschau überhaupt zum Einsatz kommen kann, dürfen sich keine leeren Spalten zwischen der Quellspalte und der Zielspalte befinden. Da unsere Straßenangaben in Spalte A stehen, beginne ich mit der Eingabe also in Spalte B und tippe das für den ersten Straßennamen gewünschte Ergebnis direkt daneben in Spalte B, also den Straßennamen ohne Hausnummer.
Die erste Straße wird als Muster eingetippt
Nun wollen wir gleich mal die Blitzvorschau fordern: Stelle dazu sicher, dass die aktive Zelle sich noch unterhalb des gerade eingegebenen Straßennamens befindet und rufe dann das Menü „Daten | Blitzvorschau“ auf:
Die Blitzvorschau-Funktion im Datenmenü
Alternativ kannst du die Tastenkombination Strg+E nutzen.
Wie von Zauberhand werden alle nachfolgenden Zellen gefüllt. Das Ergebnis ist zumindest schon mal ein Anfang, hat aber noch deutliches Verbesserungspotenzial:
Die ersten Ergebnisse sind noch unbefriedigend
Daher werden wir diesen ersten Vorschlag nicht übernehmen. Nach einer Blitzvorschau erscheint rechts neben der ersten Zelle ein kleines Symbol und einem Untermenü, aus dem du „Rückgängig Blitzvorschau“ auswählen kannst. Oder du nutzt die normale Rückgängig-Funktion Strg+Z.
Die Blitzvorschau rückgängig machen
Danach geben wir ein paar weitere Beispiele ein:
Einzelne Fehler werden manuell korrigiert
Danach drücke ich wieder Strg+E und sehe mir das Ergebnis an. Bereits deutlich besser, wenngleich auch hier noch ein paar Fehler enthalten sind:
Die Ergebnisse werden besser
Da das Gesamtergebnis schon nahe am gewünschten Ziel ist, mache ich nicht alles rückgängig, sondern knöpfe mir gezielt die noch fehlerhaften Einträge vor. Und nach jeder manuellen Korrektur löse ich wieder mit Strg+E die Blitzvorschau aus, bis ich mit dem Ergebnis zufrieden bin – oder bis Excel mich darauf hinweist, dass es kein weiteres Muster erkennen kann:
Weitere Verbesserungen
Hinweis:
Die Blitzvorschau ist in unserem Beispiel nicht dazu gedacht, die unterschiedlichen Schreibweisen von Straßennamen zu vereinheitlichen. Es geht hier wirklich nur um die Trennung von Straße und Hausnummer.
Es werden am Ende vermutlich trotzdem einige Straßennamen unvollständig übernommen oder unkorrekt aufgeteilt worden sein, das liegt in der Natur der Sache („Künstliche Intelligenz“ versus „natürliche Dummheit“), eine perfekte Lösung gibt es leider nicht. Aber der größte Teil der Liste sollte passen.
Es fehlt: Die Hausnummer
Bevor wir die Blitzvorschau auf die Hausnummern loslassen, sollte erst die Spalte C als Text formatiert werden. Denn ansonsten gibt es unerwünschte Effekte, wenn beispielsweise „2 – 4“ plötzlich als 2. April interpretiert wird.
Die Hausnummernspalte sollte als Text formatiert werden
Danach kann es wie gehabt mit ein paar Beispieleingaben losgehen, wobei hier schon zwei Muster nach einem beherzten Strg+E ein sehr brauchbares Ergebnis liefern:
Zwei Beispiele reichen aus
Fazit
Du hast nun zwei mögliche Lösungen für das Problem gesehen: Einmal mit Hilfe von relativ komplexen Formeln und einmal über die Blitzvorschau. Welche Variante ist besser?
Die Antwort: Es kommt darauf an.
Beide Lösungen sind weit davon entfernt, perfekt zu sein. Es gibt schon in Deutschland viele Sonderfälle, beispielhaft sind die Koordinaten-Angaben in Mannheim genannt. Und bei gemischten Adressenbeständen mit inländischen und ausländischen Angaben wird es sicherlich noch unschöner.
Für die Blitzvorschau spricht:
Sie ist relativ einfach in der Handhabung und liefert schnell recht akzeptable Ergebnisse, auch wenn es manchmal mehrere Versuche braucht.
Und das ist auch schon der Nachteil der Blitzvorschau: Sie ist wenig vorherseh- und somit auch wenig nachvollziehbar.
Für die Formellösung spricht:
Sie ist besser nachvollziehbar, da die Formeln eben genau die Regeln anwenden, die man eingebaut hat. Hier greift keine künstliche Intelligenz ein.
Letztendlich ist es Geschmacksache. Wichtig ist, dass du siehst, dass es unterschiedliche Lösungen gibt (die beide nicht perfekt sind) und du für dich die passendere herauspicken kannst.
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.
Zum Urschleim gehört:
Die Blitzvorschau wird über
Datei -> Optionen -> Erweitert -> AutoVervollständigen für Zellwerte aktivieren -> automatische Blitzvorschau
aktiviert/deaktiviert.
In einer Tabelle musste ich in jeder Zeile (zeitlich verteilt) „vorhanden“ oder „nicht vorhanden“ eintragen.
Sofern schon in der Spalte vorhanden, schlug Excel schon bei der Eingabe von „v“ „vorhanden“ vor. Da ist „AutoVervollständigen für Zellwerte“.
Hallo Svenni,
danke für diese Ergänzung!
Schöne Grüße,
Martin
Sehr interessant, danke für diese tolle und doch unbekannte Funktion.
Hallo Frank,
freut mich, wenn ich etwas Neues zeigen konnte.
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
Hallo Gammaloop,
die Formel ist zwar ein echtes Monster, aber funktionierte wunderbar. Vielen Dank für die tolle Ergänzung!
Schöne Grüße,
Martin
Hallo Martin,
leider ändert hier die Schrift die Anführungszeichen SUCHEN(„.“;
wenn einer die Formel kopiert und ich Excel einfügt, funktioniert die Formel nicht
Das die Anführungszeichen geändert werden, wird der Laie nicht erkennen…
Hallo Gammaloop,
ja, ich weiß, das ist ein kleines Ärgernis. Ich habe die Anführungszeichen in deiner Formel jetzt manuell angepasst.
Schöne Grüße,
Martin