Like and see, form a habit; A door-hinge is never worm-eaten.

Wechat search [Yixin Excel] to pay attention to this different we-media person.

In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.

When we are dealing with employee information, we should have dealt with the ID number. The ID card number contains a lot of information, including place of birth, age, gender, birthday, Chinese zodiac, zodiac sign and so on. Today we don’t say how to extract these information, say a more important ID card number input, after all, there are data to extract, and the input process also has a lot of problems, let’s look at how to solve these problems.

1. Display problems

Take the current mainstream 18-digit ID number as an example, when we enter a random 18-digit number in a cell such as 123456789012345678.

We found that the ID number display became a scientific counting display, and when we selected the ID number again, we found that the last three digits changed to 0. This is because numbers in Excel are 15 digits accurate, and when numbers exceed 15 digits they are replaced by 0.

The processing method is also very simple, here to two small methods: ① Input method switch to the English state, first enter single quotation marks “” and then enter id number can; ② Right click the cell and set the cell format to text format.

2. Length verification

Id card number 18 digits, input much hard to avoid meeting dazzling, carelessly can much or less.

“Data validation” can be very good solve this kind of situation, select the cell area, click the “data” TAB under the “data tools group” in the “data validation, data verification dialog, then click” Settings “TAB, and allows you to select the” text “, data selection “equals”, choose “18” length, finally click ok.

The demo is as follows:

3. Uniqueness verification

Once the digit validation is complete, there is another problem: duplicate input.

Select an area of cells that cannot be re-entered; Select “Data” → “Data Tools” → “Data Verification” to bring up the verification dialog box; =COUNTIF(B 5:B5:B5:B7,B5&”*”)=1 “;

4. Validity verification

We know that the ID card number is a set of calculation rules, let’s explain the calculation rules step by step.

① First of all, the first 17 digits of the ID card number each have their corresponding coefficients, and then multiply the 17 digits with their coefficients and add the results;

② Divide the result of the first step by 11 to get the remainder. And each remainder has its corresponding ID card number should be the 18th digit;

③ We calculated the mantissa of the ID number above and the actual comparison can be concluded that the ID number is legal.

For example, the ID number is 321324199005161058.

(1) the first calculate first 17 digits and its coefficient is the sum of the product: 3 * 7 + 9 + 2 * 1 * 10 + 3 * 5 + 2 * 8 + 4 * 4 + 1 * 2 + 9 * 1 + 9 * 6 + 3 + 0 0 * * 7 + 5 * 9 + 1 * 10 + 5 + 6 * 1 * 8 + 0 * 4 + 5 * 2 = 264;

② Then calculate the remainder of the result of the first step divided by 11: the remainder of 264/11 is 0, and the corresponding 18th digit should be 1.

③ The calculated number does not match our actual number, so the ID card number is illegal.

In fact, once we know the calculation rules, we can use a number of ways to verify the validity of the ID card, we provide a formula: “= the IF (mids (” 10 x98765432″, the MOD (SUMPRODUCT (MID (B5, ROW (an INDIRECT (” 1:17 “)), 1) * 2 ^ (18 – ROW (an INDIRECT (” 1:17 “)))), 11) + 1, 1) = RIGHT (B5),” “Legal “,” illegal “) “, just replace the B5 cell with the cell you need.

Let’s take a look at the demo:

Of course, there are many other formulas or methods to verify the validity of the ID number, you can try it yourself if you are interested.

We solved the display problem, the length problem, the repetition problem, and the legal problem in turn. But the whole process is not perfect, for example, we can also verify that the first six locations are legitimate locations; Verify that the 8-bit date is the correct date. In this way, the correctness of id card can be guaranteed to the maximum extent. Later, I will use VBA to write a custom function that I think is perfect to verify the ID number, and then share it. If you are interested, you can discuss it together.

Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.

If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.

This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.