The specific method is as follows:
1. Look at the original data first. In this case, take 18 digit ID number as an example. Column A is 18 digit ID number, so it is necessary to extract and calculate the age and gender information of columns B and C respectively;
2. 18 The eight digits of the ID number from the seventh digit represent the date of birth. The year of birth is four digits from the seventh place, so we can extract the year of birth with the MID function. Double-click B2 and enter the formula: = mid (a2 a2,7,4);
3. Age is required. You need to subtract the year of birth from the current year. We can use the YEAR function to get the current year, for example, input =YEAR(NOW ()) to return the current year 2014;
4. Synthesize the above two parts, and get the final formula of B2: = year (now ())-mid (a2,7,4);
5. Next, let's see how to judge men and women;
18 digits of the ID number 17 digits are numbers for judging gender, with odd numbers representing men and even numbers representing women. First of all, we still use the MID function to extract 17, and input the formula: = MID (A2, 17,1);
6. Then we use the MOD function (the MOD function is the function of taking the remainder) to take the remainder of bit 17 divided by 2. If the remainder is 0, the number 17 is even, that is, the ID card is female; Conversely, if the remainder is 1, the ID card is male. We nest the IF function as follows: = if (mod (mid (A2, 17, 1), 2), male and female).
The above are the tips of win 10 system to calculate age and distinguish sex with excel ID number. Now most of them use 18 digit ID numbers. If you encounter an ID number with 15 digits, you can use the formula to upgrade to 18 digits, and then use the above formula to calculate.