Excel form ID number How to extract gender, age, date of birth in one click

Excel report presumably every one of the professionals to contact each day, do not know if you have found such a problem, do not know how to extract the information in Excel table, then today's small series teach you a few very simple tips, easy to help you quickly extract the identity of the gender, age, date of birth.

1. Extract the gender of the Excel ID card

Presumably everyone is very curious about how to extract, in fact, I do not know that we do not know that the numbers in the provinces are representative of different meanings, such as the 17th digit is odd on behalf of the boys, the same even number on behalf of girls,

At this time we apply the IF function: =if (MOD (MID (a2,17,1), 2), "Male", "female"), can quickly extract the gender.

2. Extract Excel ID (real something Age)

We are curious how to extract the province certificate inside the real and something, then today teach you how to achieve, in fact, only need two functions can be solved, the real number only to calculate how much you are born year to want in, something complex to count your birth date to the present calculation,

(1) The following describes how to count the age of real age, using the DATEDIF function:

=datedif (TEXT (MID (a2,7,8), "#-00-00"), Today (), "Y")

(2) Do you have a learning age? Let's teach you how to extract something age, using functions: = (Year (now ())-mid (a2,7,4))

3. Extraction of Birth date

Extraction of Birth date is more simple, do not need to use what function, a shortcut can be gang, (Ctrl+e) because the ID card inside the 7~14 for the number is your birth date, we just need to directly fill in the table can be extracted.

The above is today for everyone to share the Excel ID card number How to quickly extract effective information, like friends to help praise the forward sharing, thank you for your support!

Excel form ID number How to extract gender, age, date of birth in one click