Excel身份证手机号输入就变E+17、日期还显井号？分清4种情况再动手，一次规范不错位（附对照表）

Excel身份证、手机号一输入就变E+17，日期还显示井号？分清4种情况再动手

不少人在Excel里录身份证号、手机号、卡号时都踩过坑：明明敲了18位数字，单元格却显示成4.32E+17；手机号开头的0不翼而飞；日期输完变成一长串数字，或者直接显示一屏####；更气人的是，明明看着是数字，求和却算出0。这些都不是Excel"坏了"，而是数据被当成了错误的类型。本文按最常见的4种场景拆解原因，并给出Excel和WPS的完整操作步骤，帮你一次规范到位。

先说结论：Excel处理数字有两条底层规则，绝大多数"变样"都源于它们。一是数字超过15位精度会自动转成科学计数法（E+17），且第16位起会被当作0；二是以0开头的数字会被自动去掉前导0。要保留身份证、手机号、卡号这类"像数字但不是用来算的"数据，核心思路只有一句话：让它按文本存储。下面逐一展开。

一、身份证号变成E+17：先设"文本"再输入

身份证号是18位，早已超过Excel的15位精度上限，所以默认会显示成科学计数法，而且输入后再设置格式已经晚了——后面的几位已经丢失成0，无法找回。所以正确做法是先把目标列设为"文本"格式，再录入。

Excel操作步骤

• 选中要录入身份证号的整列，或先选好一块空白区域。
• 按快捷键Ctrl+1打开"设置单元格格式"对话框。
• 切换到"数字"选项卡，在左侧"分类"列表中选择文本。
• 单击"确定"。此时在该区域录入身份证号即可完整显示，左上角还会带绿色小三角提示（表示是文本，属正常）。

如果你希望每次新建表格都默认处理身份证，也可以先按上述方法把一列设为文本并保存成模板，下次直接套用。

WPS表格操作步骤

• 选中目标列或单元格区域。
• 右键单击选中的单元格，选择设置单元格格式（或按Ctrl+1）。
• 在"数字"选项卡的"分类"中，下拉选择文本。
• 单击"确定"后再输入身份证号即可正常显示。

一个小技巧：单独输入时，也可以在数字前加一个英文撇号（半角单引号'），回车后Excel/WPS会把后面的内容当文本处理，但撇号本身不显示。不过批量录入时建议统一用"先设文本格式"的方式，更规范。

如果身份证号是从其他地方复制粘贴进来的，建议先把粘贴目标列设为文本，粘贴时用"只粘贴值"或"匹配目标区域格式"，避免因列宽或格式导致再次变形。

二、手机号前导0消失、变短：文本格式或自定义格式

手机号虽然通常只有11位，但分机号、国际区号、或者有些单据编号可能以0开头。Excel默认会把前导0删掉，例如输入01234567890会变成1234567890。解决办法有两种。

方法一：设成文本（最稳妥）

• 选中目标列，按Ctrl+1，在"数字"->"分类"中选择文本，确定后再录入或粘贴。

这类号码不需要参与计算，设成文本最保险，也最能保证"所见即所得"。

方法二：自定义数字格式（想保留数值运算时）

如果号码要参与公式，又想显示前导0和分隔符，可以用自定义格式。微软官方给出的例子：000-00-0000可显示美国社保号，00-0-000-000-0000可格式化长串号码。

• 选择单元格区域，按Ctrl+1打开设置单元格格式。
• 在"数字"选项卡的"分类"中选择自定义。
• 在右侧"类型"框中输入如000-0000-0000这样的格式代码，单击"确定"。

注意：自定义格式不会还原已经被删掉的前导0，只对格式设置之后输入的数字生效；而且它属于显示效果，单元格底层仍是数值。所以对"0开头、位数固定"的号码，优先用文本格式更省心。

三、日期变成####或一长串数字：列宽不足与数字格式

日期相关有两个常见误区，要分开解决。

情况1：显示成####（井号）

当单元格列宽不足以显示完整日期/数字，或日期、时间为负值时，会显示####。这不是数据没了，而是"放不下"。

• 最简单：把鼠标移到该列顶部字母右侧的边界线上，指针变成双向箭头后双击，列宽会自动适应内容。
• 也可以直接拖动列右边界，或选中列后右键->"列宽"手动调大。
Excel路径：选中日期列，在"开始"选项卡点"数字格式"下拉箭头，选择短日期，即可恢复显示。 WPS路径：选中列，右键->设置单元格格式->数字，选择"日期"并挑一个短格式（如yyyy/m/d），确定即可。

如果列已经够宽仍显示####，通常是日期或时间计算出负值（例如结束时间早于开始时间）。检查公式是否正确，或把公式结果格式改成普通数字。

情况2：日期显示成一串数字（如45372）

Excel把日期存储为从1900年1月日起的天数（序列值），只有套上"日期"格式才会显示成 yyyy/m/d。若你看到的是纯数字，说明该单元格格式是"常规"而非"日期"。

• 选中数字单元格，按Ctrl+1，在"数字"->"分类"中选择日期。
• 在右侧"类型"列表中挑一个格式（如 yyyy年m月d日或 yyyy/m/d），点"示例"预览后确定即可。

