[Excel] extracts the date of birth, age, and gender information from the ID card number

Execl is powerful. Today, we will introduce how to extract the birth date, age, and gender information from the ID card number.

First paste the effect:

Note 1: it is a coincidence that the ID card number is randomly entered ....

Edit procedure:

1. Column A is a user-Filled Column. the ID card number is more than 15 digits. Therefore, you must enter'

2. Column B is the user's age column, and the formula is used.

= Year (today ()-If (LEN (A1) = 15, concatenate ("19", mid (A1, 7,2), mid (A1, 7,4 ))

3. Column C is the user's date of birth column, and the formula is used.

= If (LEN (A1) = 15, text (concatenate ("19", mid (A1, 7,6), "#-00-00 "), text (mid (A1, 7,8), "#-00-00 "))

4. column D is the user gender column, and the formula is used.

= Year (today ()-If (LEN (A1) = 15, concatenate ("19", mid (A1, 7,2), mid (A1, 7,4 ))

NOTE 2: replace A1 with the corresponding cell in the formula.