How to check the plagiarism of ID number in Excel

How to check the identity card number in Excel: first enter the ID number, and all numbers after 15 digits will be changed to 0 and displayed in scientific notation; then use the counting function, the calculation formula is "COUNTIFS( B:B,B3)", the displayed result is 2; the final count equals 2 is a repeated result.

#1. Correctly enter the ID number

When manually inputting pure numbers greater than 15 digits in Excel, the numbers after 15 digits All become 0 and displayed in scientific notation. There are two ways to enter correctly

. The first method is: before entering the number, enter a single quotation mark in English

The second method is: adjust the format of the entire column to text format in advance, and then enter the numbers normally

2. Duplicate check of ID card data

There is an employee form. At first, we wanted to check whether there were duplicate entries through the ID number. Then we used the conditional format to highlight duplicate values. As a result, there was a pair of twins. The ID numbers were obviously different, but they were Marked with the same color, as shown below:

Then when we use the counting COUNTIF function to count:

=COUNTIFS(B:B,B3 ), there is only 1, and the displayed result is 2

##. This is because Excel only looks at the first 15 digits when counting lengths of more than 15 digits. If the first 15 If the digits are the same, just treat the two numbers as the same. This is obviously not the result we want

If we need to count correctly for numbers with more than 15 digits, we must add a wildcard symbol , use the formula:

=COUNTIFS(B:B,B2&"*")

In order to count the wildcards, the result equal to 2 is the real duplicate

3. Prohibit entry of duplicate ID numbers

In order to prohibit entry of duplicate ID numbers, we need to select Custom in the data verification, and then enter the formula: =countif (b:b,b1&"*")=1

After setting this, if you enter the same number again, an error message will pop up, prohibiting input:

Have you learned this skill? Try it yourself~

The above is the detailed content of How to check the plagiarism of ID number in Excel. For more information, please follow other related articles on the PHP Chinese website!