身份证号变成1.1E+17:Excel保住18位的三种做法和一个死穴

身份证、银行卡、订单号,凡是超过 15 位的数字,Excel 一律按数值处理,超过 15 位之后的部分直接变 0。

这不是显示问题,是精度丢失:文件里存的已经不是原来的号了。所以关键在“输之前”。

一、先判断是不是已经废了

点单元格看编辑栏:如果显示的是 1.10101E+17,说明数值已截断,改成文本格式只会显示 110101000000000000,后几位还是 0。这种数据只能回源系统重导。

如果编辑栏里还是完整的 18 位,只是显示成科学计数法,那还有救——改格式就能恢复显示。

二、输入前:整列先设文本

选中整列(点列标),右键→设置单元格格式→文本,再输入。这一步必须在输入之前做,输完再改没用。

更快的方式是先输一个英文单引号再输号码:’110101199001011234。单引号不会显示出来,Excel 会强制按文本处理。缺点是批量粘贴时不好用。

Excel身份证号科学计数法|输入前 vs 输入后示意图
左边是已经晚了的做法,右边是正确姿势|Word联盟

三、粘贴:别用 Ctrl+V

从系统复制一列号码,直接 Ctrl+V 会按数值粘贴。改用选择性粘贴→文本,或者右键粘贴选项里的“匹配目标格式”。

如果源文件是 CSV 或 TXT,用“数据→从文本/CSV”导入,向导第二步把这列的类型选成“文本”,一步到位。

场景正确做法结果
手工输入列设为文本 或 加 ‘完整 18 位
从网页/系统复制选择性粘贴→文本完整 18 位
打开 CSV数据→从文本导入,选文本完整 18 位
已变科学计数法基本无解需重新导出

四、公式里怎么保住文本

拼接号码时用 & 会按文本走:=”‘”&A2 不会截断,但如果 A2 本身已经是截断的数值,拼出来的还是错的。

校验位可以用 MOD 和数组公式算,但前提是号码完整。截断的数据不校验,算出来也是假通过。

五、显示优化:分段和掩码

18 位连成一串不好核对,用公式分段显示:=LEFT(A2,6)&” “&MID(A2,7,8)&” “&RIGHT(A2,4)。

对外发的表要脱敏:=LEFT(A2,6)&”********”&RIGHT(A2,4),保留前 6 位地区码和后 4 位,中间打码。

注意格式改成分段后,VLOOKUP 匹配要用原始值,别拿带空格的显示值去比。

常见问题(FAQ)

把单元格改成文本格式,为什么后几位还是 0?

因为数字在输入的那一刻就被转成双精度浮点,超过 15 位的部分已经丢了。格式只影响显示,改不回来。

银行卡号也一样吗?

一样,甚至更严重:19 位卡号后 4 位会变 0。处理方式完全相同,输入前设文本或加单引号。

能不能用 TEXT 函数把科学计数法转回来?

不能。TEXT 只能改字符串表现形式,源数据已经丢精度,转出来还是错的。

写在最后

这类数据只有一个原则:输入前就设成文本。粘贴、导入、手工输入三条路都要提前处理。

已经变成 1.1E+17 的表,别在 Excel 里折腾,回原系统重新导出最快。


类似文章