*醉折花枝作酒筹**Original**2021-05-10 16:53:24**25234browse*

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!

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：How to sort wps according to scoreNext article：How to sort wps according to score

- What should I do if the excel date becomes a 5-digit number and cannot be changed?
- What should I do if the excel print preview cannot be found?
- What function does excel use to find the difference?
- What should I do if it prompts for repair every time I open excel?
- How to customize complex formula calculations in Excel