Automatically calculate province based on ID number

The first two digits of the ID card represent the province. You can extract the province information as long as you know the corresponding code of each province.

1. IF function calculation

Use the IF function to list each case for calculation.

The following code can be directly copied to the formula editor,Replace the ID number field.

{"text":"IF(EQ(LEN(​身份证号​),18),IF(EQ(VALUE(MID(​身份证号​,1,2)),11),\"北京市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),12),\"天津市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),13),\"河北省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),14),\"山西省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),15),\"内蒙古自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),21),\"辽宁省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),22),\"吉林省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),23),\"黑龙江省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),31),\"上海市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),32),\"江苏省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),33),\"浙江省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),34),\"安徽省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),35),\"福建省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),36),\"江西省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),37),\"山东省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),41),\"河南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),42),\"湖北省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),43),\"湖南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),44),\"广东省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),45),\"广西壮族自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),46),\"海南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),50),\"重庆市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),51),\"四川省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),52),\"贵州省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),53),\"云南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),54),\"西藏自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),61),\"陕西省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),62),\"甘肃省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),63),\"青海省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),64),\"宁夏回族自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),65),\"新疆维吾尔自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),83),\"台湾\",IF(EQ(VALUE(MID(​身份证号​,1,2)),81),\"香港特别行政区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),82),\"澳门特别行政区\",\"\")))))))))))))))))))))))))))))))))),\"\")","marks":[{"from":{"line":0,"ch":10,"sticky":null},"to":{"line":0,"ch":16,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":38,"sticky":null},"to":{"line":0,"ch":44,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":77,"sticky":null},"to":{"line":0,"ch":83,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":116,"sticky":null},"to":{"line":0,"ch":122,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":155,"sticky":null},"to":{"line":0,"ch":161,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":194,"sticky":null},"to":{"line":0,"ch":200,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":236,"sticky":null},"to":{"line":0,"ch":242,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":275,"sticky":null},"to":{"line":0,"ch":281,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":314,"sticky":null},"to":{"line":0,"ch":320,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":393,"sticky":null},"to":{"line":0,"ch":399,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":432,"sticky":null},"to":{"line":0,"ch":438,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":354,"sticky":null},"to":{"line":0,"ch":360,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":471,"sticky":null},"to":{"line":0,"ch":477,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":549,"sticky":null},"to":{"line":0,"ch":555,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":510,"sticky":null},"to":{"line":0,"ch":516,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":588,"sticky":null},"to":{"line":0,"ch":594,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":627,"sticky":null},"to":{"line":0,"ch":633,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":666,"sticky":null},"to":{"line":0,"ch":672,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":705,"sticky":null},"to":{"line":0,"ch":711,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":744,"sticky":null},"to":{"line":0,"ch":750,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":826,"sticky":null},"to":{"line":0,"ch":832,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":865,"sticky":null},"to":{"line":0,"ch":871,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":783,"sticky":null},"to":{"line":0,"ch":789,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":943,"sticky":null},"to":{"line":0,"ch":949,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1021,"sticky":null},"to":{"line":0,"ch":1027,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1101,"sticky":null},"to":{"line":0,"ch":1107,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1062,"sticky":null},"to":{"line":0,"ch":1068,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1140,"sticky":null},"to":{"line":0,"ch":1146,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1222,"sticky":null},"to":{"line":0,"ch":1228,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1304,"sticky":null},"to":{"line":0,"ch":1310,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1347,"sticky":null},"to":{"line":0,"ch":1353,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1266,"sticky":null},"to":{"line":0,"ch":1272,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1179,"sticky":null},"to":{"line":0,"ch":1185,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":982,"sticky":null},"to":{"line":0,"ch":988,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":904,"sticky":null},"to":{"line":0,"ch":910,"sticky":null},"value":"textField_l200ci6g","invalid":false}],"isCmData":true}

• IF
• MID(A,B,C): in A string, C characters are retrieved from bit B.
• VALUE(): converts the string retrieved by the MID function into a number.
• EQ(value1,value2) returns true when two values are equal. It supports numbers and dates.

Calculate province formula

2. Video demonstration

此处为语雀视频卡片，点击链接查看：根据身份证号自动计算省份.mov

Automatically calculate province video demonstration

3. For more information about identity, please read the document.👇:

1.Automatically calculate birthday based on ID number

2.Automatically calculate gender based on ID number

3.Automatically calculate constellation based on ID number

4.Automatically calculate zodiac signs based on ID number

YIDA in order to better optimize the content and quality of YIDA user manual, it takes you 3-5 minutes to fill in the document feedback questionnaire. The document feedback questionnaire is submitted anonymously, and the questionnaire information is only used for YIDA document experience feedback collection. Thank you for your support for YIDA!

Click here to fill in the questionnaire

-------------------- Get the latest information YIDA, welcome to follow US--------------------