Excel Formeln – Straßenname und Hausnummer trennen

Hier werde ich ihnen zeigen, wie man es schafft in Excel eine Zelle, in der sowohl der Straßenname als auch die Hausnummer eingetragen sind, zu trennen, dass beides einzeln angezeigt wird.

Dabei werde ich ihnen zeigen, dass man nicht nur ganz gewöhnliche Straßen, die nach dem Format „Musterstraße Hausnummer“ aufgebaut sind, zerlegen kann, sondern es geht auch bei Straßen mit einer Zahl in Straßennamen oder einer römischen Zahl als Hausnummer.

Beginn:

In die Zelle A1 schreiben Sie zuerst den Namen der Straße und die Hausnummer. Zum Beispiel „Musterstraße 1“, oder aber auch „Musterstraße 1a“ oder der abgekürzten Version von „Straße“, also „Musterstr. 1“. Natürlich können Sie auch eine andere Zelle wählen, für die weitere Anleitung empfehle ich aber diese Zelle, damit auch keine Zellbezüge geändert werden müssen.

Straßenname:

Nachdem der Straßenname mit Hausnummer nun eingetragen wurde, können Sie sich nun der Ausgabe des Straßennamens widmen. Nehmen Sie dafür einfach die Zelle B1. Hier schreiben Sie nun folgende Formel hinein:

=LINKS(A1;FINDEN(„#“;WECHSELN(A1;“ „;“#“;LÄNGE(A1)-LÄNGE(WECHSELN(A1;“ „;““))))-1)

Da es keine Matrixformel ist, wird hier sofort, nachdem Sie die Formel eingeben haben der Name erscheinen. Womöglich müssen Sie davor noch die Enter Taste drücken.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Straßenname mit Matrixformel:

Eine weitere Möglichkeit der Ausgabe des Straßennamens ist die Verwendung einer Matrixformel. Löschen Sie also den aktuellen Inhalt der Zelle B1 und geben Sie nun folgendes ein:

=LINKS(A1;MAX((TEIL(A1;SPALTE(1:1);1)=“ „)*SPALTE(1:1))-1)

Wie schon erwähnt, ist die eine Matrixformel. Das bedeutet, dass die Formel nicht einfach mit der enter Taste bestätigt werden kann. Machen Sie aber nur dies, so wird hier womöglich ein Fehler oder ein falscher Wert angezeigt. Sollte die Ausgabe trotzdem korrekt sein, haben Sie nur ein günstiges Beispiel gewählt, bei dem es auch so funktioniert. Um die Formel aber richtig auszuführen, klicken Sie die Zelle B1 erneut an. Anschließend klicken Sie mit der Maus oben in die Bearbeitungszeile der Formel. Haben Sie das getan, so drücken Sie auf ihrer Tastatur gleichzeitig die Tasten STRG+SHIFT+Enter. Die Anzeige unten wird nun korrekt sein und die Formel wird sich in folgende verändert haben:

{=LINKS(A1;MAX((TEIL(A1;SPALTE(1:1);1)=“ „)*SPALTE(1:1))-1)}

Jetzt wissen sie schon über die beiden Möglichkeiten Bescheid, den Straßennamen anzuzeigen. Wie man die Hausnummer ausgeben kann wird jetzt erklärt.

Hausnummer:

Auch für die Hausnummer gibt es zwei Formeln und beide benötigen zuerst den Eintrag des Straßennamens in B1. Nun aber zu der ersten Formel. Diese funktioniert so, dass sie die Anzahl an Zeichen aus der Zelle A1 berechnet und davon die Anzahl an Zeichen aus der Zelle B1 abzieht. Von diesem Ergebnis wird dann noch einmal „1“ abgezogen. Dies Zahl wird dann für die Funktion RECHTS verwendet, welche eine Anzahl an Stellen von der rechten Seite benötigt, die ausgegeben werden sollen. Diese Anzahl an Stellen ist der Wert der zuvor berechnet wurde. Ausgegeben wird also dann der Text von der rechten Seite aus, jedoch nur eine gewisse Anzahl an Zeichen weit. Dafür sieht die Formel so aus:

=RECHTS(A1;LÄNGE(A1)-LÄNGE(B1)-1)

Haben Sie diese Formel eingegeben, so wird umgehend die Hausnummer angezeigt, in meinem Beispiel also „14“.

Hausnummer andere Möglichkeit:

Die andere Möglichkeit, mit der man es schafft, dass die Hausnummer angezeigt wird, basiert auch nicht auf einer Matrixformel. Die Vorgehensweise dieser Formel ist so: Zuerst werden die beiden Inhalte von den Zellen A1 und B1 verglichen. Anschließend wird die Funktion WECHSELN angewandt, welche bewirkt, dass von dem Inhalt der Zelle A1 nur noch die Hausnummer und ein Leerzeichen übrig bleiben. Um das Leerzeichen zu löschen wird anschließend noch die Funktion GLÄTTEN benutzt. Meine Erklärung in Form einer Formel sieht dann so aus:

=GLÄTTEN(WECHSELN(A1;B1;))

Haben Sie alles richtig gemacht, so wird auch jetzt der Wert sich nicht verändern, bei mir bleibt also die Nummer 14 stehen.

Anwendungsbeispiele:

Diese Formeln können aber nicht nur auf Straßen mit dem Format „Straßenname indische Ziffer“ angewendet werden. So kann im Straßenname auch durchaus noch eine weitere Zahl vorkommen, wie das zu Beispiel bei der „Straße des 17. Juni“-s der Fall ist. Auch damit kann das Programm fehlerfrei umgehen und ei richtiges Ergebnis liefern.

Desweiteren sind auch römische Ziffern als Hausnummern kein Problem. Heißt eine Anschrift beispielsweise „Musterstraße VIII“, so wird auch also Straßenname „Musterstraße“ und als Hausnummer „VII“ ausgegeben.

Nun wissen Sie, wie einfach es ist mit ein paar Formeln die Hausnummer und den Straßennamen eigenständig aus einem String zu bekommen, der beides beinhaltet. Jedoch kommt es bei der Formel zu Fehlern, wenn die Hausnummer mit Leerzeichen getrennt geschrieben wird, wie zum Beispiel „1 a“ oder „1 – 6“.