• Simon Klapwijk
  • NEWBIE
  • 5 Points
  • Member since 2014

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 1
    Replies
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
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