Formula for MOD 10 Weight 2 Algorithm

inosent

Board Regular
Joined
Mar 19, 2007
Messages
128
I am looking for an xl formula to calculate the check digit for a MERS MIN. They tell me it is a MOD 10 Weight 2 Algorithm.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
836
Office Version
2007
Platform
Windows
I searched for check digit algorithm on google, and got this routine. It should do what you are asking for with a bit of thought. A formula would be more difficult.... but the basic principle of a mod 10 check is to multiply alternate digits by 2 or 1 and add all the results. Then use divide by 10 and use the remainder as your check digit.
Code:
<code class="vb keyword">Function</code> <code class="vb plain">checkdigit(idWithoutCheckDigit)</code>
 
<code class="vb plain">ucIdWithoutCheckdigit = UCase(idWithoutCheckDigit)</code>
<code class="vb plain">total = 0</code>
<code class="vb keyword">For</code> <code class="vb plain">i = Len(ucIdWithoutCheckdigit) </code><code class="vb keyword">To</code> <code class="vb plain">1 </code><code class="vb keyword">Step</code> <code class="vb plain">\-2</code>
<code class="vb plain">digit = Asc(Mid(ucIdWithoutCheckdigit, i, 1)) - 48</code>
<code class="vb plain">total = total + (2 * digit) - Int(digit / 5) * 9</code>
<code class="vb keyword">If</code> <code class="vb plain">(i > 1) </code><code class="vb keyword">Then</code>
<code class="vb plain">digit = Asc(Mid(ucIdWithoutCheckdigit, i - 1, 1)) - 48</code>
<code class="vb plain">total = total + digit</code>
<code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb keyword">Next</code> <code class="vb plain">i</code>
<code class="vb plain">total = Abs(total) + 10</code>
<code class="vb plain">checkdigit = (10 - (total </code><code class="vb keyword">Mod</code> <code class="vb plain">10)) </code><code class="vb keyword">Mod</code> <code class="vb plain">10</code>
 
<code class="vb keyword">End</code> <code class="vb keyword">Function</code>
 
Last edited:

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi,

If you confirm the algorithm then I'm sure this will be quite doable using worksheet formulas alone as well.

Regards
 

Forum statistics

Threads
1,085,651
Messages
5,384,936
Members
401,927
Latest member
commae

Some videos you may like

This Week's Hot Topics

Top