This Excel tutorial explains the algorithm of HKID Check Digit (last digit) and provide a custom Access Excel VBA Function to verify the Check Digit. (香港身份証最後號碼)
You may also want to read:
Access Excel Generate random HKID Hong Kong ID card number
HKID Check Digit
Hong Kong ID card number has a last digit with bracket called “Check Digit“. Check Digit is a number to verify if the previous number and alphabet are correct. By understanding the algorithm of calculating Check Digit, you can generate a random HKID.
There are two main purposes for understand HKID Check Digit.
First, some online services only offer to local residents, they do not want overseas residents to register their service, therefore in the membership registration page, they ask you to enter a valid HKID number. The verification system cannot check whether the HKID is real, they only check if your Check Digit is valid.
Second, this checking can be used to verify if the employee database maintains correct records, or check before inputting the data in the system.
Algorithm of HKID Check Digit
There are two types of HKID number:
1) Prefix with one alphabet, such as A123456(7)
2) Prefix with two alphabets, such as AB123456(7)
I will demonstrate the algorithm below using HKID A123456(x)
Step 1
Convert the prefix alphabet to a numeric value. If Prefix contains two alphabets, add 8 to the original converted value of single alphabet, regardless of the first alphabet.
For example, A should convert to 8, then AA (or BA,CA,DA, etc) should be converted to 8+8 = 16
In our example HKID A123456(x), the result is 8
Prefix Alphabet | Converted value |
A | 8 |
B | 16 |
C | 24 |
D | 32 |
E | 40 |
F | 48 |
G | 56 |
H | 64 |
I | 72 |
J | 80 |
K | 88 |
L | 96 |
M | 104 |
N | 112 |
O | 120 |
P | 128 |
Q | 136 |
R | 144 |
S | 152 |
T | 160 |
U | 168 |
V | 176 |
W | 184 |
X | 192 |
Y | 200 |
Z | 208 |
xA | 16 |
xB | 24 |
xC | 32 |
xD | 40 |
xE | 48 |
xF | 56 |
xG | 64 |
xH | 72 |
xI | 80 |
xJ | 88 |
xK | 96 |
xL | 104 |
xM | 112 |
xN | 120 |
xO | 128 |
xP | 136 |
xQ | 144 |
xR | 152 |
xS | 160 |
xT | 168 |
xU | 176 |
xV | 184 |
xW | 192 |
xX | 200 |
xY | 208 |
xZ | 216 |
Step 2
Convert each HKID digit to a number.
Nth HKID digit | multiplier |
1st | 7 |
2nd | 6 |
3rd | 5 |
4th | 4 |
5th | 3 |
6th | 2 |
In our example HKID A123456(x)
1 is the 1st digit, multiply 1 by 7 = 7
2 is the 2nd digit, multiply 2 by 6 = 12
3 x 5 = 15
4 x 4 = 16
5 x 3 = 15
6 x 2 = 12
Total = 77
Step 3
Calculate remainder of (Step 1 result + Step 2 result)/11
= (8+77)/11
= 8
Step 4
Check Digit = 11 – result of Step 3
= 11 – 8
= 3
Step 5
The Check Digit should contain only a single digit, otherwise further convert using the below rules.
If Check Digit = 10, convert to A
If Check Digit = 11, convert to 0
Result
Since the calculated Check Digit is a single digit, x=3 for HKID A123456(x)
Custom VBA Function – HKID Check Digit
The manual calculation of Check Digit is very complicated, therefore I created a custom Function for Excel and Access for checking if the last digit is correct.
This Function removes all brackets and capitalize all alphabets during the checking (the original value will not be modified).
After the Function calculates a Check Digit, it will compare with the Check Digit of user input.
This Function returns TRUE if last digit is correct, FALSE if incorrect.
I have verified the accuracy of this custom Function with 40,000+ real HKID.
VBA Code – HKID Check Digit
Public Function wCheckHKID(ID As String) As String Dim newIDArr() As Variant newID = UCase(Replace(Replace(ID, "(", ""), ")", "")) lenNewID = Len(newID) IDnoDigit = Left(newID, lenNewID - 1) lenIDnoDigit = Len(IDnoDigit) For i = 1 To lenIDnoDigit ReDim Preserve newIDArr(i) newIDArr(i) = Mid(IDnoDigit, i, 1) Next i If lenIDnoDigit = 7 Then checkSum = (Asc(newIDArr(1)) - 64) * 8 + newIDArr(2) * 7 + newIDArr(3) * 6 + newIDArr(4) * 5 + newIDArr(5) * 4 + newIDArr(6) * 3 + newIDArr(7) * 2 checkDigit = 11 - checkSum Mod 11 ElseIf lenIDnoDigit = 8 Then checkSum = 6 + (Asc(newIDArr(2)) - 64) * 8 + newIDArr(3) * 7 + newIDArr(4) * 6 + newIDArr(5) * 5 + newIDArr(6) * 4 + newIDArr(7) * 3 + newIDArr(8) * 2 checkDigit = 11 - checkSum Mod 11 End If If checkDigit = 10 Then newCheckdigit = "A" ElseIf checkDigit = 11 Then newCheckdigit = "0" Else: newCheckdigit = checkDigit End If If Asc(newCheckdigit) = Asc(Right(newID, 1)) Then wCheckHKID = True Else wCheckHKID = False End If End Function
Syntax of custom Access Excel Function – HKID Check Digit
wCheckHKID (ID)
ID | Full HKID such as Z123456(2), accept values with or without brackets, lower case or upper case |
How did it automaticaaly write true and false in the result column
If Asc(newCheckdigit) = Asc(Right(newID, 1)) Then
wCheckHKID = True
Else wCheckHKID = False
rio truly an impressive message thanks so much for communion i bequeath shoot the breeze this enthusiastic web site every unwed twenty-four hour period