NRIC Checksum

[excelvbanew]

Hello i am writing an formula to check my checkum of NRIC

However i encounter issue where i keep returning false

Please Login or Register  to view this content.

I have try use https://nric.biz/ to give me an nric and i try to put in my excel sheet however it keep returning false for M
can someone help me figure why ?

[XLent]

you would need to provide some examples (B3 values) which generate FALSE but which you feel should be TRUE, without that there's little any can add, I suspect.

[excelvbanew]

Hi I have attached my excel file that I would use

i will first go to https://nric.biz/

Then change to checksum

Then key in any number then let it generate the NRIC then copy the NRIC which the 9 letter to my excel to check the formula is true or false

[AliGW]

You were asked for some examples, not just one!

Why should the example you have given not return FALSE? Explain the criteria for a text string to meet the check.

On the site to which you linked, that text string returns an error message:

D is incorrect. (Hint: it is K!)

[excelvbanew]

Hi as i am using the website to check my formula whether is true or false

Cos the website will generate the correct checksum if u paste it in it will display true

as this is an algorithm to be use with the code

[AliGW]

How are we supposed to know the algorithm that the website is using? Are you saying that you don't know what it is?

[AliGW]

Your formula is not returning FALSE for M - M passes the criteria set. It's the final D that is being rejected.

[excelvbanew]

I see it mean is the M is wrong ?

[AliGW]

No. Look here:

(EDIT: for 9 numbers read 9 characters in the string).

[excelvbanew]

Yes correct start with M and 9 number however only when I added M it return me with false which I not sure why from S, T, G, F it return true
The code orginally come with working condition for S, T, G, Fhowever after google On M algorithm I added Iin the vlookup value I couldn't get no matter what

[Bo_Ry]

Please try

=LET(id,B3,b,LEFT(id),s,SEQUENCE(7),m,MOD(SUM(MID(id,s+1,1)*(MOD(1-s,6)+2))+SWITCH(b,"T",4,"G",4,),11)+1,
MID(CHOOSE(FIND(b,"0STFGM")/2,"JZIHGFEDCBA","XWUTRQPNMLK","TRQPNJLKXWU"),m,1)=RIGHT(B3))

[AliGW]

It's because, based on your formula, the M needs to be matched with R at the end, not D, so it isn't the M that it's telling you is wrong.

Explain in WORDS, step-by-step, what you need this part of the formula to do:

Please Login or Register  to view this content.

[excelvbanew]

This formula checks whether a Singaporean National Registration Identity Card (NRIC) number is valid by verifying its checksum digit.

The formula takes the NRIC number as input in cell B3. It first checks that the NRIC number has a length of 9 using the LEN() function.

If the NRIC number is 9 characters long, the formula then calculates the checksum using the following steps:

Extracts the first letter of the NRIC number using the LEFT() function, and converts it to uppercase using the UPPER() function.
Looks up the corresponding conversion table for the first letter of the NRIC number in the array

{"S","J","Z","I","H","G","F","E","D","C","B","A";"T","J","Z","I","H","G","F","E","D","C","B","A";"F","X","W","U","T","R","Q","P","N","M","L","K";"G","X","W","U","T","R","Q","P","N","M","L","K";"M","Y","X","W","U","T","R","Q","P","N","M","L"} using the VLOOKUP() function.
Looks up the corresponding conversion value for the first letter of the NRIC number in the array {"S",0;"T",4;"F",0;"G",4;"M",9;"A",1;"B",2;"C",3;"D",4;"E",5;"H",6;"I",7;"J",8;"K",9} using the VLOOKUP() function.

Multiplies each digit of the NRIC number except the first and last digits with a corresponding weight from the array {2,7,6,5,4,3,2}.
Sums up the products obtained in step 4.

Adds the result obtained in step 5 to the conversion value obtained in step 3, and takes the modulo 11.
Adds 2 to the result obtained in step 6, and takes the modulo 10.

Looks up the final conversion value in the array {"J","Z","I","H","G","F","E","D","C","B","A"} using the VLOOKUP() function.

The formula then checks whether the final conversion value obtained in step 8 is equal to the last digit of the NRIC number using the RIGHT() function. If they are equal, the formula returns TRUE, indicating that the NRIC number is valid. Otherwise, the formula returns FALSE, indicating that the NRIC number is invalid.

This is the original code that work from for the 4 first Starting NRIC / FIN : S, F, T, G

Please Login or Register  to view this content.

[excelvbanew]

Nice so far this work the best it work in every scenario where it return true or false from the website i use and paste inside to the b3 Thanks hope one day i can find a better version for it, i have marked this as solved as it work