Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Textfunktionen - BEITRAG: Straßenname und Haus-Nr. trennen *


Aufgabe
Straßenname (auch mehrgliedrig) und Haus-Nr. in Zelle A1 sollen getrennt werden.

       A               B               C       
1 Collenbachstr. 124  Collenbachstr.   124  
2 Straße des 17. Juni 35  Straße des 17. Juni   35  
3 Gartenstr. 12b  Gartenstr.   12b  
4 Kolpingstr. 4-6  Kolpingstr.   4-6  
5 am Stein VIII  am Stein   VIII 

Lösung
in B1 der Straßenname:
=LINKS(A1;FINDEN("#";WECHSELN(A1;" ";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))))-1)
oder mit der Array-Formel:
{=LINKS(A1;MAX((TEIL(A1;SPALTE(A1:IQ1);1)=" ")*SPALTE(A1:IQ1))-1)}

Die Formel versagt, wenn keine Haus-Nr. vorkommt:
Platz des 4. Juni wird dann zu: Platz des 4. / Juni
Marktplatz ergibt #WERT! Wenn die Hausnummer beginnt, darf kein Leerzeichen mehr folgen. 12 b oder 4 - 6 funktioniert also nicht.

in C1 die Haus-Nr.:
=RECHTS(A1;LÄNGE(A1)-LÄNGE(B1)-1)
oder
=GLÄTTEN(WECHSELN(A1;B1;))

B1-Optimierung von Franz Pölt (also ohne obige Einschränkungen):
=LINKS(A1;LÄNGE(A1)-VERWEIS(2;1/LINKS(RECHTS(A1&1;SPALTE(A1:Z1)))/ISTFEHLER(SUCHEN(".";RECHTS(A1&0;SPALTE(A1:Z1))));SPALTE(A1:Z1)-1))

Erläuterung
Die ganzen Handstände mit römischen Hausnummern, die es kaum gibt, haben wir jetzt weggelassen. Und in der Innenstadt von Mannheim wohnt sowieso keine Sau.