How to automatically correct ID card errors in Excel

Method: First enter "=IF(LEN(data)=18,MID('10X98765432',MOD(SUMPRODUCT(VALUE(MID(data,ROW(data area),1))), Data area),11) 1,1),'Length error')"; and then just compare.

The operating environment of this tutorial: Windows 7 system, Microsoft Office Excel 2013 version, Dell G3 computer.

Let’s take a look at the principle first. The second-generation ID card has 18 digits in total, so the last digit is calculated by arithmetic of the previous 18 digits. If one digit is wrong, the verification results will be inconsistent. Use this to determine whether the ID number is correct and legal

Let’s look at the algorithm again. First, multiply the first 17 digits of the ID card by different coefficients, as shown in the figure

Add the result obtained after multiplying this 17-digit number and the coefficient to get a total number, and then divide it by 11 to get the remainder. Then the remainder and the check code (the last digit of the ID card is ) The corresponding relationship is as shown in the figure, that is, if the remainder is 3, the check code is 9.

After clarifying the verification principle, let’s take a look at how to operate it on excel, create a new table, and simply make a table for easy viewing

For better understanding, first calculate 18 digits, =IF(LEN($C2)=18,MID('10X98765432',MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17)), 1)),$B$2:$B$18),11) 1,1),'Length error')

Verify again that the 18th digit matches the ID card The 18th bit is inconsistent, legal and valid =IF(LEN($C2)=18,IF(MID('10X98765432',MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B $2:$B$18),11) 1,1)=RIGHT($C2,1),'Legal','Illegal'),'Wrong length')

Related learning recommendations: excel tutorial

The above is the detailed content of How to automatically correct ID card errors in Excel. For more information, please follow other related articles on the PHP Chinese website!