You need to sign in to do that

Don't have an account?

Simon 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

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 KlapwijkApparently 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 5Hi 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 5Hi Simon,

"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))

"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))

"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))

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),

&

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),

&

TEXT(CASE(MID(SUBSTITUTE(IBAN_TEST__c , " ", ""),2,1),

&

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

)

,97

)

<> 1

Regards,

Frank van Meegen

Wael RAWAS 7Hello 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.

GuyClairboisWe 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 GetaI 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 ELMADIHello,

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

MUSTAPHA ELMADII 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.