干货!别再手动输入了,Excel一招教你15位身份证号一键转18位

    在了解公式之前，我们先简单科普一下身份证号的升级规则：

年份补全： 15位的身份证号中，出生年份是2位数（如：91代表1991）。升级时，需要在年份前加上“19”。

末尾校验码： 18位身份证的最后一位是校验码。它是由前17位数字通过特定复杂的公式计算出来的，可能是0-10的数字，如果是10，则用罗马数字“X”表示。

    所以，我们的任务就是：把15位数字拆解，补上“19”，再计算出一个新的校验码贴在最后。

    假设你的数据A列是15位身份证号，我们从B列开始转换。

第一步：拆解旧号码

    由于15位身份证号的结构是：6位地址码 + 6位出生日期码 + 3位顺序码。

    我们需要把它们拆出来：

提取地址码+出生年份+顺序码（补19）：     在B2单元格输入公式：

解析： LEFT(A2,6) 提取前6位地址码；& "19" 连接上世纪的“19”；MID(A2,7,6) 从第7位开始提取后面的9位（即6位出生日期码+3位顺序码）。

    此时，我们得到了一个 17位 的数字（这是计算校验码的基础）。

    这是最复杂的一步，但Excel的强大在于我们可以嵌套公式。身份证最后一位的校验码是根据国家标准《GB 11643-1999》中的系数加权计算而来。

    直接上最终公式（请直接复制使用，只需要修改单元格地址A2即可）：

在C2单元格输入最终转换公式：

看不懂没关系，我们把它合并成一个公式（一步到位版）：

    如果你想一步到位，不需要辅助列，可以直接使用这个 终极嵌套公式：

    把这个公式粘贴到B1，然后下拉填充，你会发现所有15位号码都变成了标准的18位。

    如果觉得上面的公式太复杂容易出错，可以按照下面三步走：

补全年份： 假设A2是15位号码，在B2输入 =REPLACE(A2,7,0,"19")。这个函数的意思是在第7位前面插入“19”。你会得到一个17位的数字。其中0表示插入字符。

计算校验位： 这一步必须使用上面提到的LOOKUP+SUMPRODUCT组合公式。我们只需要引用第一步得到的17位数字所在的单元格即可。

合并结果： 如果第二步的结果在C2，那么D2输入 =B2&C2，就能得到最终结果。

单元格格式： 由于身份证号码超过15位，Excel会默认显示为科学计数法（如：1.10101E+17）。转换成功后，记得选中该列，右键点击【设置单元格格式】，在【数字】选项卡中选择【文本】。

适用性： 本公式适用于第一代居民身份证（1999年前后签发的证件），这些证件出生年份多为19xx年。如果是18xx年出生的老前辈，这个方法就不适用了，但现实中极少遇到。

校验X： 如果你的结果最后一位出现了“X”，那是正确的，代表校验码为10，请务必保留大写X。

写在最后

    Excel的强大之处就在于，它能将枯燥繁琐的重复劳动，变成一次性的函数设置。掌握了这个小技巧，下次处理员工档案时，就能事半功倍了。

    觉得本文有用的话，别忘了点赞和转发，分享给更多需要的小伙伴！

互动： 文末可以加一句“你有被Excel的长数字自动变科学计数法坑过吗？评论区聊聊”。