+ Start a Discussion
Simon KlapwijkSimon Klapwijk 

Validation IBAN

Currently I am working on my DEV401 certification and as an excersise I tried making an IBAN validation rule. Since it actually works I thought I post it here. For some reason the rule editor didn't allow me to concatenate numbers so I had to convert them to text first and convert the result back to a number so I could MOD97 it. 
Suggestions for improvement of course are welcome.

MOD(
VALUE(
TEXT(CASE(MID(IBAN__c,5,1),
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8))
&
TEXT(CASE(MID(IBAN__c,6,1),
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8))
&
TEXT(CASE(MID(IBAN__c,7,1),
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8))
&
TEXT(CASE(MID(IBAN__c,8,1),
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8))
&
RIGHT(IBAN__c,
(CASE(LEFT(IBAN__c,2),
"AL",28,"AD",24,"AT",20,"AZ",28,"BE",16,"BH",22,"BA",20,"BR",29,"BG",22,
"CR",21,"HR",21,"CY",28,"CZ",24,"DK",18,"DO",28,"EE",20,"FO",18,"FI",18,
"FR",27,"GE",22,"DE",22,"GI",23,"GR",27,"GL",18,"GT",28,"HU",28,"IS",26,
"IE",22,"IL",23,"IT",27,"KZ",20,"KW",30,"LV",21,"LB",28,"LI",21,"LT",20,
"LU",20,"MK",19,"MT",31,"MR",27,"MU",30,"MC",27,"MD",24,"ME",22,"NL",18,
"NO",15,"PK",24,"PS",29,"PL",28,"PT",25,"RO",24,"SM",27,"SA",24,"RS",22,
"SK",24,"SI",19,"ES",24,"SE",24,"CH",21,"TN",24,"TR",26,"AE",23,"GB",22,
"VG",24,
8
)-8)
)
&
TEXT(CASE(MID(IBAN__c,1,1),
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8))
&
TEXT(CASE(MID(IBAN__c,2,1),
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8))
&
MID(IBAN__c,3,2)
)
,97
)
<> 1
Simon KlapwijkSimon Klapwijk
Apparently I can't edit my post, but here is some explanation:
-> I assume the IBAN is in the correct format, if not the validation will fail anyway.
-> I start by converting the 4 digits of the bank code to corresponding numbers
-> I add the accountnumber, the length of which will vary depending on the countrycode, which is represented by the first two characters.
-> Next the country code is converted to corresponding numbers as well
-> Finally the two checkdigits are added
-> From the resulting number a MOD97 is calculated
-> If the IBAN is correct the remainder equals 1
Frank van Meegen 5Frank van Meegen 5
Hi Simon,

Thank you for sharing this IBAN validation code. This saves me a lot of work!

I have tested this with my IBAN number from the Netherlands and the validation worked fine.

Regards,

Frank van Meegen
Vodafone NL
Frank van Meegen 5Frank van Meegen 5
Hi Simon,

I took the opportunity to tweak your code so spaces in the IBAN number are allowed. Since a lot of bank use spaces in the IBAN number.

If you use the following code the spaces will be substituted in the validation rule:

MOD( 
VALUE( 
TEXT(CASE(MID( SUBSTITUTE(IBAN_TEST__c , " ", "") ,5,1), 
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8)) 

TEXT(CASE(MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),6,1), 
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8)) 

TEXT(CASE(MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),7,1), 
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8)) 

TEXT(CASE(MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),8,1), 
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8)) 

RIGHT(SUBSTITUTE(IBAN_TEST__c , " ", ""), 
(CASE(LEFT(SUBSTITUTE(IBAN_TEST__c , " ", ""),2), 
"AL",28,"AD",24,"AT",20,"AZ",28,"BE",16,"BH",22,"BA",20,"BR",29,"BG",22, 
"CR",21,"HR",21,"CY",28,"CZ",24,"DK",18,"DO",28,"EE",20,"FO",18,"FI",18, 
"FR",27,"GE",22,"DE",22,"GI",23,"GR",27,"GL",18,"GT",28,"HU",28,"IS",26, 
"IE",22,"IL",23,"IT",27,"KZ",20,"KW",30,"LV",21,"LB",28,"LI",21,"LT",20, 
"LU",20,"MK",19,"MT",31,"MR",27,"MU",30,"MC",27,"MD",24,"ME",22,"NL",18, 
"NO",15,"PK",24,"PS",29,"PL",28,"PT",25,"RO",24,"SM",27,"SA",24,"RS",22, 
"SK",24,"SI",19,"ES",24,"SE",24,"CH",21,"TN",24,"TR",26,"AE",23,"GB",22, 
"VG",24, 

)-8) 


TEXT(CASE(MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),1,1), 
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8)) 

TEXT(CASE(MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),2,1), 
"A",10,"B",11,"C",12,"D",13,"E",14,"F",15,"G",16,"H",17,"I",18,"J",19,"K",20,"L",21,"M",22,"N",23,"O",24,"P",25,"Q",26,"R",27,"S",28,"T",29,"U",30,"V",31,"W",32,"X",33,"Y",34,"Z",35,8)) 

MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),3,2) 

,97 

<> 1

Regards,

Frank van Meegen
Wael RAWAS 7Wael RAWAS 7
Hello everyone, 

I used this piece of verification to validate the IBAN:

MOD(
VALUE(
SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((
SUBSTITUTE(
RPAD( MID( SUBSTITUTE(Name , " ", ""),5,LEN(SUBSTITUTE(Name , " ", "")) ) ,LEN(SUBSTITUTE(Name , " ", "")), MID(SUBSTITUTE(Name , " ", ""),1,4) ),"A","10"))
,"B","11")),"C","12")),"D","13")),"E","14")),"F","15")),"G","16")),"H","17")),"I","18")),"J","19")),"K","20")),"L","21")),"M","22")),"N","23")),"O","24")),"P","25")),"Q","26")),"R","27")),"S","28")),"T","29")),"U","30")),"V","31")),"W","32")),"X","33")),"Y","34")),"Z","35"))
,97)
<>1

I tested it on IBAN codes from all over the world.

 
GuyClairboisGuyClairbois
We also noticed that the original validation is not complete, in the sense that many formats (e.g. Belgium, Germany, Luxemburg, Spain, Denmark) were not correctly supported. Switching to @Wae's solution resolved that for us. Moreover, it's much more elegant and short. Thanks!
Ekaterina GetaEkaterina Geta
I tried the code from @FrankvanMeegen5 and it blocked every valid FR Iban I entered.

And for the @Wae RAWAS7 it was exactly the opposite - any entry was accepted...

Did any of the 2 worked for anyone?
MUSTAPHA ELMADIMUSTAPHA ELMADI

Hello, 

Actualy i have a question, is it possible to use the formula in FlowDesigner validation field ?? 

MUSTAPHA ELMADIMUSTAPHA ELMADI
I Found  an app in APEXchange(type iban in the search barre) hwo does the job, so i used the invocable method in the FLOW to verify the IBAN.