Formulas to verify IBAN

[Albert_belingar]

Hi,

i’m using Airtable to help my customers for refund.
so the form on my website is working fine but i need to improve it.

Customers enter their bank details. I would like to make sure that the key is correct.
i have a huge formula in Excel and i want to know if it’s possible in airtable.

thanks

[Ambroise_Dhenai]

Here is a solution that covers SEPA-only countries: Formula to check if IBAN is a valid IBAN within the SEPA area

Here is another solution for all countries that support IBAN:

github.com

UnlyEd/airtable-utils/blob/master/formulas/is-bank-IBAN-structure-valid.txt

IF(
  LEN(bankIBAN) > 0,
  IF(
    REGEX_MATCH(
      SUBSTITUTE(bankIBAN, ' ', ''),
      '^(?:(?:CR|DE|ME|RS|VA)\d{20}|(?:CZ|ES|SE|SK|TN)\d{22}|(?:DK|FI|FO|GL|SD)\d{16}|(?:AT|BA|EE|LT|XK)\d{18}|(?:AE|IL|TL)\d{21}|(?:LY|PT|ST)\d{23}|(?:IT|SM)\d{2}[A-Z]\d{10}[A-Za-z0-9]{12}|(?:HU|PL)\d{26}|(?:AL|CY)\d{10}[A-Za-z0-9]{16}|(?:CH|LI)\d{7}[A-Za-z0-9]{12}|(?:FR|MC)\d{12}[A-Za-z0-9]{11}\d{2}|(?:GB|IE)\d{2}[A-Z]{4}\d{14}|(?:KZ|LU)\d{5}[A-Za-z0-9]{13}|(?:GI|IQ)\d{2}[A-Z]{4}[A-Za-z0-9]{15}|(?:PK|RO)\d{2}[A-Z]{4}[A-Za-z0-9]{16}|(?:PS|QA)\d{2}[A-Z]{4}[A-Za-z0-9]{21}|AD\d{10}[A-Za-z0-9]{12}|AZ\d{2}[A-Z]{4}[A-Za-z0-9]{20}|BE\d{14}|BG\d{2}[A-Z]{4}\d{6}[A-Za-z0-9]{8}|BH\d{2}[A-Z]{4}[A-Za-z0-9]{14}|BR\d{25}[A-Z][A-Za-z0-9]|BY\d{2}[A-Za-z0-9]{4}\d{4}[A-Za-z0-9]{16}|DO\d{2}[A-Za-z0-9]{4}\d{20}|EG\d{27}|GE\d{2}[A-Z]\d{16}|GT\d{2}[A-Za-z0-9]{24}|GR\d{9}[A-Za-z0-9]{16}|HR\d{19}|IS\d{24}|JO\d{2}[A-Z]{4}\d{4}[A-Za-z0-9]{18}|KW\d{2}[A-Z]{4}[A-Za-z0-9]{22}|LC\d{2}[A-Z]{4}[A-Za-z0-9]{24}|LB\d{6}[A-Za-z0-9]{20}|LV\d{2}[A-Z]{4}\d{13}|MD\d{2}[A-Za-z0-9]{20}|MK\d{5}[A-Za-z0-9]{10}\d{2}|MR\d{25}|MT\d{2}[A-Z]{4}\d{5}[A-Za-z0-9]{18}|MU\d{2}[A-Z]{4}\d{19}[A-Z]{3}|NL\d{2}[A-Z]{4}\d{10}|NO\d{13}|SA\d{4}[A-Za-z0-9]{18}|SC\d{2}[A-Z]{4}\d{20}[A-Z]{3}|SI\d{17}|SV\d{2}[A-Z]{4}\d{20}|TR\d{8}[A-Za-z0-9]{16}|UA\d{8}[A-Za-z0-9]{19}|VG\d{2}[A-Z]{4}\d{16}|GE\d{2}[A-Z]{2}\d{16})$'
    ),
    1,
    -1
  ),
  0
)

Documentation:
This formula checks if the structure of TODO_REPLACE_WITH_IBAN is a valid IBAN structure (each country has its own specification).

Specifications:
- https://www.iban.com/structure
- https://docs.google.com/spreadsheets/d/1376mu58jQ2P5jGX7tIXGrFfwgfo0RHO7RBRSpKcViIs/edit?usp=sharing Google Sheet based on the above data structure, easier to work with
- https://stackoverflow.com/a/69068004/2391795

This file has been truncated. show original