Straßenname und Haus-Nr. trennen *

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.

Das Portal zum Thema Excel-Formeln