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

Recommended>>
1、New research from Yale University and several other institutions uses robots to teach deaf children how to communicate
2、Isnt the blockchain secure Why bitcoins are still being stolen
3、The pythonfunctools module in a nutshell even a novice can read it at a glance
4、Ping An PW uses big data to crack the financial industrys wind control bottleneck
5、Who says Java is going out of style You know this doesnt exist after reading the big Java event recap of 2017

已发送

发送中