Supportnet / Forum / Tabellenkalkulation
Straße und Hausnr. von 1 in 2 Spalten trennen
Frage
Hallo zusammen,
ich habe 1000 Straßen mit Hausnummern. Jetzt möchte ich die Straße in Zelle A1 und die Hausnummer in Zelle B1 haben. Wie stelle ich das an.
Das Problem ist, dasss nicht vor jeder Hausnummer ein Punkt steht und dass manche Hausnummern einen Buchstaben dabei haben.
Auszug aus der Datei:
PARKSTR. 54
AUF DER PLATTE 30A
AM BURGACKER 15
AM STADTWALD 18
IM HEIMBACHTAL 8
HEIDGEN 5
RUTENSTRASSE 11
LANGE STR. 36 A
VON-SAUER-STR. 1C
HASSELBROOK 32
AHORNWEG 6D
Wäre klasse, wenn mir jemand helfen kann.
Danke an alle und Grüße
Andrea
Antwort 1 von Hajo_Zi
Hallo Andrea,
Straße von Hausnummer trennen
in D1 für die Hausnummer steht also die Array-Formel:
{=TEIL(A1;VERGLEICH(WAHR;ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1);0);LÄNGE(A1))*1}
in C1 für den Straßennamen steht:
=LINKS(A1;LÄNGE(A1)-LÄNGE(D1))
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
Von WF
Straße des 17. Juni 23
dann nimm:
{=TEIL(RECHTS(A1;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A1;4);SPALTE(1:1);1)*1);0);LÄNGE(RECHTS(A1;4)))*1}
Über 4-stellige Hausnummern gibt’s ja wohl nicht; und die Formel versagt dann nur, wenn der "Name" mit einer Zahl endet.
in C1 für den Straßennamen steht:
=LINKS(A1;LÄNGE(A1)-LÄNGE(D1))
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
Von WF
GRuß Hajo
Straße von Hausnummer trennen
in D1 für die Hausnummer steht also die Array-Formel:
{=TEIL(A1;VERGLEICH(WAHR;ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1);0);LÄNGE(A1))*1}
in C1 für den Straßennamen steht:
=LINKS(A1;LÄNGE(A1)-LÄNGE(D1))
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
Von WF
Straße des 17. Juni 23
dann nimm:
{=TEIL(RECHTS(A1;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A1;4);SPALTE(1:1);1)*1);0);LÄNGE(RECHTS(A1;4)))*1}
Über 4-stellige Hausnummern gibt’s ja wohl nicht; und die Formel versagt dann nur, wenn der "Name" mit einer Zahl endet.
in C1 für den Straßennamen steht:
=LINKS(A1;LÄNGE(A1)-LÄNGE(D1))
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
Von WF
GRuß Hajo
Antwort 2 von CaroS
Hallo Andrea,
so ordentlich wie die Beispieldaten, die Du hier angegeben hast, sind Deine echten Daten doch nicht wirklich? Das wäre ein Wunder. Wenn Menschen es schon so weit kommen lassen, dass sie Straße und Hausnummer nicht getrennt erfassen, wofür es gute oder weniger gute Gründe gibt, das ist ja egal, dann gibt es da doch noch viel mehr Unregelmäßigkeiten?
Wenn garantiert wäre, dass das letzte Leerzeichen immer zwischen Straße und Hausnummer steht, dann wäre das überhaupt kein Problem, ein Zweizeiler mit einem kleinen bisschen drumherum. Aber ich fürchte, dass die böse Praxis dann irgendwann mal fürchterlich zuschlägt - so ist sie nun mal.
Aus GOETHESTRASSE 5 A wird dann Straße = GOETHESTRASSE 5 und Nr. = A,
aus INDUSTRIESTRASSE 12 - 14 Straße = INDUSTRIESTRASSE 12 - und Nr. = 14.
Und dann gibt´s da ja noch jede Menge mehr Striche und Zeichen, die gerne mal verwendet werden, Postfächer, Aufgänge, Hinterhäuser, mehrteilige Begriffe mit und ohne Lücken, und wenn dann auch noch was fehlt, wird´s richtig interessant. Alles klar bei FRANKFURTER PLATZ (ohne Hausnummer)?
Kann man sich auf das letzte Leerzeichen verlassen?
Oder auf ein anderes eindeutiges Merkmal? Wahrscheinlich nicht.
Da muss man entweder eine Menge Möglichkeiten testen oder eine Menge Fehler in Kauf nehmen und manuell nachbessern. Ich persönlich finde es gar nicht so leicht, menschliche Intelligenz, die sofort und sicher entscheidet, was Straße und was Hausnummer ist, mit einfachen Computerprogrammen nachzubilden. Aber letztlich hängt alles nur von Deinen Daten ab. Wenn sie ordentlich sind (und die Menge groß ist), dann lohnt sich der Aufwand.
Gruß,
CaroS
so ordentlich wie die Beispieldaten, die Du hier angegeben hast, sind Deine echten Daten doch nicht wirklich? Das wäre ein Wunder. Wenn Menschen es schon so weit kommen lassen, dass sie Straße und Hausnummer nicht getrennt erfassen, wofür es gute oder weniger gute Gründe gibt, das ist ja egal, dann gibt es da doch noch viel mehr Unregelmäßigkeiten?
Wenn garantiert wäre, dass das letzte Leerzeichen immer zwischen Straße und Hausnummer steht, dann wäre das überhaupt kein Problem, ein Zweizeiler mit einem kleinen bisschen drumherum. Aber ich fürchte, dass die böse Praxis dann irgendwann mal fürchterlich zuschlägt - so ist sie nun mal.
Aus GOETHESTRASSE 5 A wird dann Straße = GOETHESTRASSE 5 und Nr. = A,
aus INDUSTRIESTRASSE 12 - 14 Straße = INDUSTRIESTRASSE 12 - und Nr. = 14.
Und dann gibt´s da ja noch jede Menge mehr Striche und Zeichen, die gerne mal verwendet werden, Postfächer, Aufgänge, Hinterhäuser, mehrteilige Begriffe mit und ohne Lücken, und wenn dann auch noch was fehlt, wird´s richtig interessant. Alles klar bei FRANKFURTER PLATZ (ohne Hausnummer)?
Kann man sich auf das letzte Leerzeichen verlassen?
Oder auf ein anderes eindeutiges Merkmal? Wahrscheinlich nicht.
Da muss man entweder eine Menge Möglichkeiten testen oder eine Menge Fehler in Kauf nehmen und manuell nachbessern. Ich persönlich finde es gar nicht so leicht, menschliche Intelligenz, die sofort und sicher entscheidet, was Straße und was Hausnummer ist, mit einfachen Computerprogrammen nachzubilden. Aber letztlich hängt alles nur von Deinen Daten ab. Wenn sie ordentlich sind (und die Menge groß ist), dann lohnt sich der Aufwand.
Gruß,
CaroS
Antwort 3 von .::Andrea::.
Vielen Dank für die schnelle und gute Hilfe! Die Formel hat funktioniert. Ich musste nur bei Straße nachhelfen, die eine Hausnummer mit Buchstabe hatten (z.B. 16A). Das waren aber nicht viele.
Danke und Grüße
Andrea
Danke und Grüße
Andrea
Antwort 4 von nighty
hi caros :)
abtastung einer zahl bzw ohne waere einfach nachzubilden,oder faellt dir nochmehr ein :)))
gruss nighty
abtastung einer zahl bzw ohne waere einfach nachzubilden,oder faellt dir nochmehr ein :)))
gruss nighty
Antwort 5 von CaroS
Hallo nighty,
wenn ich daran denke, wie Daten in der Praxis manchmal aussehen, vor allem wenn sie ohne Rücksicht auf eine spätere computergestützte Verarbeitung erfasst wurden, dann fällt mir noch eine ganze Menge ein. Weißt Du, einfach so mal ein paar Adressen einhacken, das kann so ziemlich jeder. Leider macht es auch so ziemlich jeder. Aber wenn dann noch nicht einmal daran gedacht wurde, Straße und Hausnummer getrennt einzugeben, muss man doch das schlimmste befürchten. (Zum Glück tritt ja dann der schlimmste Fall nicht immer ein.)
Da hat der Einäugige in bester Absicht gemeint, Straßennamen sind Texte, Hausnummern wegen der möglichen Buchstaben, Striche und sonstigen Zusätze auch, also "nehm wa mal ne Textspalte". Und dann ging´s richtig los!
Na ja, zum Ausdrucken von Adressetiketten ist das alles ja auch ganz prima, und wenn dann solche wichtigen Sachen wie Hinterhof oder 2. Seitenflügel rechts u. ä. einfach gleich noch mit hinten dran gehängt wurden, dann ist es einfach perfekt! Alles beieinander, so muss das sein.
Entscheidend ist immer, mit wie viel und mit welchen Hintergedanken die Leute ihre Möglichkeiten genutzt haben. Mit manchen Adressensammlungen aus frühesten dBASE-Zeiten kommt man heute noch klar, weil sie richtig ordentlich (relational) angelegt wurden, aber es gibt aus neuerer Zeit auch die umgekehrten Beispiele. Das ärgste, was ich im letzten halben Jahr hatte, war so was ähnliches wie eine prn-Datei mit ca. 300 Adressen, ca. 25 Felder pro Datensatz, die hatte man durch Ausdrucken in eine Datei als "Sicherheitskopie" erstellt. Das war das einzige, was noch existierte. Das Ding ging weder zu drucken noch sinnvoll zu importieren, es wusste auch keiner mehr, mit was für einer Anwendung das Ding überhaupt erstellt wurde. Und da die lieben Vorfahren dann auch noch alles kreuz und quer eingegeben haben, was ich aber nicht beweisen kann, kam richtig Freude auf. Endlich mal wieder Handarbeit!
Das nur zu Deiner Frage. Wenn Andreas Problem einzig darin besteht oder bestand, dass ein paar einzelne Hausnummern mit Buchstaben hinten dran nicht in Hajos Formel gepasst haben und sie sich wahrscheinlich damit beholfen hat, dass sie die Buchstaben in den Ausgangsdaten gelöscht und anschließend an die Hausnummer wieder angehängt hat, dann ist es ja gut.
Nur wenn man versucht, sich auf alle "Schweinereien" mit entsprechenden Zerlegungen und Tests einzustellen, dann beweisen einem die Daten sehr wahrscheinlich, dass es immer noch eine "Schweinerei" mehr gibt, an die man nicht gedacht hat bzw. mit seinen Formeln nicht in den Griff kriegt.
Ein ganz "normales" Beispiel (Hajo) ist ja bei uns in Berlin die Straße des 17. Juni 23. Genau den gleichen Aufbau haben auch der Maiglöckchenweg 45 Postfach 31 und die Industriestraße 12 - 14: Text Zahl Text Zahl. Nur mal ist die Hausnummer die zweite Zahl (23), mal die erste Zahl (45) und mal beide Zahlen einschließlich Bindestrich (12 - 14). Kommen Deine Formeln damit klar? Ich bin noch nicht soweit.
Gruß,
CaroS
wenn ich daran denke, wie Daten in der Praxis manchmal aussehen, vor allem wenn sie ohne Rücksicht auf eine spätere computergestützte Verarbeitung erfasst wurden, dann fällt mir noch eine ganze Menge ein. Weißt Du, einfach so mal ein paar Adressen einhacken, das kann so ziemlich jeder. Leider macht es auch so ziemlich jeder. Aber wenn dann noch nicht einmal daran gedacht wurde, Straße und Hausnummer getrennt einzugeben, muss man doch das schlimmste befürchten. (Zum Glück tritt ja dann der schlimmste Fall nicht immer ein.)
Da hat der Einäugige in bester Absicht gemeint, Straßennamen sind Texte, Hausnummern wegen der möglichen Buchstaben, Striche und sonstigen Zusätze auch, also "nehm wa mal ne Textspalte". Und dann ging´s richtig los!
Na ja, zum Ausdrucken von Adressetiketten ist das alles ja auch ganz prima, und wenn dann solche wichtigen Sachen wie Hinterhof oder 2. Seitenflügel rechts u. ä. einfach gleich noch mit hinten dran gehängt wurden, dann ist es einfach perfekt! Alles beieinander, so muss das sein.
Entscheidend ist immer, mit wie viel und mit welchen Hintergedanken die Leute ihre Möglichkeiten genutzt haben. Mit manchen Adressensammlungen aus frühesten dBASE-Zeiten kommt man heute noch klar, weil sie richtig ordentlich (relational) angelegt wurden, aber es gibt aus neuerer Zeit auch die umgekehrten Beispiele. Das ärgste, was ich im letzten halben Jahr hatte, war so was ähnliches wie eine prn-Datei mit ca. 300 Adressen, ca. 25 Felder pro Datensatz, die hatte man durch Ausdrucken in eine Datei als "Sicherheitskopie" erstellt. Das war das einzige, was noch existierte. Das Ding ging weder zu drucken noch sinnvoll zu importieren, es wusste auch keiner mehr, mit was für einer Anwendung das Ding überhaupt erstellt wurde. Und da die lieben Vorfahren dann auch noch alles kreuz und quer eingegeben haben, was ich aber nicht beweisen kann, kam richtig Freude auf. Endlich mal wieder Handarbeit!
Das nur zu Deiner Frage. Wenn Andreas Problem einzig darin besteht oder bestand, dass ein paar einzelne Hausnummern mit Buchstaben hinten dran nicht in Hajos Formel gepasst haben und sie sich wahrscheinlich damit beholfen hat, dass sie die Buchstaben in den Ausgangsdaten gelöscht und anschließend an die Hausnummer wieder angehängt hat, dann ist es ja gut.
Nur wenn man versucht, sich auf alle "Schweinereien" mit entsprechenden Zerlegungen und Tests einzustellen, dann beweisen einem die Daten sehr wahrscheinlich, dass es immer noch eine "Schweinerei" mehr gibt, an die man nicht gedacht hat bzw. mit seinen Formeln nicht in den Griff kriegt.
Ein ganz "normales" Beispiel (Hajo) ist ja bei uns in Berlin die Straße des 17. Juni 23. Genau den gleichen Aufbau haben auch der Maiglöckchenweg 45 Postfach 31 und die Industriestraße 12 - 14: Text Zahl Text Zahl. Nur mal ist die Hausnummer die zweite Zahl (23), mal die erste Zahl (45) und mal beide Zahlen einschließlich Bindestrich (12 - 14). Kommen Deine Formeln damit klar? Ich bin noch nicht soweit.
Gruß,
CaroS
Antwort 6 von nighty
hi caros :))
wie immer beeindruckend erklärt :))
schönen dank :)
gruss nighty
wie immer beeindruckend erklärt :))
schönen dank :)
gruss nighty
Antwort 7 von SnoelgOhneCookie
Hallo,
habe die Formel für mich angepasst - jetzt ergibt sich folgendes Problem:
wenn ich die Formel nach unten kopiere und dann zuerst Spalte C und dann D anwähle und die Formeln mit Strg/Shift/Enter erzeugen will, wird von der ausgehenden Zelle der Wert übernommen und ich erhalte überall den Wert von A1 und nicht von A1:A222.
Wie kann ich das machen, dass ich nicht jede Zelle einzeln anwählen und erzeugen muss?
Danke
Mark
habe die Formel für mich angepasst - jetzt ergibt sich folgendes Problem:
wenn ich die Formel nach unten kopiere und dann zuerst Spalte C und dann D anwähle und die Formeln mit Strg/Shift/Enter erzeugen will, wird von der ausgehenden Zelle der Wert übernommen und ich erhalte überall den Wert von A1 und nicht von A1:A222.
Wie kann ich das machen, dass ich nicht jede Zelle einzeln anwählen und erzeugen muss?
Danke
Mark
Antwort 8 von rainberg
Hallo Mark,
eine Matrixformel wird eingegeben und sofort, ohne weitere Zwischenaktionen mit Strg+Shift+Enter abgeschlossen.
Erst danach kannst Du die Formel in andere Zellen kopieren.
Gruß
Rainer
eine Matrixformel wird eingegeben und sofort, ohne weitere Zwischenaktionen mit Strg+Shift+Enter abgeschlossen.
Erst danach kannst Du die Formel in andere Zellen kopieren.
Gruß
Rainer
Antwort 9 von SnoelgOhneCookie
Danke Rainer,
das klappt soweit alles in Zeile 1.
Wenn ich dann Zeile 1:150 makiere und es mit Strg/Enter nach unten kopieren will, wird alles aufgehoben!
Kann doch nicht Sinn sein das in jede Zeile einzeln zu übertragen, oder?
Oder war der Cafe bei mir Heute schlecht ;-)
Mark
das klappt soweit alles in Zeile 1.
Wenn ich dann Zeile 1:150 makiere und es mit Strg/Enter nach unten kopieren will, wird alles aufgehoben!
Kann doch nicht Sinn sein das in jede Zeile einzeln zu übertragen, oder?
Oder war der Cafe bei mir Heute schlecht ;-)
Mark
Antwort 10 von rainberg
Hallo Mark,
gib die Formel in die erste Zelle ein, schließe die Eingabe mit Strg+Shift+Enter ab, fasse die Zelle unten rechts am Ausfüllkästchen an, halte die linke Maustaste gedrückt und ziehe so die Formel bis in die gewünschte Zeile runter.
Gruß
Rainer
gib die Formel in die erste Zelle ein, schließe die Eingabe mit Strg+Shift+Enter ab, fasse die Zelle unten rechts am Ausfüllkästchen an, halte die linke Maustaste gedrückt und ziehe so die Formel bis in die gewünschte Zeile runter.
Gruß
Rainer