身份证号以文本存储的解决

作者:  //  2013 年 1 月 26 日  //  Excel相关  //  没有评论

身份证号以文本存储的解决

几天前,微博有个求助:身份证号存储为CSV文档后,再次使用Excel打开时,18位身份证号会以科学计数方式显示,且后3位的数字会变为000,见下图。第1反应是在身份证号前加英文半角单引号:’,但这只对Excel文档有效,对CSV文档没有丝毫效果,再次打开,显示效果涛声依旧。貌似这只能使用折衷方案:在身份证号前加入其它文本字符。

❶ 分析:

今天闲来无事,再次确认这个问题时发现:不论你是否强制设置身份证号的单元格数字格式为文本,或在其前加入英文半角的单引号:’,其在存储为CSV文档时其都会以数字形式存储,并不以文本方式存储。而再次打开时,Excel又会自作聪明的将其视为科学计数方式的数字,但是这个“几十亿亿的巨大数字”,已经远超Excel处理水平,因此出现:后3位的数字会变为000。

问题的解决似乎简单了;如何强制Excel打开CSV文档时不使用科学计数即可。Excel在打开Txt格式文档时可以自动调出[文本导入对话框],利用分列功能可以非常方便的设置数字格式为文本,但CSV文档却是直接打开的,搜索谷歌、度娘亦无解。难不成要将csv文档后缀改为:.txt?

❷ 解决:

问题回到原点,即如何强制Excel将身份证号设置为文本,所有问题将迎刃而解。使用VBA或是比较好的解答,但限制较多。Excel可以有多种方式将数据库导出的文本设置为数字格式,但要逆向操作却并不好使。既然在身份证号前加文本字符可以强制变为文本,是不是可以插入ASCII码对应的制表符?于是利用CHAR()函数,果断B2=CHAR(9)&A2 [注:CHAR(9)为Tab键 水平制表符],再次尝试确实可行,如下图。

但并不完美,受使用字体影响,再次打开后身份证号前会出现了空白,使用记事本打开这个CSV文档可见[” 536102199807130215″]。既然CHAR()函数是处理ASCII码的,是否可以插入ASCII码对应的分割符?再次使用B2=CHAR(28)&A2 [注:CHAR(28)为文件分隔符],这次完美了 :lol: 。后又尝试了CHAR(29)[分组符],CHAR(30)[记录分隔符],CHAR(31)[单元分隔符]均非常完美,如下图。且这个CSV文档在不编辑身份证号单元格前提下,再次在Excel中保存在打开依然完美!

唯一不完美:由于使用了额外的字符,其字符长度为19位,这相比加英文半角单引号:’,要多出1位,利用公式提取出生年月时需特别注意。使用该方案是一个将Excel数字彻底变更为文本的解决方案,且使用选择性粘贴:文本,也照样可行。表亲:从此你再也无需在Excel中为身份证号折腾了。

❹ 备注:

如若不考虑Excel直接来打开CSV文档,而是将CSV格式文档内容直接导入到新工作簿,则可以以纯文本方式的分列来解决,则可以完全忽略上述内容。如下图:

相关测试案例下载:

新浪微盘提取码: 【IKLX】

关于作者

Ms Office爱好者,仅仅就是一个懂得高效偷懒的伙计,但绝对不是属于Office软件的骨灰级玩家,相对注重实用。比较热衷数据可视化,当然这个爱好中忒注重视觉的部分,和我的工作基本无关,但就是喜欢。

查看所有 的文章