自定义日期代码也很有用：yyyy显示四位年、mm显示01-12、dd显示01-31。在"分类"选"日期"后再切到"自定义"，就能看到并修改这套代码。

四、看着是数字求和却为0：文本型数字转数值

从系统导出、或复制网页/数据库的数据，经常是"看起来是数字、实际是文本"，特征就是单元格左对齐、左上角有绿色小三角。此时SUM求和会跳过这些文本，结果偏小甚至为0。需要把它们转成真正的数值。以下方法在Excel和WPS中通用。

方法一：错误检查一键转换（少量）

• 选中带绿色三角的单元格区域。
• 点击单元格旁出现的黄色感叹号错误提示按钮。
• 在菜单中选转换为数字，绿色三角消失、数字变右对齐即成功。

方法二：分列批量转换（整列推荐）

• 选中整列文本数字。
• 在"数据"选项卡点击分列（Excel与WPS均可）。
• 向导第一步、第二步都直接点"下一步"（不做拆分）。
• 第三步"列数据格式"选择常规或数值，点击"完成"。

整列文本数字会一次性转为数值，可参与求和。

方法三：选择性粘贴乘1（数值不变）

• 在任意空白单元格输入1，选中并复制。
• 选中要转换的文本数字区域，右键->选择性粘贴。
• 在"运算"区域勾选乘，点确定。所有数字乘以1后保持值不变，但转成数值格式。

方法四：VALUE函数（保留原列）

• 在空白辅助列输入=VALUE(A2)（A2为文本数字所在单元格），回车。
• 向下填充公式，得到一列真正的数值，再复制粘贴为值即可替换原列。

WPS还提供一键工具：切换到"会员专享"->"智能工具箱"，点"格式"->"文本型数字转为数字"，可批量完成转换，适合大量数据。

五、身份证号录好以后，怎么提取出生日期

身份证号存成文本后不乱码只是第一步，很多人还要从18位号码里自动提取出生日期、判断年龄或性别。这时可以用函数配合文本格式完成，因为身份证本身是规范的18位（前6位地区、7-14位出生日期、17位性别、18位校验位）。

提取出生日期

假设身份证号在A2单元格，用MID函数截取第7-14位，再用DATE拼成日期：

• 输入公式=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))并回车。
• 若返回的是数字，选中结果列按Ctrl+1设为"日期"格式即可显示为yyyy/m/d。

判断性别（第17位奇男偶女）

• 输入=IF(ISODD(MID(A2,17,1)),"男","女")，MID取出第17位，ISODD判断奇偶。
WPS中的MOD写法也可：=IF(MOD(MID(A2,17,1),2)=1,"男","女")。

校验身份证位数（防录入错）

• 用=IF(LEN(A2)=18,"正确","错误")检查长度是否为18位，方便批量排查漏录。

重要前提：这些函数都要求身份证号码是完整的纯文本。如果号码已经变成E+17且尾部丢失成0，提取出来的出生日期和性别就会错。所以务必先按第一章方法把号码存成文本，再套函数——这也是为什么"录入前先定格式"如此关键。

六、日期录入的快捷技巧

规范日期格式不仅能避免显示成####，还能提升录入效率。以下技巧在Excel和WPS中均支持。

• 快速输入当天日期：按Ctrl+;（Ctrl加分号）立即插入当前日期；按Ctrl+Shift+;可插入当前时间。
• 自动更新的今天：输入=TODAY()，每次打开或重算都显示为当前日期；需要带时间用=NOW()。
• 默认日期格式快捷套用：选中日期单元格按Ctrl+Shift+#，快速套用默认短日期格式。
• 文本日期转真日期：若从记事本粘贴进来的是文本型日期，直接改格式无效，需用"分列"或=DATEVALUE(A2)先转成真正的日期序列值，再设格式。

关于1904日期系统：如果日期相减或时间计算出现负值导致####，可改为1904日期系统显示负时间（文件->选项->高级，勾选"使用1904日期系统"）。但注意这会改变整套工作簿的日期基数，非必要不要启用，仅当确实要显示负时间才用。

七、四种场景对照表

八、小结与避坑提醒

把"像数字但不是数字"的字段（身份证、手机号、卡号、编号）统一存成文本，是治本之策；把"真正要算的数"确保是数值，是求和不出错的前提。记住三个要点：

• 先设格式、后输入：身份证、0开头编号务必先设文本，否则事后改格式救不回已丢失的数字。
• 看到####先看列宽：多半是列太窄，双击列边界即可，别急着删数据。
• 求和为0先查绿三角：用"转换为数字"或"分列"一把转数，别手动一个个改。

以上操作在Microsoft Excel（桌面版）与WPS表格中路径一致，均经微软官方与WPS官方资料交叉验证。判断数据该当文本还是数值，只需问一句：这串数字将来要参与加减乘除吗？不参与就按文本存，参与就转成数值。避开这四个坑，你的入库数据就不再"看着对、算着错"，身份证、手机号、日期都一次规范到位，后续做VLOOKUP、透视表、求和也都不再被格式问题拖后腿。建议把这些设置固化进常用模板，让团队的表格从一开始就干净规范。