Wie ist eine IBAN aufgebaut?
Eine IBAN entspricht einem international standardisierten Format. Sie besteht in Deutschland aus 22 Zeichen in folgendem Format:
LLPP BBBB BBBB NNNN NNNN NN
Die Landeskennung
(L = 2 Buchstaben)
Dieses Kennzeichen besteht aus 2 Buchstaben und steht für das entsprechende Land, in welchem das Konto geführt wird. DE = Deutschland, NL = Niederlande usw.
Die Prüfziffer
(P = 2 Ziffern)
Die Prüfziffer berechnet sich aus dem Rest der IBAN und muss daher immer zu einer gültigen IBAN passen. Damit kann man leicht auf Eingabefehler testen.
Die Bankleitzahl
(B = 8 Ziffern)
Über die Bankleitzahl kann das Kreditinstitut identifiziert werden. Sie entspricht der BIC und kann mit über die BLZ Liste der Bundesbank geprüft werden.
Die Kontonummer
(K = 10 Stellen)
Innerhalb des Kreditinstituts wird das Konto hiermit eindeutig identifiziert.
Wie kann ich eine IBAN in Excel berechnen?
Vereinfacht kann man eine IBAN in Deutschland mit folgender Formel aus der BLZ und der Kontonummer berechnen:
="DE"&TEXT((98-REST((62*(1+REST(A1;97))+27*REST(A2;97));97));"00")& A1&TEXT(A2;"0000000000")
Hier steht in Zelle A1 die BLZ und in A2 die Kontonummer. Leider gibt es in DE auch Unterkontonummern, also 8 Stellen Kontonummern und 2 Stellen Unterkonto, wobei auch die 8-stellige gültig ist und mit 2 Nullen aufgefüllt werden muss. Ex-IBAN berücksichtigt auch diese Sonderheiten.
Prüfen einer IBAN bei der Eingabe
Um eine IBAN bereits bei der Eingabe in Excel auf Gültigkeit zu prüfen, kann man obige Formel in einer "bedingten Formatierung" prüfen. Wir markieren den Bereich, in welchen IBANs eingegeben werden können. Nun definieren wir die bedingte Formatierung wie folgt:
- "Start/Bedingte Formatierung/Formel zur Ermittlung der..." anwählen
- In das Feld "Werte formatieren, für die diese Formel wahr ist" tragen wir ein:
=S2<>"DE"&TEXT((98-REST((62*(1+REST(TEIL(S2;5;8);97))+27*REST(RECHTS(S2;10);97)); 97));"00")&TEIL(S2;5;8)&TEXT(RECHTS(S2;10);"0000000000")
Wobei hier z.B. in Spalte S die IBANs stehen und wir in der zweiten Zeile beginnen (S2). Mit "Formatieren/Ausfüllen/rote Farbe" formatieren wir die Zellen, für welche die Formel wahr ist.
Besser geht das natürlich mit einer eigenen VBA Funktion, wie in Ex-Sepa. Hier können auch internationale IBANs auf Gültigkeit geprüft werden.
Wie kann ich eine Bankleitzahl aus einer IBAN auslesen?
Mit einer Excelformel ist das sehr einfach. Einfach in die Zelle daneben, z.B. Zelle B2, die Formel eintragen:
=TEIL(B1;5;8)
Wie liest man eine Kontonummer aus einer IBAN aus?
Hier verwendet man die Formel
=TEIL(B1;13;10)
Die IBAN steht in Zelle B1, wir beginnen ab dem 13ten Zeichen und schreiben 10 Zeichen.
Anmerkung
Die Berechnung einer IBAN anhand der Kontonummer und Bankleitzahl ist in der ISO Norm 13616 beschrieben. Hier wird insbesondere auch festgelegt, dass die Ausgabe nur durch das kontoführende Kreditinstitut zulässig ist. Daher müssen die berechneten IBANs von den entsprechenden Kreditinstituten zusätzlich validiert werden.
Mitteilung der dt. Bundesbank vom 19.12.2012
Bei einigen Zahlungsdienstleistern sind bei der Umrechnung von Kontonummern und Bankleitzahlen in IBAN (und BIC) institutsindividuelle Besonderheiten hinsichtlich der Zusammensetzung der nationalen Kontokennung BBAN (Basic Bank Account Number) aus Kontonummer und Bankleitzahl zu beachten. Die in der Deutschen Kreditwirtschaft zusammengeschlossenen Spitzenverbaende sowie die Deutsche Bundesbank haben sich daher darauf verstaendigt, alle Zahlungsdienstleister mit Bankleitzahl zu verpflichten, die fuer ihre Zahlungskonten verwendeten Berechnungsmethoden fuer IBAN (und BIC) aus Kontonummer und Bankleitzahl offenzulegen.
Jeder Berechnungsmethode (IBAN-Regel) wird ein Kennzeichen zugeordnet, das in einem neuen Feld einer erweiterten Version der Bankleitzahlendatei hinterlegt wird. Anhand dieses Kennzeichens kann die entsprechende IBAN-Regel einer Uebersicht entnommen werden.
Um den Anpassungsaufwand fuer die Anwender der Bankleitzahlendatei zu begrenzen, erfolgt die Bereitstellung der Bankleitzahlendatei ab dem 3. Juni 2013 in zwei Versionen: einer unveraenderten Version und einer um das neue Kennzeichen erweiterten Version.
Aktuell unterstützt Ex-Iban diese zusätzliche Bank-Information nicht. Da Ex-Iban aber schon jetzt ausreichend sicher funktioniert und weitere Konvertierungstools von den Banken angeboten werden, ist eine weitere Programmanpassung nicht mehr geplant.
jetzt bestellen !