nerofu
Goto Top

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 face-smile

LG,
Nero

Content-ID: 353771

Url: https://administrator.de/contentid/353771

Ausgedruckt am: 05.11.2024 um 16:11 Uhr

MrCount
MrCount 06.11.2017 um 10:17:32 Uhr
Goto Top
Servus,

vielleicht hilft dir das hier weiter...
Nerofu
Nerofu 06.11.2017 um 10:20:29 Uhr
Goto Top
Perfekt!
Genau das habe ich gesucht, dankeschön.
Anscheinend kann ich nicht richtig googeln face-sad
Nerofu
Nerofu 06.11.2017 um 10:40:05 Uhr
Goto Top
Da war ich zu voreilig, ich komme nicht weiter.
Biber
Biber 06.11.2017 aktualisiert um 11:54:16 Uhr
Goto Top
Moin Nerofu,


Zitat von @Nerofu:

Da war ich zu voreilig, ich komme nicht weiter.
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
Nerofu
Nerofu 06.11.2017 um 12:09:55 Uhr
Goto Top
Vielen Dank für deine Schnelle Antwort,

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:
> =WENNFEHLER(TEIL(A1;SUCHEN(SPALTE($A1:$I1);A1);10);"")  
> 
Ausgabe: Gesammter Code.

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).

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).

Das wäre schon ein sehr gutes Ergebnis für mich.

lG,
Nero
Nerofu
Nerofu 06.11.2017 um 12:39:04 Uhr
Goto Top
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
Biber
Lösung Biber 07.11.2017 aktualisiert um 16:45:08 Uhr
Goto Top
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"
=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))))
"Hausnummernanteil"
=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