用文本函数玩转身份证提取出生日期

Work, inevitably, many times need to extract a variety of information about the employee through the ID number, here today to tell you how to extract the birth year in the ID number with the help of excel in the MID () function.

Here the use of the MID () function, which is a text function, that is, with the help of its implementation to extract the text string, this function has a total of three parameters, respectively, the extraction of the cell, the location of the first few extracted position, the interception of the fragment.

So here we note that the ID number starts from the 7th digit for the year of birth, and then the year of birth is 8 digits in total, so the final formula should be filled in like this.

Next for the column of gender, we perform calculations on it. The 17th odd or even digit of the ID number represents its gender. If odd, it is indicated as male; if even, it is indicated as even. Then here you need to take the help of the remainder function MID() to determine its parity.

The formula is as follows, using the mid () function to extract the 17th digit, and then with the help of mod () function to take the remainder, and finally determine the male female with the help of if function to achieve.

** More dry goods, free download**