Du bist hier::--Excel Formeln – Postleitzahl auslesen

Excel Formeln – Postleitzahl auslesen

Sie haben in Excel eine Zelle, in der eine Adresse steht und Sie möchten dabei an die Postleitzahl kommen? Mit den Standardformeln geht das leider nicht, aber wir haben hier für Sie Abhilfe gefunden.

Oftmals hat man in einer Tabelle, in der Daten über eine Person stehen auch eine Zelle mit seiner vollständigen Anschrift. Haben Sie diese Zelle, möchten aber extra noch die Postleitzahl in einer Zelle haben, so geht nicht so leicht, vor allem wenn die Straßennamen verscheiden lang sind. In diesem Artikel werde ich ihnen zeigen, wie das trotzdem relativ leicht geht.

Mit Formatierung:

Schritt 1:

Zu Beginn müssen Sie natürlich erst einmal die komplette Anschrift in die Excel Tabelle eintragen. Machen Sie das am Besten in der Zelle A1. Natürlich können Sie auch eine anderen wählen, jedoch müssen dann die Zellbezüge in den Formeln/der Formel angepasst werden. Der Text in dieser Zelle ist nach dem Format „Straße Hausnummer, Postleitzahl Ort“ aufgebaut. Ich habe für mein Beispiel mit Dresden einen sächsischen Ort ausgewählt, da Sachen als Sonderfall noch eine „0“ am Anfang der Postleitzahl stehen hat, was die Formel etwas erschwert. In Zelle B1 erfolgt nun die Ausgabe der Postleitzahl. Schreiben Sie dazu in Die Zelle folgende Formel hinein:

=VERWEIS(9^9;1*TEIL(A1&"#";SPALTE(1:1);6))

Anschließend wird in der Zelle sofort die Postleitzahl zu sehen sein. Haben Sie, wie ich auch, eine sächsische Postleizahl mit der Null am Anfang gewählt, so ist diese aufgrund der Regel bezüglich der führenden Nullen verschwunden. Da eine Postleitzahl aber auch führende Nullen und immer fünf Stellen hat, muss das Format noch geändert werden. Wie das funktioniert lesen Sie in Schritt 2.

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-postleitzahl-auslesen-formel-ohne-format-470.png?nocache=1308578995442

Schritt 2:

Nun geht es an die Formatierung der Zelle. Klicken Sie dazu nun die Zelle, in der die Postleitzahl steht mit rechts an. In meinem Fall war das die Zelle B1. Haben Sie das gemacht, so wird ein Dropdown Menü erscheinen. Um nun das Format der Zelle zu ändern, müssen Sie selbsterklärend den Punkt „Zellen formatieren…“ mit einem Linksklick auswählen.

02-excel-formeln-postleitzahl-auslesen-zelle-formatieren-470.png?nocache=1308579023596

Schritt 3:

Anschließend öffnet sich ein neues Fenster, welches den Titel „Zellen formatieren“ trägt. In der linken Spalte befinden sich die verschiedenen Kategorien. Zu Beginn ist hier „Standard“ ausgewählt. Diese Kategorie ist aber nicht die Richtige, deshalb müssen Sie sie wechseln. Die Postleitzahlen befinden sich unter dem Punkt „Sonderformat“. Klicken Sie diesen also mit links an.

03-excel-formeln-postleitzahl-auslesen-sonderformat-470.png?nocache=1308579043373

Schritt 4:

Die rechte Seite in diesem Fenster hat sich nun verändert. Oben finden Sie den Punkt „Beispiel“. Dieser gibt an, wie die Zelle aussieht, wenn man die unten ausgewählte Art bestätigt. Das Beispiel zeigt schon die richtige Art an, nämlich die Zahl „01067“, also nun wirklich die Postleitzahl von Dresden. Das kommt daher, dass wenn man den Punkt in der linken Hälfte wechselt, automatisch der erste Beitrag des Überpunktes genommen wird. In unserem Fall war es gleich mit „Postleitzahl“ der richtige. Bevor Sie unten auf „OK“ klicken, vergewissern Sie sich noch, dass oben „Postleitzahl“ ausgewählt ist.

04-excel-formeln-postleitzahl-auslesen-ok-470.png?nocache=1308579065361

Schritt 5:

Nach dem Klick auf „OK“ hat sich das Fenster sofort geschlossen und die Zahl in der Zelle B1 hat sich schon umgeändert. Hier wird nun auch die führende Null mit angezeigt.

Mit automatischer Formatierung:

Wenn man statt der zu Beginn aufgeführten Formel eine andere verwendet, so kann man sich die manuelle Arbeit, die Zelle zu formatieren auch sparen. Das funktioniert so, dass schon in der Zelle das Format mit angegeben wird. Die Formel, die die gleiche Arbeit wie die erste übernimmt, das Format aber auch bestimmt, sieht so aus:

=TEXT(VERWEIS(9^9;1*TEIL(A1&"#";SPALTE(1:1);6));"00000")

Geben Sie diese nun in B1 ein, so sehen Sie, dass die Postleitzahl korrekt angezeigt wird, ohne noch die weiteren Schritte zu tätigen. Vergleichen Sie die beiden Formel allein auf ihren Aufbau, so werden Sie sehen, dass in Formel 2 Formel 1 komplett mitgenommen wird, jedoch wird um sie noch die TEXT Funktion eingebaut, welche das Format der Ausgabe automatisch festlegt.

05-excel-formeln-postleitzahl-auslesen-formel-formatiert-470.png?nocache=1308579120525

Nun haben Sie gelernt, wie man es schafft aus einem String, bestehend aus der kompletten Anschrift, die Postleitzahl auszulesen. Welche der beiden Funktionen Sie dafür benutzen, liegt völlig bei ihnen, da der einzige Unterschied zwischen beiden ist, ob die Formel formatiert werden muss.

Von |2018-07-25T14:23:39+00:00Juni 21st, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar