Découvrez comment vérifier un code IBAN en un clin d'œil avec Excel (sans VBA) !
Dans ce tutoriel, je vais vous montrer comment vérifier un code IBAN à l'aide d'Excel.
Que vous soyez novice ou habitué à travailler avec des chiffres, cette méthode vous permettra de valider rapidement et facilement les données bancaires sans effort.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Qu'est-ce qu'un code IBAN ?
Le code IBAN (International Bank Account Number) est une norme internationale utilisée pour identifier les comptes bancaires. Il a été introduit pour faciliter les transactions bancaires internationales et standardiser le format des numéros de compte.
Le code IBAN est composé d'une série de lettres et de chiffres spécifiques à chaque pays, suivis d'un numéro de compte bancaire propre à chaque utilisateur. La longueur et la structure du code IBAN varient d'un pays à l'autre.
Par exemple, en France, l'IBAN est composé de 27 caractères alphanumériques, tandis qu'en Allemagne, il compte 22 caractères.
La vérification d'un code IBAN est essentielle pour s'assurer que les informations bancaires sont correctes avant d'effectuer des transactions. En vérifiant le code IBAN, on prévient les erreurs de saisie qui pourraient entraîner l'envoi de fonds vers un mauvais destinataire ou un compte inexistant.
La validation du code IBAN permet également de détecter les éventuelles fraudes ou erreurs lors de la saisie du numéro de compte bancaire. Cela contribue à garantir l'exactitude des transactions financières et à maintenir la confiance dans le système bancaire.
Excel, en tant qu'outil de traitement de données puissant, peut nous aider à automatiser la vérification des codes IBAN et à détecter les erreurs éventuelles. En utilisant les formules appropriées, nous pouvons effectuer les calculs nécessaires pour vérifier la validité d'un code IBAN.
En utilisant des formules Excel, nous pouvons appliquer l'algorithme de vérification spécifique à chaque pays pour valider les codes IBAN. Les fonctions intégrées telles que MOD (pour le calcul du reste de la division) et les fonctions d’extraction des parties spécifiques d'une chaîne de caractères sont particulièrement utiles dans ce contexte.
2. Préparation des données dans Excel
Pour illustrer ce tutoriel, nous allons utiliser la feuille de calcul suivante, dans laquelle nous retrouvons un mini-formulaire de validation du code IBAN :
L’objectif sera donc de saisir un code IBAN dans la cellule C8 afin d’obtenir la confirmation ou non de la validité de celui-ci en retour dans la cellule C14.
Pour faciliter la mise en place de ces traitements, il est possible de sélectionner directement l’un des trois codes IBAN préenregistrés.
Il s’agit de codes fictifs qui n’existent pas, car les codes banque correspondant (43 et 86) ne correspondent à aucune banque référencée en France, mais qui seront malgré tout valide.
Si vous disposez déjà des codes IBAN, vous pouvez les saisir directement dans la cellule dédiée.
3. Création de la formule de vérification IBAN
La vérification d'un code IBAN repose sur un algorithme spécifique. Il consiste à calculer le reste de la division du code IBAN par 97.
Si le reste est égal à 1, le code IBAN est valide, dans le cas contraire, celui-ci est erroné.
Avant de pouvoir effectuer cette division, nous allons devoir effectuer deux retraitements sur le code IBAN
- En premier lieu, nous allons devoir remplacer les lettres présentes au tout début par des nombres,
- Ensuite, nous allons placer la correspondance en fin de chaîne (les quatre premiers caractères seront ainsi placés à la fin
Ensuite, en concaténant chacun de ces nombres les uns à la suite des autres, nous récupérerons un très grand nombre que nous pourrons insérer à l’intérieur de la fonction MOD.
Mais avant tout, nous allons commencer par nettoyer le code IBAN saisie dans la cellule C8, en supprimant les espaces superflues qui pourraient nuire à la bonne exécution de la formule, à la fonction SUBSTITUE qui permet, comme nous l’avons déjà vu dans un tutoriel précédent de remplacer une portion de texte par une autre.
Nous saisissons donc la formule suivante dans la cellule C9 :
SUBSTITUE(C8;" ";"")
Nous en arrivons maintenant à l’étape du retraitement du code IBAN.
Ici, nous allons décomposer la chaîne, afin de transférer les quatre premiers caractères à la fin de celle-ci, tout en remplaçant l’indicatif du pays par un code numérique correspondant.
Ce code est déterminé en remplaçant les lettres qui le composent par des chiffres.
Ainsi, le code en lettre « FR » a pour correspondance numérique « 1527 » :
- La lettre « F » correspond au nombre 15
- Et la lettre « R » au nombre 27
Pour déterminer le nombre correspondant, vous trouverez dans l’onglet « Table conversion pays » du fichier téléchargeable le tableau de conversion :
Il suffit d’ajouter un nouveau pays à la suite dans la première colonne pour obtenir le code correspondant.
Ensuite, nous allons effectuer le retraitement du code IBAN en réorganisant les caractères pour obtenir le format correct.
La formule utilisée est la suivante :
DROITE(C9;27-4)&"1527"&STXT(C9;3;2)
Cette formule extrait les parties du code IBAN et les concatène dans l'ordre approprié.
Plus précisément, elle prend les 23 derniers caractères à partir de la droite (27 - 4), ajoute la valeur "1527", puis ajoute les 2 caractères situés à partir de la position 3 du code IBAN.
En théorie, il nous suffirait maintenant de calculer le reste de la division euclidienne de ce très grand nombre pour pouvoir valider le code.
Sauf, qu’Excel n’est pas capable de gérer des calculs sur d’aussi grands nombres (pas plus de 14 chiffres…)
Nous allons donc séparer ce code IBAN en trois parties distinctes pour faciliter la vérification. Voici les formules utilisées reposant sur les fonctions d’extraction de texte (GAUCHE, DROITE, STXT) :
Partie 1 (12 premiers caractères) :
GAUCHE(C10;12)
Partie 2 (12 caractères suivants) :
STXT(C10;13;12)
Et enfin, partie 3 (5 derniers caractères) :
DROITE(C10;5)
Puis, nous allons calculer les restes de la division par 97 de ces nombres :
Pour cela, nous allons pouvoir utiliser la fonction MOD que nous avions découverte dans un tutoriel précédent.
Nous commençons par calculer le reste du résultat de la cellule C11 correspondant à la division pour 97 :
=MOD(C11;97)
Ensuite, nous nous plaçons sur la cellule C12 située juste en dessous, et nous appuyons sur les touches [Ctrl]+[B] pour dupliquer la formule.
Nous obtenons alors la formule suivante :
=MOD(C12;97)
Ici, nous allons souhaiter ajouter le résultat calculer juste avant au début de ce nombre, nous allons donc le concaténer :
=MOD(D11&C12;97)
Pour finir, nous plaçons sur la cellule D13 et nous appuyons à nouveau sur les touches [Ctrl]+[B] :
=MOD(D12&C13;97)
Maintenant, si ce résultat est égal à 1, alors le code IBAN est valide ! Dans le cas contraire, celui-ci est erroné.
Nous pouvons donc ajouter le résultat correspondant dans la cellule C14 en y saisissant la formule suivante :
SI(D13=1;"L'IBAN est correct !";"L'IBAN n'est pas valide")
Cette formule compare le résultat du calcul du reste de division (étapes précédentes) avec la valeur 1.
Si le résultat est égal à 1, cela signifie que le code IBAN est valide. Dans ce cas, le texte "L'IBAN est correct !" est affiché dans la cellule C14. Sinon, le texte "L'IBAN n'est pas valide" est affiché.
Il suffit maintenant, de modifier l’un des chiffres de ce code pour tester si l’algorithme est valide ou non :
