Excel身份证变E+17我试了3招找回

Excel身份证变E+17我试了3招找回

上周三下午，行政的同事发来一个表格，让我把几十个员工身份证号核一遍。

我打开一看，整个人都不好了。身份证号全变成了一串 1.23E+17 这样的玩意儿，还有几个尾数直接成了 000。我问她怎么录的，她说就正常复制粘贴进去的，粘完就成这样了，还问我是不是她电脑坏了。

这不是电脑坏了，是 Excel 的老毛病。

Excel 记数字，精度最多到 15 位，身份证号有 18 位，超出了它能记的范围，后面几位它记不住，就擅自给你抹成 0，再显示成科学计数法。所以凡是身份证号、银行卡号、手机号这种长数字，直接往 Excel 里输，十有八九要翻车，而且翻得很隐蔽，你当时可能没注意，等发现问题，数据已经坏了。

这里要提醒一句，数据一旦变成科学计数法，后面被抹掉的数字就真的找不回来了，Excel 不会帮你留底。所以那些已经变坏的身份证号，别指望在原表里还能恢复出完整的号，只能靠我下面的办法重新整理格式，或者拿原始数据重新录一遍。这也是我为什么要把这几个办法记下来，遇到一次就是一次血泪。

我那天下午折腾了两个多小时，试了好几种办法，最后留下三个管用的，按好用程度排个序。

第一个，也是我以后最常用的，输之前先把格子设成文本。

操作很简单：要填身份证的那一列，先整列选中，右键，选设置单元格格式，在数字那一栏里点文本，确定，然后再往里面输身份证号，它就会老老实实按原样显示，一个数字都不丢。

要是嫌麻烦，还有一个更快的土办法，输入之前先敲一个英文单引号，就是键盘上逗号旁边那个符号，比如先敲个引号再输身份证号，输完回车，引号不会显示出来，身份证号也是完完整整的。这个小撇号就是告诉 Excel，别拿我当数字，我这是文字。

第二个办法，是专门救已经变坏的。

如果你和我同事一样，数据已经粘进去了，全是 E+17，别慌，还有救。选中那列，点菜单栏的数据，找分列，一路点下一步，到第三步让你选列数据格式的时候，点文本，完成。这时候你会发现，那些科学计数法全变回完整的身份证号了。原理很简单，就是强制让 Excel 把这一列当文字重新认一遍。

我同事那几十个号，就是用这招救回来的，一分钱没花，几分钟搞定。

第三个办法，是批量粘贴的时候用的。

如果身份证号在别的表格或者网页里，你要一次性贴一大堆，直接 Ctrl+V 大概率又变 E+17。稳妥的做法是，先把目标列设成文本格式，然后粘贴的时候选选择性粘贴，再选文本，这样整批数据都会以文字的形式进来，不会变科学计数法。或者更粗暴一点，先把数据贴到记事本里，再从记事本复制，贴到已经设好文本格式的 Excel 里，中间过一道手，就什么问题都没有了。

我那天就是先用第二个办法把坏数据救回来，又用第三个办法重新录了一批新员工的号，从下午三点折腾到五点，总算是把表格整利索了。

顺带说一句，手机号虽然只有 11 位，一般不会触发科学计数法，但有些带 +86 前缀或者特别长的号码，偶尔也会出问题，最稳妥的做法还是统一设成文本格式，一劳永逸。

顺便说几个容易踩的小坑。一个是，分列恢复的时候，一定要在第三步选文本，我一开始偷懒没选，直接点完成，出来还是 E+17，白忙活。另一个是，恢复完的身份证号，单元格左上角可能会有个小绿三角，那是 Excel 在提醒你这是个文本，不用管它。还有，一个表格里很容易混着两种格式的身份证号，有的文本有的数字，看起来一模一样，拿去匹配却怎么都对不上，这时候全选那列再走一遍分列，统一转成文本，两边就一致了。如果你要拿身份证号去匹配、去重、做 VLOOKUP，记得先把格式统一这一步做了。

对了，如果你就是想让身份证号显示成纯数字的样子，又不想变科学计数法，可以在单元格格式里选自定义，输入一串十八个零，也能凑合着显示，但这个属于偏方，没有前两个办法稳。

回头我把这招教给行政同事的时候，她感叹了一句，说她做了五年表格，从来不知道还有分列这个功能。我说你以后录身份证号之前，记得先把格子设成文本，就再也不用受这个罪了。

评论区聊聊，你有没有被 Excel 的科学计数法坑过，除了身份证，还丢过什么更重要的数字？

本文由公众号原创 · 转载请注明出处