Apex Code Development You need to sign in to do that
Don't have an account?
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
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
-> 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
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
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
)-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
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.
And for the @Wae RAWAS7 it was exactly the opposite - any entry was accepted...
Did any of the 2 worked for anyone?
Hello,
Actualy i have a question, is it possible to use the formula in FlowDesigner validation field ??
Is there a way to make sure that this IBAN validation also allows lowercase letters?
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