In einer Adressliste gibt es in- und ausländische Adressen. Es gibt drei Vorschriften für diese Liste:
- Land Deutschland
PLZ Ort erster Buchstabe groß - Bei Ländern einer Liste (USA, UK, Australien etc.)
Ort in Großbuchstaben Staat PLZ - alle übrigen Länder
PLZ Ort in Großbuchstaben
Wir haben die Liste der Länder aus Punkt 2 als formatierte Tabelle angelegt mit dem Namen tbl_umgekehrt (weil PLZ und Stadt im Vergleich zu Deutschland umgedreht werden). Dann kann die Länderliste jederzeit ergänzt werden.
Verwendet wurden vier Excel-Funktionen:
- WENN und ISTFEHLER
damit starte ich die Abfrage - GROSS
damit wird die Großschreibung der Orte erreicht - VERGLEICH
sucht das Land in der vorgegebenen Liste
Schlussendlich sah die Formel so aus:
=WENN(D2=“Deutschland“;A2&“ „&B2;WENN(ISTFEHLER(VERGLEICH(D2;tbl_umgekehrt;0));A2&“ „&GROSS(B2);GROSS(B2)&“ „&C2&“ „&A2))
In Spalte G sehen Sie die formatierte Tabelle mit den bisher erfassten Ländern, für die PLZ und Ort umgedreht werden müssen. In A steht die PLZ, in B der Ort, in C der Staat – sofern vorhanden und notwendig -, in D das Land. In E stelle ich alles zusammen.
Die Formel Schritt für Schritt:
=WENN(D2=”Deutschland”;A2&” “B2;
Wenn in D2 das Land “Deutschland” steht, dann schreibe den Inhalt von A2 plus einen Leerschritt plus den Inhalt von B2 – wichtig ist hier: keine Großschreibung des Ortes
WENN(ISTFEHLER(
Wenn bei der folgenden Berechnungen ein Fehlerwert ausgegeben wird – z. B. #NV für “nicht vorhanden” – jetzt kommt die Berechnung, die eventuell einen Fehlerwert ausgibt
VERGLEICH(D2;tbl_umgekehrt;0))
Vergleiche den Wert aus D2 in der Tabelle tbl_umgekehrt und suche den exakten Wert; kommt der Wert nicht vor, wird eine Fehlermeldung #NV ausgegeben – darauf reagiert die Funktion ISTFEHLER.
A2&” “&GROSS(B2)
Wenn also der ISTFEHLER anspringt, weil das Land nicht in der Liste vorkommt, dann schreibe den Wert aus A2 plus einen Leerschritt plus den Inhalt aus B2 in Großbuchstaben
GROSS(B2)&“ „&C2&“ „&A2
Das letzte Argument ist das gemeinsame SONST aus beiden WENN: wenn es nicht Deutschland ist und wenn kein Fehlerwert produziert wurde, dann schreibe in Großbuchstaben den Inhalt aus B2 plus einen Leerschritt plus den Inhalt aus C2 plus den Inhalt aus A2.
Stören Sie die zwei Leerschritte, die entstehen, wenn eine Adresse keinen Eintrag in C2 hat? Dann schreiben Sie im letzten Argument statt &C2& noch einmal ein WENN:
WENN(C2<>““;C2&“ „;““)&A2))
Wenn C2 nicht leer ist (<> heißt “nicht” und “” bedeutet “leer”), dann schreibe C2 plus einen Leerschritt, sonst schreibe nichts. Diese Formel sieht komplett so aus:
WENN(D2=“Deutschland“;A2&“ „&B2;WENN(ISTFEHLER(VERGLEICH(D2;tbl_umgekehrt;0));A2&“ „&GROSS(B2);GROSS(B2)&“ „&WENN(C2<>““;C2&“ „;““)&A2))
Kompliziert? Ja, wenn man sich die komplette, fertige Formel anschaut. Aber sie entsteht in Schritten. Ich habe zuerst ausprobiert, ob ich mit VERGLEICH feststellen kann, ob ein Land in meiner Liste vorkommt. VERGLEICH liefert bei Vorkommen eine Zahl, bei Nichtvorkommen #NV. Ich kann also unterscheiden:
- Liefert VERGLEICH #NV, dann schreibe PLZ und Ort in Großbuchstaben
- sonst schreibe Ort in Großbuchstaben plus Staat plus PLZ
Ob #NV geliefert wird, kann ich mit ISTFEHLER erkennen. So ist der mittlere Teil entstanden.
Dann muss ich noch Deutschland abfragen – das kam ganz vorne hin. Und als gemeinsames Sonst alle anderen Länder.
Nein, das ist nicht in drei Minuten aus dem Ärmel geschüttelt. Da ist erst ein Problem, dann eine vage Idee, dann eine Lösung mit drei, vier Hilfsspalten, dann eine langsame Reduzierung.
Nur Mut! Traut Euch!
Wer gucken will: Beispieltabelle
Bork.Blog 