How easy is it to use Excel to automatically extract hidden information from your ID card?

Hello, everyone. I'm Xiao Yin.

I had a friend's birthday yesterday and made an appointment to have dinner tonight.

But before I got off work, my boss sent me a form.

He asked me to fill in my birthday, age and sex according to everyone's ID number.

This one, I. Will only be entered manually.

Colleague: why are you still here? Aren't you in a hurry to go to dinner?

Me: the boss has added a temporary task, so it's hard to say whether he can eat or not.

Colleague: fill in the ID card information? I'm here to make sure you have something to eat!

Let's see what to do.

Birthdays and birthdays are already marked in the ID card number, which we can extract directly.

❶ manually enters the first person's birthday according to the ID card number.

❷ then press the shortcut key [Ctrl+E] to quickly fill in, and you can automatically extract other people's birthdays.

Age cannot be extracted directly from the ID card number, so you need to use the function at this time.

❶ first enter this string of functions in the cell:

= YEAR (NOW ())-MID (B2jing7 (4)) here, YEAR (NOW ()) represents the year of this year, while MID (B2jing7 (4)) indicates the year in which everyone on the ID card was born. The subtraction of the two is everyone's age!

Let's review the use of the MID function:

= MID (text, start position, number of characters) text: the cell in which the content is to be extracted; (the ID card number in the table is in cell B2)

Start position: from which bit to start extraction; (start with bit 7 to indicate the year)

Number of characters: several digits are extracted. (there are 4 in total in the year)

❷ finally double-clicks the lower right corner of the cell to quickly fill it with the fill handle.

To judge gender, we first need to understand the meaning of the 17th digit of the ID card:

The odd number stands for "male" and the even number represents "female".

❶ enters the function in the cell:

= IF (MOD (MID (B2jin17) 1), 2) = 1, "male", "female")

In this function, the MOD function returns the remainder of the division of two numbers.

According to the above, we can know that MID (B2Power17jue 1) extracts the 17th digit of the ID card number, so the whole function can be understood as:

If the 17th bit of the ID card divided by 2 equals 1 (odd), the gender is judged to be "male"; otherwise (even), the gender is judged to be "female".

❷ finally, also double-click the lower right corner of the cell to fill it quickly.

How is it? are these methods for automatically extracting ID card information very convenient?

With the help of God, I finally filled out the form before getting off work.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaoyin, Editor: Zhu Lan