Wie kann ich Daten die sich in einer Zelle in Excel befinden trennen?
Ich habe die Daten: Herr Max Mustermann Kugelstraße 3 5789 Berlin in einer Zelle in Excel stehen. Wie kann ich diese Daten voneinander trennen, sodass jeweils Anrede, Vor- und Nachname, Straße und Postleitzahl alleine stehen ?
4 Antworten
Abhängig von der Zahl der Datensätze ist manuell manchmal sogar die schnellere Lösung. Anrede lässt sich einfach ermitteln, wenn wirklich immer Herr und Frau so ausgeschrieben sind, oder gibt es auch Hr. Dr. und co?
wenn nicht: =links(A1;4)
Wenn man fest von fünfstelligen PLZ ausgeht, könntest Du die Position der PLZ im Text so feststellen (vielleicht fällt jemandem noch was eleganteres ein)
=FINDEN("#####";WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(A2;1;"#");2;"#");3;"#");4;"#");5;"#");6;"#");7;"#");8;"#");9;"#");0;"#"))
(alles in eine Zeile: Es werden alle Zahlen durch # ersetzt und dann die Position gesucht wo ##### steht. Das sollte hoffentlich immer die PLZ sein)
Ich habe es in meinem Beispiel in eine Hilfsspalte G kopiert, damit die Formeln etwas überschaubarer bleiben. Kannst Du aber auch direkt in die nachfolgenden Formeln schreiben:
Von dieser eben ermittelten Position aus hast Du natürlich einmal die PLZ selbst: =TEIL(A1;G1;5) -> G1 wie gesagt die Position der PLZ
und alles rechts davon sollte der Ort sein:
=TEIL(A1;G1+6;99)
Für die Trennung zwischen Namen und Straße fällt mir aber keine zuverlässige Lösung ein, wie man auseinander halten kann ob es zwei Vornamen oder eine Straße mit zwei Wörtern ist. Also
Eva Maria Müller Marktplatz 7 oder
Eva Müller Am Marktplatz 8
Ich würde unterstellen, dass die meisten Straßen nur aus einem Wort bestehen und das so trennen und den Rest manuell nacharbeiten.
=TEIL(A1;6;LÄNGE(A1)-ABS(G1-LÄNGE(A1))-6)
liefert den Text nach der Anrede bis PLZ -> das dann separat versuchen in Name und Str. zu splitten.
das ist total simpel.
du musst einfach nur wort für wort "lesen" und die leerstellen als "STOP" benutzen.
wie das geht findest du ganz schnell mit google oder einfach hier: https://support.office.com/de-de/article/Text-mithilfe-von-Funktionen-in-Spalten-aufteilen-c2930414-9678-49d7-89bc-1bf66e219ea8
wenn die reihenfolge immer die selbe ist, brauchst du nur wort für wort "lesen" und dann in eine andere tabelle bzw. zelle rein schreiben. die zellen beschriftest du nur noch entsprechend und fertig
Idealerweise sind die einzelnen Adressdaten mit Tabulatoren oder einem Semikolon getrennt. Dann man ganz leicht im Optionsmenü von 'Text in Spalten' das entsprechende Trennzeichen auswählen.
Stehen hingegen nur Leerzeichen als Trennzeichen zur Verfügung wird es etwas kniffelig. Im ersten Fenster, ebenfalls im Optionsmenü (Text in Spalten), kann zwischen 'Getrennt' und 'Feste Breite' gewählt werden. Sollte kein passendes Trennzeichen vorhanden sein, kann über 'Feste Breite' ein bestimmter Trennbereich festgelegt werden. Ich befürchte zwar, dass dies auch nicht einwandfrei funktioniert. Zumindest aber insofern eine Erleichterung, dass die manuelle Korrektur etwas schneller gehen sollte.
Wie viele Datensätze sind es und aus welchem Programm stammen die Adressen? Unter Umständen bietet sich ein neuer Export, mit geeignetem Trennzeichen, der Adressen an.
Über die "Text in Spalten"-Funktion. Du markierst deine Spalte, wählst die Funktion und gibts "leerzeichen" zum Trennen an. Allerdinsg musst du dann die gewünschten Daten die du nicht trennen wolltest wieder zusammen führen.
wieder zusammen führen
Genau das kann seehhr aufwändig werden: wennzB mal Hausnummern fehlen, mal drei Vornamen existieren (Johann Baptist Nepomuk Mustermann), mal der Orts- oder Straßenname aus zwei oder mehr Teilen besteht (Kirchheim u.Teck, Freiburg i. Br., Innerer Ring, Karl Maria v. Weber-Platz, Tegernseer Landstraße). Es gibt komplizierte Methoden, zumindest zu testen, ob eine Zahl eine bis zu 4-stellige Hausnummer oder eine 5-stellige Postleitzahl ist - mehr kann wohl kein Programm leisten (und jetzt um halb zwei nachts sowieso nicht mehr)
Aber man kann nach der Aktion Text in Spalten einige Leerspalten einfügen, um Manövrierraum zum schnelleren Verschieben der getrennten Zellen zu haben.
Darin wird aber nicht die Logik beschrieben, wie man zwischen Namen und Straßen unterscheiden kann und das ist nach meinem Verständnis bei dieser Frage das Hauptproblem.