IBAN validation with BBAN format check • Pixcels.nl

In this post I describe Excel/VBA functions for IBAN validation that not only perform a Mod 97 calculation but also validate the format of the country-specific Basic Bank Account Number (BBAN).
This article elaborates on Wikipedia article IBAN: International Bank Account Number. Please read before you continue.
IBAN

Examples of IBAN numbers are:
Greece        : GR16 0110 1250 0000 0001 2300 695
United Kingdom: GB29 NWBK 6016 1331 9268 19
Switzerland   : CH93 0076 2011 6238 5295 7
In general    : ccdd bbbb bbbb bbbb

Characteristics of IBAN are:

Permitted are the digits 0-9 and the 26 upper case Latin alphabetic characters A to Z;
Maximum length is 34 characters;
Positions 1 and 2 (cc) form the country code according to ISO 3166-1 alpha-2;
Positions 3 and 4 (dd) are two check digits as a result of a Mod 97 calculation;
Position 5 and further (bbbb) is the country-specific Basic Bank Account Number (BBAN).

An IBAN should not contain spaces when transmitted electronically. However, when printed on paper, the number is usually expressed in groups of four characters separated by a single space.
Validating IBAN
A basic method for validating IBAN is to calculate the Mod 97. This can easily be improved by validating the Country Code and the country-specific BBAN format. For example, the BBAN format of the United Kingdom is 4a,14n which means: it consists of 4 alphabetic characters, followed by 14 numeric characters (see wikipedia page). Without calculating the Mod 97 we can now easily see that “GB99 1234 5678 9012 3456 78” and “GB99 ABCD 1234 5678” are not valid IBAN numbers. However, in “GB99 ABCD 5678 9012 3456 78” the BBAN conforms to the format, but after calculating the Mod 97 we would find this IBAN to be invalid.
Function IsValidIBAN()
The function IsValidIBAN() takes an IBAN string as input and returns True or False:
Public Function IsValidIBAN(ByVal IBAN As String) As Boolean
    IBAN = UCase(Replace(IBAN, " ", ""))
    IsValidIBAN = False
    If Len(IBAN) &amp;lt; 6 Or Len(IBAN) &amp;gt; 34 Then Exit Function
    If Not Left(IBAN, 4) Like "[A-Z][A-Z]##" Then Exit Function
    If Not Mid(IBAN, 5) Like GetBBANFormat(Left(IBAN, 2)) Then Exit Function
    IBAN = Mid(IBAN, 5) &amp;amp; Left(IBAN, 4)
    IsValidIBAN = (GetMod97(IBAN) = 1)
End Function

Explanation:

Line 2 removes all spaces and converts all lower case to upper case;
Line 4 does a basic check on length of the IBAN;
Line 5 checks the format of the first 4 characters ccdd;
Line 6 checks the BBAN format against the country-specific format by means of the function GetBBANFormat;
Line 7 moves the first 4 positions of the IBAN to the end of the string, in preparation of the Mod 97 calculation;
Line 8 calculates the Mod 97 of this modified IBAN; if the result = 1 then this is a valid IBAN.

If you prefer a Mod 97 only validation you could remove line 6.
Function GetBBANFormat()
Function GetBBANFormat() takes the Country Code as input and returns a Like formatted string. For example, with country code “GB” (format 4a,14n) GetBBANFormat returns the string: “[A-Z][A-Z][A-Z][A-Z]##############”, to be compared with the BBAN part of the IBAN number. In fact, this function validates:

the Country Code;
the country-specific length of the IBAN;
and the country-specific format of the BBAN as described before.

Function GetBBANFormat makes the IBAN validation more reliable than a Mod 97 only validation.
More functions
Module MIBAN of the workbook contains two more functions.

GetIBAN takes a Country Code and a BBAN and returns the IBAN. The function validates the country code and the BBAN using function GetBBANFormat and then calculates the check digits;
GetMod97 is a general implementation of Mod 97 for large integers, as described in the above mentioned wikipedia page.

How to use
To use the functions IsValidIBAN and GetIBAN() simply drag the module to your own workbook. Now you can use each function as a user defined worksheet function or as vba function. See the workbook for examples.
Final notes
Please keep in mind that the function IsValidIBAN() is not a complete and rigorous test of an individual IBAN. For example, IsValidIBAN() returns True for “GB28 XXXX 1234 5678 9012 34”, which is not a valid IBAN since Great Britain has no bank code “XXXX”. In my opinion a 100% IBAN test can only be done by means of an online service.


I hope this pixcel helps. Excel!
Download
IbanCheck_v1.xlsm