How excel automatically matches gender code according to ID number

Use excel form , Sometimes you need to enter your identity 、 Gender and other information . The ID card can be machine swiped or imported , But if the gender is entered manually , Too many people are troublesome . In particular, we need to deal with a large number of student data in the recruitment work of our school , ID number and gender are essential data .

Today I'd like to share a excel Code , It can automatically match the gender of men and women according to the ID number .

Everyone knows that every digit of China's identity number has a specific meaning , And the penultimate ID number （18 No. of ID number 17 position ） It refers to the signs of men and women , If the first 17 If you are single, you are male , Even numbers are women .

Knowing this, we can easily write a code to automatically identify the gender of men and women in ID cards .

The code is ：IF(MOD(MID(C2,17,1),2=1," male "," Woman ") 

The formula should be ：=IF(MOD(MID(C2,17,1),2)=1," male "," Woman ")

Reading ：

MID(C2,17,1) The function takes the number of ID number 17 position ;

MOD(MID(C2,17,1),2)=1 Yes, please 17 Whether the number of digits is odd ( The first 17 Number of digits divided by 2 The remainder after is 1 It's an odd number ）;

IF(MOD(MID(C2,17,1),2)=1," male "," Woman ") If the judgment is odd, it is male , Otherwise, it's female