Skills of extracting ID card number and sharing information in Excel

Original title: "Excel knows all the secrets hidden in ID number!"

As we all know, there is a lot of personal information hidden in the ID card number. What is the date of birth, gender, etc. are all in it. Today, Teacher Xiao Wang will use Excel to help you smooth it out!

01. Extract the last 8 digits of the seventh digit of the birth date ID card number, which is the birth date, so we can extract the birth date through the formula. The formula is as follows:

=TEXT(MID(B2,7,8),"0-00-00")

02. Get the age. In the previous step, we obtained the date of birth through the ID card number. Then, we can obtain the age through the date of birth. The formula is as follows:

=DATEDIF(C2,TODAY(),"y")

The 17th digit of the ID card number can be used to determine the gender of men and women. Odd numbers are male and even numbers are female. The formula is as follows:

=IF(MOD(MID(B2,17,1), 2),"M","F")

04, get zodiac first through the ID card number to get the date of birth, and then through the date of birth to get zodiac. The formula is as follows:

=MID("Mouse, Ox, Tiger, Rabbit, Dragon, Snake, Horse, Sheep, Monkey, Chicken, Dog, Pig",MOD(YEAR(C2)-4,12)+1,1)

This article comes from Weixin Official Accounts: Word Alliance (ID: Wordlm123), author: Wang Qi