7
Adressen Trennen In Excel
Hallo zusammen!
Ich möchte gerne Hausnummern von Straßen trennen.
Allerding s ist der Datensatz den ich bekommen habe relativ "wild".
Das heiß es gibt Leerzeichen zwischen Hausnummern wie z.B. "22 - 39" oder "23 A".
Hier einmal ein paar Beispiele (Ausgedacht):
Eingabe:
Teststr. 1 A
Admin Straße 1 - 2
Coolerweg 22
Was ich haben Möchte ist:
Teststr. | 1 A
Admin Straße | 1 - 2
Coolerweg | 22
"|" Steht für andere Zelle.
Nun habe ich ein wenig rumexperimentiert und habe es mit "=LINKS(A1;FINDEN("1";A1)-1)" speziell für die erste Zeile versucht, und es funktioniert auch soweit, dass ich die Straße schonmal habe.
Nun hätte ich aber gerne dass ich nicht nach der "1" Speziell suche, sonder generell nach Zahlen. Also im Prinzip: =LINKS(A1;FINDEN("n";A1)-1) n = natürliche Zahl
Ich habe wenig Erfahrung mit Excel also kann es gut sein, dass ich etwas total offensichtliches übersehe.
Vielen Dank schonmal
LG,
Nero
Ich möchte gerne Hausnummern von Straßen trennen.
Allerding s ist der Datensatz den ich bekommen habe relativ "wild".
Das heiß es gibt Leerzeichen zwischen Hausnummern wie z.B. "22 - 39" oder "23 A".
Hier einmal ein paar Beispiele (Ausgedacht):
Eingabe:
Teststr. 1 A
Admin Straße 1 - 2
Coolerweg 22
Was ich haben Möchte ist:
Teststr. | 1 A
Admin Straße | 1 - 2
Coolerweg | 22
"|" Steht für andere Zelle.
Nun habe ich ein wenig rumexperimentiert und habe es mit "=LINKS(A1;FINDEN("1";A1)-1)" speziell für die erste Zeile versucht, und es funktioniert auch soweit, dass ich die Straße schonmal habe.
Nun hätte ich aber gerne dass ich nicht nach der "1" Speziell suche, sonder generell nach Zahlen. Also im Prinzip: =LINKS(A1;FINDEN("n";A1)-1) n = natürliche Zahl
Ich habe wenig Erfahrung mit Excel also kann es gut sein, dass ich etwas total offensichtliches übersehe.
Vielen Dank schonmal
LG,
Nero
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 353771
Url: https://administrator.de/contentid/353771
Printed on: April 16, 2024 at 09:04 o'clock
7 Comments
Latest comment
Perfekt!
Genau das habe ich gesucht, dankeschön.
Anscheinend kann ich nicht richtig googeln
Genau das habe ich gesucht, dankeschön.
Anscheinend kann ich nicht richtig googeln
Da war ich zu voreilig, ich komme nicht weiter.
Moin Nerofu,
Wenn du das nächste Mal eine so tolle Fehlerbeschreibung hier ablädst, dann wird sich dieser Zustand nicht ändern.
Denn dann bist du bei allen auf der Ignore-Liste.
Wenn in A1, A2,..Ax deine zu zerlegenden Strasse+Hausnummern stehen:
in B1 ff die Strasse
in C1 ff der Hausnummernanteil:
Anmerkung:
- das "SPALTE($A1:$J1) bleibt so, auch wenn dein Zerlege-Wert irgendwo anders stehen sollte. Damit werden nur die Ziffern 1-9 abgeklappert.
- die "10" in der zweiten Formel beschränkt den Hausnummernteil auf 10 Zeichen. Wenn du mehr brauchst, hochsetzen.
- "Eine Str 123" ind "Andere Str. 17a 1.Etage" werden richtig zerlegt, und Str ohne Hausnumner in "altes Feld" und "" (Leerstring).
Schiefgehen wird es natürlich bei der "Straße des 17. Juni 86b" , wenn also Ziffern im Strassennamen sind.
Oder wenn die Haisnummer mit einer "0" beginnt (ich suche nur nach 1-9).
Grüße
Biber
Wenn du das nächste Mal eine so tolle Fehlerbeschreibung hier ablädst, dann wird sich dieser Zustand nicht ändern.
Denn dann bist du bei allen auf der Ignore-Liste.
Wenn in A1, A2,..Ax deine zu zerlegenden Strasse+Hausnummern stehen:
in B1 ff die Strasse
=WENNFEHLER(LINKS(A1;SUCHEN(SPALTE($A1:$I1);A1)-1);A1)in C1 ff der Hausnummernanteil:
=WENNFEHLER(TEIL(A1;SUCHEN(SPALTE($A1:$I1);A1);10);"") Anmerkung:
- das "SPALTE($A1:$J1) bleibt so, auch wenn dein Zerlege-Wert irgendwo anders stehen sollte. Damit werden nur die Ziffern 1-9 abgeklappert.
- die "10" in der zweiten Formel beschränkt den Hausnummernteil auf 10 Zeichen. Wenn du mehr brauchst, hochsetzen.
- "Eine Str 123" ind "Andere Str. 17a 1.Etage" werden richtig zerlegt, und Str ohne Hausnumner in "altes Feld" und "" (Leerstring).
Schiefgehen wird es natürlich bei der "Straße des 17. Juni 86b" , wenn also Ziffern im Strassennamen sind.
Oder wenn die Haisnummer mit einer "0" beginnt (ich suche nur nach 1-9).
Grüße
Biber
Vielen Dank für deine Schnelle Antwort,
Ich stehe noch auf dem Schlauch:
Ausgabe: Gesammter Zelleninhalt
Den Part mit SPALTE($A1:$J1) habe ich nicht verstanden, worfür ist der da?
Ich habe die Daten an erster Stelle an "M2" stehen und habe deshalb einfach überall "A1" mit "M2" ersetzt, SPALTE($A1:$J1) habe ich so gelassen.
Das wäre schon ein sehr gutes Ergebnis für mich.
lG,
Nero
Ich stehe noch auf dem Schlauch:
in B1 ff die Strasse
> =WENNFEHLER(LINKS(A1;SUCHEN(SPALTE($A1:$I1);A1)-1);A1)
> Ausgabe: Gesammter Zelleninhalt
in C1 ff der Hausnummernanteil:
Ausgabe: Gesammter Code.> =WENNFEHLER(TEIL(A1;SUCHEN(SPALTE($A1:$I1);A1);10);"")
> Anmerkung:
- das "SPALTE($A1:$J1) bleibt so, auch wenn dein Zerlege-Wert irgendwo anders stehen sollte. Damit werden nur die Ziffern 1-9 abgeklappert.
- die "10" in der zweiten Formel beschränkt den Hausnummernteil auf 10 Zeichen. Wenn du mehr brauchst, hochsetzen.
- "Eine Str 123" ind "Andere Str. 17a 1.Etage" werden richtig zerlegt, und Str ohne Hausnumner in "altes Feld" und "" (Leerstring).
- das "SPALTE($A1:$J1) bleibt so, auch wenn dein Zerlege-Wert irgendwo anders stehen sollte. Damit werden nur die Ziffern 1-9 abgeklappert.
- die "10" in der zweiten Formel beschränkt den Hausnummernteil auf 10 Zeichen. Wenn du mehr brauchst, hochsetzen.
- "Eine Str 123" ind "Andere Str. 17a 1.Etage" werden richtig zerlegt, und Str ohne Hausnumner in "altes Feld" und "" (Leerstring).
Den Part mit SPALTE($A1:$J1) habe ich nicht verstanden, worfür ist der da?
Ich habe die Daten an erster Stelle an "M2" stehen und habe deshalb einfach überall "A1" mit "M2" ersetzt, SPALTE($A1:$J1) habe ich so gelassen.
Schiefgehen wird es natürlich bei der "Straße des 17. Juni 86b" , wenn also Ziffern im Strassennamen sind.
Oder wenn die Haisnummer mit einer "0" beginnt (ich suche nur nach 1-9).
Oder wenn die Haisnummer mit einer "0" beginnt (ich suche nur nach 1-9).
Das wäre schon ein sehr gutes Ergebnis für mich.
lG,
Nero
Ich habe es Gelöst bekommen:
=LINKS(M2;LÄNGE(M2)-VERWEIS(2;1/LINKS(RECHTS(M2&1;SPALTE(A:I)))/ISTFEHLER(SUCHEN(".";RECHTS(M2&0;SPALTE(A:I))));SPALTE(A:I)-1)) für die Straßen
und =GLÄTTEN(WECHSELN(M2;G2;"")) für die Nummern.
LG,
Nero
=LINKS(M2;LÄNGE(M2)-VERWEIS(2;1/LINKS(RECHTS(M2&1;SPALTE(A:I)))/ISTFEHLER(SUCHEN(".";RECHTS(M2&0;SPALTE(A:I))));SPALTE(A:I)-1)) für die Straßen
und =GLÄTTEN(WECHSELN(M2;G2;"")) für die Nummern.
LG,
Nero
Moin Nerofu,
noch ein Nachtrag.
Meine oben gepostete Version war nur schönwettergetestet: ich hatte drei Beispieladressen, "Eine Str. 123" "Xy-Str 154a 1.Treppe" und "Kirchplatz".
Mit denen funktionierte meine Formel, weil zufällig alle vorhandenen Hausnummern mit "1" anfingen.
Bei Hausnummern beginnend mit 2...9 kommt Bullshit raus.
Aber auch zu deiner Lösung möchte ich Kritik anmelden... ist auch nicht wirklich Sturm- und Hagelfest.
2 Sollbruchstellen:
a) im neuen Feld "Strasse ohne Hausnummernteil" steht dann jeweils ein trailing blank am Ende, alsi aus "Eine Str 123" wird "Eine Str " und "123". Teste mit trailing blanks sind immer Ursache für ganz lustige Folgefehler bei Suchen/Auswertungen/Vergleichen.
b) durch die Strategie, nur die letzten (rechtesten) 10 Zeichen nach Ziffern abzugrasen, wird beispielsweise in dem String "EineStrasse 232 a, im Hinterhof" keine Hausnummer gefunden obwohl eindeutig vorhanden
c) lustige Sachen passieren auch bei anderen Zusatzangaben innerhalb der letzten 10 Zeichen
"EineStrasse 232 a, in Etage 3" wird getrennt in "EineStrasse 232 a, in Etage " und "3"
"EineStrasse 232 a, im 3.Stock" wird getrennt in "EineStrasse 232 a, im 3.Stock " und ""
Ich habe aber eine Alternative dabei;
"Strassenanteil ohne Hausnummer"
"Hausnummernanteil"
im Kern macht hier AGGREGAT() die Arbeit, dieses Glätten() und Teil() drumherum ist nur Aufhybscherei des Ergebnisses..
Innerhalb von AGGRGAT() brate ich ein Array zusammen, in demm jedes Zeichen des M2-Textes hoch 0 potenziert wird. Bei Ziffern kommt dann raus 1^0=1, 2^0=1,..9^0=^, bei Nicht-Ziffern kommt Fehler heraus. So bekomme ich ein Ergebnisarray der Form "Fehler, Fehler,.., 9, 10, Fehler.."" -> davon das Minimum (ohne Fehlerwerte) sagt mir: An Position 9 im String ist die erste vorkommende Ziffer.
Die Funktion AGGREGAT() ist seit den letzten paar Excelversionen dabei und ist wirklich so schweineunsauber implementiert und dokumentiert, dass es sehr wenige Excelaner gibt, die sie auch nur auf Facebook zu ihren Freunden huinzugefügt hatten.
Aber sie hat 2 richtig schöne Vorteile
a) es lassen sich Matrixformeln ohne Ctrl+Shft+Enter-Gedöns verwenden
b) bei Parameter "Function" > 10 werden auch Fehlerwerte im Ergebnis-Array optional ignoriert
Deshalb steht bei mir in der Aggregat()-Function
NICHT: = Aggregat(5(=Minimum),.ignoriereFehlerWerte.irgendeinArray)
SONDERN:= Aggregat(15(=KKleinste),.ignoriereFehlerWerte.irgendeinArray, 1 (=den ersten der Kkleinsten==das Minimum)
Ach, manchmal wünschte ich, ich könnte auch so richtig shice programmierte Schnipsel so teuer verkaufen wie die Redmonder.
Grüße
Biber
noch ein Nachtrag.
Meine oben gepostete Version war nur schönwettergetestet: ich hatte drei Beispieladressen, "Eine Str. 123" "Xy-Str 154a 1.Treppe" und "Kirchplatz".
Mit denen funktionierte meine Formel, weil zufällig alle vorhandenen Hausnummern mit "1" anfingen.
Bei Hausnummern beginnend mit 2...9 kommt Bullshit raus.
Aber auch zu deiner Lösung möchte ich Kritik anmelden... ist auch nicht wirklich Sturm- und Hagelfest.
2 Sollbruchstellen:
a) im neuen Feld "Strasse ohne Hausnummernteil" steht dann jeweils ein trailing blank am Ende, alsi aus "Eine Str 123" wird "Eine Str " und "123". Teste mit trailing blanks sind immer Ursache für ganz lustige Folgefehler bei Suchen/Auswertungen/Vergleichen.
b) durch die Strategie, nur die letzten (rechtesten) 10 Zeichen nach Ziffern abzugrasen, wird beispielsweise in dem String "EineStrasse 232 a, im Hinterhof" keine Hausnummer gefunden obwohl eindeutig vorhanden
c) lustige Sachen passieren auch bei anderen Zusatzangaben innerhalb der letzten 10 Zeichen
"EineStrasse 232 a, in Etage 3" wird getrennt in "EineStrasse 232 a, in Etage " und "3"
"EineStrasse 232 a, im 3.Stock" wird getrennt in "EineStrasse 232 a, im 3.Stock " und ""
Ich habe aber eine Alternative dabei;
"Strassenanteil ohne Hausnummer"
=GLÄTTEN(TEIL(M2;1;WENNFEHLER(AGGREGAT(15;6;TEIL(M2;SPALTE($A$1:$Z$1);1)^0*SPALTE($A$1:$Z$1);1)-1;LÄNGE(M2))))=GLÄTTEN(TEIL(M2;LÄNGE(M20)+1;100))im Kern macht hier AGGREGAT() die Arbeit, dieses Glätten() und Teil() drumherum ist nur Aufhybscherei des Ergebnisses..
Innerhalb von AGGRGAT() brate ich ein Array zusammen, in demm jedes Zeichen des M2-Textes hoch 0 potenziert wird. Bei Ziffern kommt dann raus 1^0=1, 2^0=1,..9^0=^, bei Nicht-Ziffern kommt Fehler heraus. So bekomme ich ein Ergebnisarray der Form "Fehler, Fehler,.., 9, 10, Fehler.."" -> davon das Minimum (ohne Fehlerwerte) sagt mir: An Position 9 im String ist die erste vorkommende Ziffer.
Die Funktion AGGREGAT() ist seit den letzten paar Excelversionen dabei und ist wirklich so schweineunsauber implementiert und dokumentiert, dass es sehr wenige Excelaner gibt, die sie auch nur auf Facebook zu ihren Freunden huinzugefügt hatten.
Aber sie hat 2 richtig schöne Vorteile
a) es lassen sich Matrixformeln ohne Ctrl+Shft+Enter-Gedöns verwenden
b) bei Parameter "Function" > 10 werden auch Fehlerwerte im Ergebnis-Array optional ignoriert
Deshalb steht bei mir in der Aggregat()-Function
NICHT: = Aggregat(5(=Minimum),.ignoriereFehlerWerte.irgendeinArray)
SONDERN:= Aggregat(15(=KKleinste),.ignoriereFehlerWerte.irgendeinArray, 1 (=den ersten der Kkleinsten==das Minimum)
Ach, manchmal wünschte ich, ich könnte auch so richtig shice programmierte Schnipsel so teuer verkaufen wie die Redmonder.
Grüße
Biber
2