Validate IBAN in Excel

Question

Is there any ready excel sheet to validate the IBAN? I will enter the IBAN and it will show valid or invalid.

I have searched for a number of Adds In and found this one

But I don't know how to open it. Can anyone help?

Answer 1

What about this, has the formatting issue solved and carries out the "97 check":

Public Function VALIDATEIBAN(ByVal IBAN As String) As String

' Created by : Koen Rijnsent (www.castoro.nl)
' Inspired by : Chris Fannin (AbbydonKrafts)
' Inspired by : bonsvr (http://stackoverflow.com/users/872583/bonsvr)

On Error GoTo CatchError

Dim objRegExp As Object
Dim IBANformat As Boolean
Dim IBANNR As String
Dim ReplaceChr As String
Dim ReplaceBy As String

'Check format
IBAN = UCase(IBAN)
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[a-zA-Z]{2}[0-9]{2}[a-zA-Z0-9]{4}[0-9]{7}([a-zA-Z0-9]?){0,16}"
IBANformat = objRegExp.Test(IBAN)

'Validity of country code will not be checked!
If IBANformat = False Then
    VALIDATEIBAN = "FORMAT NOT RECOGNIZED"
Else
    'Flip first 4 characters to the back
    IBANNR = Right(IBAN, Len(IBAN) - 4) & Left(IBAN, 4)

    'Replace letters by the right numbers
    For Nr = 10 To 35
        ReplaceChr = Chr(Nr + 55)
        ReplaceBy = Trim(Str(Nr))
        IBANNR = Replace(IBANNR, ReplaceChr, ReplaceBy)
    Next Nr

    'Loop through the IBAN, as it is too long to calculate at one go
    CurrPart = ""
    Answer = ""
    For CurrDigit = 1 To Len(IBANNR)
        CurrPart = CurrPart & Mid$(IBANNR, CurrDigit, 1)
        CurrNumber = CLng(CurrPart)
        'If the number can be divided
        If 97 <= CurrNumber Then
            LeftOver = CurrNumber Mod 97
            WorkValue = (CurrNumber - LeftOver) / 97
            Answer = Answer & CStr(WorkValue)
            CurrPart = CStr(LeftOver)
        Else
            'If no division occurred, add a trailing zero
            If Len(Answer) > 0 Then
                Answer = Answer & "0"
                'Exception for the last number
                If CurrDigit = Len(IBANNR) Then
                    LeftOver = CurrNumber Mod 97
                Else
                End If
            Else
            End If
        End If
    Next
    If LeftOver = 1 Then
        VALIDATEIBAN = "IBAN OK"
    Else
        VALIDATEIBAN = "97 CHECK FAILED"
    End If
End If

Exit Function

CatchError:
    VALIDATEIBAN = "ERROR: " & Err.Description
    MsgBox "Module: " & MODULE_NAME & " - VALIDATEIBAN function" & vbCrLf & vbCrLf _
    & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Function