CLABE Checksum. Determining position of values.

Maik

New Member
Joined
Oct 16, 2012
Messages
10
Hi there guys, hope you can help me :). First of all, the CLABE number is just a banking standard like IBAN or Routing numbers. I've been trying to write a code which could lead to retrieving the whole Mexican CLABE number + control digit from just the first 17 digits. This is how the checksum works

The thing is I am not an expert, and I did this mainly by googling so I don't really know where is the mistake here. I think is just about I don't achieve to return the position number in order to obtain the weight factor so that would be the question.
Code:
Public Function okCLABE(CLABE As String) As String

    Dim Idx As Integer
    Dim ChkSum As Long
    Dim ChkVal As Integer
    Dim Wgt As Long
    Dim MyStr As String
    Dim MyChr As String
    
    For Idx = Len(CLABE) To 1 Step -1    
        [COLOR=#ff0000]MyChr = Mid$(CLABE, Idx, 1)[/COLOR]
        If MyChr Mod 3 = 0 Then Wgt = Val(MyChr) * (3)
        
        If MyChr Mod 3 = 1 Then Wgt = Val(MyChr) * (7)
        
        If MyChr Mod 3 = 2 Then Wgt = Val(MyChr) * (1)
        
        ChkSum = ChkSum + Wgt
    Next Idx
    ChkVal = (10 - (ChkSum Mod 10))
 
    okCLABE = CLABE & Trim(Str(ChkVal))
End Function

Here it is a CLABE code to test: 03218000011835971 9=(Control digit)
Thanks in advance :).
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
How about just a formula?


Code:
      --------A-------- B
  1   03218000011835971 9

In B1:

=10 - MOD(SUMPRODUCT(MOD(MID("37137137137137137", ROW(INDIRECT("1:17")), 1) * MID(A1, ROW(INDIRECT("1:17")), 1), 10)), 10)

 
Last edited:
Upvote 0
How about just a formula?


Code:
      --------A-------- B
  1   03218000011835971 9

In B1, confirmed with Ctrl+Shift+Enter:

=10 - MOD(SUM(MOD(MID("37137137137137137", ROW(INDIRECT("1:17")), 1) * MID(A1, ROW(INDIRECT("1:17")), 1), 10)), 10)

You rock. I would like to achieve it by VBA so I can learn how to do it for other bank standards or control digit checksums but that is working great. So if nobody could help I will use that, many thanks :)
 
Last edited:
Upvote 0
Probably still not what you want, but this returns the check digit for a 17-digit string, and returns True or False for an 18-digit string:

Code:
Function CLABE(sInp As String) As Variant
    Dim sFrm As String

    Select Case Len(sInp)
        Case 17
            sFrm = "10 - MOD(SUMPRODUCT(MOD(" & _
                   "MID(""37137137137137137"",  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * " & _
                   "MID(""" & sInp & """,  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), 10)), 10)"
            CLABE = Evaluate(sFrm)
        Case 18
            CLABE = --Right(sInp, 1) = CLABE(Left(sInp, 17))
        Case Else
            CLABE = "Invalid length!"
    End Select
End Function
 
Last edited:
Upvote 0
For a non-volatile formula confirmed in the usual way,

=10 - MOD(SUMPRODUCT(MOD(MID("37137137137137137", {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), 10)), 10)
 
Upvote 0
Probably still not what you want, but this returns the check digit for a 17-digit string, and returns True or False for an 18-digit string:

Code:
Function CLABE(sInp As String) As Variant
    Dim sFrm As String

    Select Case Len(sInp)
        Case 17
            sFrm = "10 - MOD(SUMPRODUCT(MOD(" & _
                   "MID(""37137137137137137"",  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1) * " & _
                   "MID(""" & sInp & """,  " & _
                   "{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}, 1), 10)), 10)"
            CLABE = Evaluate(sFrm)
        Case 18
            CLABE = --Right(sInp, 1) = CLABE(Left(sInp, 17))
        Case Else
            CLABE = "Invalid length!"
    End Select
End Function

As you said is not exactly what I want but it achieves the same objective, which is validating the CLABE.

I hope I will success by myself with other routing numbers such as ABA or BSB, thank you very much, and you are fast as hell :).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top