View original (French)

IBAN Key Calculation modulo 97

Solved
sandjee Posted messages 27 Status Member -  
 banco -
Hello,

I am looking for an Excel formula that would allow me to derive the corresponding IBAN from a French bank account number (bank code - branch code - account number and key).
Can anyone help me?
Thank you very much

Configuration: Windows XP / Internet Explorer 8.0

6 answers

  1.
    pilas31 Posted messages 1878 Status Contributor 648
     
    Hello,

    Out of curiosity, I looked up the method and made a small Excel file:

    https://www.cjoint.com/c/CBfs3TdrwsU

    You need to enter the two-letter country code in A2 and the complete account number of the country in B2

    The IBAN is calculated in B4

    Best regards,
    4
    1. sandjee Posted messages 27 Status Member
       
      Thank you very much for your valuable help. Super cool!
      Have a nice day
      0
    2. sandjee Posted messages 27 Status Member
       
      euh .. well, when trying to use it, I'm having issues .. when I want to apply it with a French RIB (whether it's lettered or not in the account number), it seems unable to calculate the key => so I get the message #NUMBER!
      Do you think you can do something ...
      Thank you very much for your help.

      The formula I asked the developers to apply in its literal form
      is :- If the payment country is France, the IBAN and BIC fields must be consistent.
      * The RIB is structured as follows:
      bank code = 5 BBBBB
      branch code = 5 AAAAA
      account number = 11 CCCCCCCCCCC
      rib key = 2 KK

      * The IBAN consists of the RIB data (27 characters)
      FRXXBBBBBAAAAACCCCCCCCCCCKK

      The first two characters are: FR (ISO2 code for France)
      The 3rd and 4th characters are: XX (IBAN key)
      From 5 to 9: BBBBB (bank code)
      From 10 to 14: AAAAA (branch code)
      From 15 to 25: CCCCCCCCCCC (account number)
      The 26th and 27th are KK (rib key)

      If both RIB and IBAN are provided, they must match => the payment type is transfer

      - If the payment country is France, the IBAN key must be calculated according to the algorithm:
      1- IBAN code place 00 in the IBAN key field FR00BBBBBAAAAACCCCCCCCCCCKK
      2- place the first 4 characters at the end of the code BBBBBAAAAACCCCCCCCCCCKKFR00
      3- replace letters with their corresponding numbers (A=10, B=11, C=12... Z=35) and thus BBBBBAAAAACCCCCCCCCCCKK152700 (F=15 and R=27)
      All letters must be replaced
      4- Perform the modulo 97 command on the obtained result
      Mod97 = BBBBBAAAAACCCCCCCCCCCKK152700 MODULO 97
      5- perform the subtraction 98- Mod97 = IBAN key (XX)
      6- the resulting IBAN code is therefore: FRXXBBBBBAAAAACCCCCCCCCCCKK
      0
    3. momo26
       
      I am looking for an Excel formula that would allow me to derive the corresponding IBAN from a French RIB (bank code - branch code - account number and key).
      Can someone help me?
      Thanks a lot.
      0
    4. pilas31 Posted messages 1878 Status Contributor 648
       
      Hello momo26,

      Have you downloaded the file offered above?
      I just tested it, it is still accessible and it works.
      If there are any issues, you should consider replacing the formula in C29 with the more reliable formula suggested below.

      Best regards,
      0