案例30 将15位身份证号码升级为18位（REPLACEB）

⊙ 源文件：CDROM\04\4.11\案例30.xls

将长度是15位的身份证号码转换成18位，18位者保持不变。打开光盘中的数据文件，在单元格C2中输入公式：

=IF(LEN(B2)=18,B2,LEFT(REPLACEB(B2,7,,19),17)&MID("10X9 8765432",MOD(SUM(MID(REPLACEB(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1))

按下【Enter】键后，公式对身份证号码进行转换，复制单元格 C2中的公式到其他单元格，结果如图4-22所示。

图4-22 将15位身份证号码升级为18位

公式说明

本例公式主要有两个重点。其一是将表示日期的6位字符转换成8位，例如将字符串“780912”替换为“19780912”；其二是将15位身份证号码在末尾添加一个校验码，校验码是从“10X98765432”字符串中提取出来的。

案例提示

1.本例公式适用于1900年以后出生者的身份证号码转换，18XX年出生者不适用。但鉴于工作中不可能遇到这种人的身份证号码转换，故此忽略。

2.B 列的身份证号码必须要作为文本型数据录入，否则将产生错误。因为Excel对数字的计算精度只有15位，在单元格录入超过15位的数字时， Excel只记录前 15位数字，后面的值截去或以 0代替，这样的话身份证号码转换后会产生错误。