Excel Sheet for IBAN Checker

[maas]

Hello All,

I am looking for a ready excel sheet or a useful way to include an IBAN checker in an excel sheet.
I searched for a number of examples but I could not find a useful one.

Can you help in finding this sheet or at least help in doing the excel.

Thanks

 

[maas]

I have found a ready Microsoft Excel Add In which is http://www.codeproject.com/KB/string/CSharp_IBAN_Verification.aspx
But, I don't know how to open it in excel.

Can anyone help me?

 

[VoG]

But, I don't know how to open it in excel.

That is explained (for Excel 2003 and earlier) in the link that you posted. Which version of Excel are you using?

 

[Rick Rothstein]

I have no experience with IBAN, but following the algorithm laid out here...

http://en.wikipedia.org/wiki/International_Bank_Account_Number

I came up with this function (can be used as a UDF if need be)...

Function IsFormatIBAN(ByVal S As String) As Boolean
  Dim X As Long, DigitValue As Long, Total As Long
  If Not S Like "*[0-9A-Z ]*" Then Exit Function
  S = Replace(S, " ", "")
  S = Mid(S, 5) & Left(S, 4)
  For X = 65 To 90 'A to Z
    S = Replace(S, Chr(X), X - 55)
  Next
  S = StrReverse(S)
  DigitValue = 1
  Total = Left(S, 1)
  For X = 2 To Len(S)
    DigitValue = 10 * DigitValue Mod 97
    Total = Total + Mid(S, X, 1) * DigitValue
  Next
  IsFormatIBAN = (Total Mod 97) = 1
End Function

This should tell you if the IBAN number is correctly formatted but, of course, not if there is an actual account matched to it. I believe the code is correct, but without any knowledge of IBAN (until your post), my suggestion is for you to test it out against several known good and bad IBAN numbers to make sure it really returns the correct values for you.

 

[maas]

Thank You my friend and appreciate your help