Like and see, form a habit; Better to guard the mouth of the people than the stream.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
The front said about the id number input of all kinds of problems, today say what information can be extracted from the ID card, including birthplace of the province, birthday, age, gender, zodiac, constellation and so on. See how much information the ID card number contains, so usually pay attention to protect our ID card oh.
1. Extracting provinces and cities
The first six digits of the ID card number represent the provincial code, which is also the administrative code, so we can get the province through the first six digits. Of course, there is a premise, we need to prepare an administrative division code and region corresponding table, you can download online, you can also contact me, the effect is as follows:
So we can use the formula: “=LEFT(A2,6)*1” to capture the first six digits, where *1 is used to convert text to numbers. Then use VLOOKUP to reverse query provinces and cities.
VLOOKUP(LEFT(A2,6)*1) A:D,2,0) &b =VLOOKUP(LEFT(A2,6)*1) A:D,2,0)&
=VLOOKUP(LEFT(A2,6)*1, A2 :D,3,0)& VLOOKUP(LEFT(A2,6)*1) A:D,4,0)&
Then, through a quick fill, the other rows are processed as follows:
2. Extract the birthday
The 7 to 14 digits of the ID number correspond to the date of birth. So we can use the formula “=MID(A2,7,8)” to intercept the birthday string, and then use the TEXT function to format the birthday string into the date format.
= MID(A2,7,8),”0-00-00″) = MID(A2,7,8),”0-00-00″)
3. Extraction age
=YEAR(TODAY()) -mid (A2,7,4)) =YEAR(TODAY() -mid (A2,7,4)) But this is not very accurate, for example, today is February 3, 2021, if the birthday is February 4, then the year of this year minus the year of birth has to be subtracted by 1.
We can use Excel to hide function DATEDIF.
Format: DATEDIF(start time, result time, unit), where parameters can be D, M, Y, YD, YM, MD.
For example, the formula “=DATEDIF(“2020-02-04″,”2021-02-03″,”Y”)” is 0, and the unit “Y” indicates the number of years between the two dates.
So, we enter the formula in F2 cell: “=DATEDIF(TEXT(MID(A2,7,8),”0-00-00″),NOW(),”Y”)” to get the age.
4. Extract gender
The 17th digit of the ID card number indicates gender, which is the second from the bottom, with an odd number for male and an even number for female.
To judge even and odd, MOD function can be used to divide the remainder of 2, the result is 1 is odd, 0 is even; You can also use the ISODD function to determine whether it ISODD or not; You can use the ISEVEN function to determine whether it’s even or not.
So enter any formula in G2 cell: “= the IF (MOD (mids (A2, 17, 1), 2),” male “and” female “) “, “= the IF (ISODD (MID (A2, 17, 1)),” male “and” female “) “or” = the IF (ISEVEN (MID (A2, 17, 1)), “female”, “male”) “can be, the results are as follows:
5. Extract zodiac signs
The Chinese zodiac has a long history as a folk culture in China. The twelve zodiac animals include rat, ox, tiger, rabbit, dragon, snake, horse, sheep, monkey, rooster, dog and pig. Each of them has many beautiful legends. The diagram below:
We can calculate Chinese zodiac signs according to the year, just divide the year by 12 to get the remainder, and each remainder corresponds to a Chinese zodiac sign, the corresponding relationship is shown as follows:
=MID(MID(A2,7,4),12)+1,1) =MID(MID(A2,7,4),12)+1,1))
6, constellation
The twelve zodiac signs are becoming more and more popular as a foreign culture. They refer to Aries, Taurus, Gemini, Cancer, Leo, Virgo, Libra, Scorpio, Sagittarius, Capricorn, Aquarius and Pisces. The diagram below:
The calculation of the constellation is mainly based on the date. The corresponding relationship between the date and the constellation is as follows:
We can place the above date and constellation in an array and then use VLOOKUP to find the corresponding constellation. Specific use formula: “= VLOOKUP (mids (A2, 11, 4) * 1, {0,” Capricorn “, 120, “Aquarius”, 219, “Pisces”, 321, “Aries,” 420, “Taurus”, 521, “Gemini”, 622, “cancer”, 723, “Leo”, 823, “virgo”, 923, “day Libra “;1024 “Scorpio “;1123” Sagittarius “;1222 “Capricorn “},2,1)”, the results are as follows:
You can see how much information can be extracted from an ID card number, so usually pay attention to protect their ID card oh.
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.