出现这个现象的原因是Excel软件把全数字形式的身份证号码当作18位的数字来处理了，包含字母X的身份证号码就不会出现这个现象。

　　解决方法就是把数字变成文本格式，有两种方法：

　　第一种：在输入身份证号码前，先输入单引号，注意是英文标点的单引号，这是转义字符，Excel软件将会把后面的数字作为文本来处理。

　　第二种：在输入身份证号码前，先选中要输入身份证号码的单元格，右键-设置单元格格式，打开设置单元格格式对话框，如图，切换到“数字”选项卡，单击“分类”中的文本，再单击确定按钮，这时再输入任何数字，Excel软件都会作为文本来处理。

　　补充：众所周知，身份证号码是18位。如果将身份证号码当作数字来处理的话，Excel软件是以科学计数法的形式来表现的，而Excel 采用 15 个有效数字的精度来存储和计算数字，其中，小数点要占用掉一位有效数字，超出的部分以0来代替。这样就不难理解，直接输入的身份证号码后四位变成0的原因了。

（本文内容由百度知道网友sxpchlszy贡献）

问题来源

输入身份证号码是EXCEL数据处理时经常遇到的情况。但是身份证号码位数较多，现在一般都是18位，一旦输入不慎，就会出现倍数错误。今天韩老师教给大家一种“条件格式”的设计方法，只要位数错误，单元格会自动填充颜色，以提示出现了错误。

为了预防现在还在15位身份证的情况，韩老师写条件格式的时候也把15位的考虑在内。

结果如下：

（单元格只默认15位有效数字，所以身份证输入之前，先输入一个英文单引号，把单元格格式转为文本，该单引号不占字符数）

1、选中数据A列，【开始】——【条件格式】——【新建规则】：

2、【新建格式规则】——【使用公式确定要设置格式的单元格】，输入公式：

=AND(LEN(A1)<>15,LEN(A1)<>18,A1<>'''')：

3、设置单元格格式填充为你想要的颜色：

公式解析

=AND(LEN(A1)<>15,LEN(A1)<>18,A1<>'''')：

当单元格长度不是15和13，并且单元格不是空时，填充某种颜色。

文/小雨

图/网络

Excel由于其强大的数据处理功能，成为了办公软件的主流软件之一。在使用Excel来处理数据的时候，我们也会遇到一些奇怪的问题。

比如，当我们要在Excel里面输入后18位的身份证号码的时候，会发现：明明输入的是18位的数字，但是等输入完后发现最后4位却成了“0”。

难道Excel不支持输入身份证号吗？当然不是了。

实际上，Excel在处理数据的时候也有一个潜规则：

输入数字超过了12位数值，Excel则会自动转换为科学记数格式，输入如果超出了15位数值，Excel则会自动将15位以后的数值转换为“0”。

那么怎么解决这个Excel输入身份证后四位变成0的问题呢？小雨在这里提供2种解决方法。

第一种解决方法：

将单元格的格式设置为【文本】类型

Excel默认会根据我们输入的内容格式按照它自己的系统设定来做出一些调整。但是我们在输入18位的身份证号后就不需要它再做这样的调整了。所以，我们需要手动将这个单元格内的格式设置为【文本】类型。

具体设置方法：

首先，选中所需输入身份证号码的所有单元格，然后【右键】-【设置单元格格式】-【文本】，确定即可，此时，我们再去输入身份证号即可正常显示！

第二种解决方法：

在输入身份证号码之前先在最前面直接输入一个单引号【’】，然后再输入身份证号码。这样输入后就会正常显示，并且最前面输入的那个单引号也不会显示出来。需要注意的是，在输入单引号的一定要将输入法切换到英文状态下才可以。

这种方法操作起来特别简单，只要记住这个单引号就可以了，非常方便实用。

好了，以上就是Excel输入身份证后四位变成0的解决方法，只要一个字符轻松搞定！今天的分享就到这里。

小伙伴们，大家好！

平时我们在填一些表格的时候，身份号码的每个数字一般都有方框，主要是为了防止多填或者少填。

那么，在Excel里可以实现这个效果吗？

方法：公式+选择性粘贴

步骤：新建一个工作表，在任意一行填好1至18，并画出18个方框。

步骤：在第一个方框内输入公式【=MID(Sheet1!$B$3,Sheet2!C13,1)】，然后左键按住单元格右下角，拖动至第18个单元格。

Tips：MID函数讲解

MID(text, start_num, num_chars)

text：被截取的字符

start_num：从左起第几位开始截取

num_chars：从左起向右截取的长度是多少

步骤：1. 将身份证号码单元格中的字体颜色调成白色；

2. 复制由公式产生的带方框的身份证号码；

3. 在身份证号码单元格按右键，【选择性粘贴】-【链接的图片】，调整图片大小；

4. 用方向键定位到身份证号码单元格，在上方编辑区域可以调整号码，粘贴过来的图片会自动更新。

好了，是不是非常实用？

欢迎关注“职场e学”，各种职场技能，一学就会，让我们一起变成更厉害的自己！

我们都知道身份证号码的前6位是代表省、市、区的代号比如（340505等），如果我们的工作人员是同一个地方的，我们就可以简化输入过程，在excel中快速输入身份证号码，具体操作用一个通讯费用报销工作表来讲解。

1、在excel工作表中选择需要输入身份证号码的单元格区域 C5:C54。切换到【开始】选项卡，在【单元格】组中单击【格式】下拉按钮，执行【单元格】命令。

2、弹出【设置单元格格式】对话框，单击【数字】标签，在【分类】列表框中选择【自定义】选项。

3、在 【类型】 文本框中输入"152103"@， 其中数字部分是身份证号码的前几位。双引号在英文状态下输入。

图1

4、单击【确定】按钮。

5、在单元格中输入号码剩余的数字。

图2

6、按下 Enter 键， 发现单元格 C5 中的身份证号码的前几位152103已经自动添加到前面。

图3

通过上面的方法，我们就可以在excel中快速输入身份证号码了，这里我可以大大的提高工作效率，当然此项技巧我们也可以运用到很多工作领域。

Excel表格中根据身份证号码自动填出生日期、性别、年龄

右击所在列→“设置单元格格式”为“常规” →复制以下（蓝色）公式：

=================================================================

◎ A1为15位或18位身份证号，要在B1算出 生日

=TEXT(19*(LEN(A1)=15)&M")

◎ A1为15位或18位身份证号，要在B1算出 性别

=IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2),"男","女")

◎ A1为15位或18位身份证号，要在B1算出 截止到“今天”的 年龄

=DATEDIF(TEXT((LEN(A1)=15)*19&M"),TODAY(),"Y")

注意：根据表格修改单元格“(A1)”下图已经修改为“(B1)”。修改方式：将上述公式打入后，抹黑上述公式中的“A1”（不要抹括号）,再点“B1”单元格，公式中就自动生成“B1”。然后下拉“C1”，以下所有行就全部自动完成了。

---另：

根据身份证号码求出生日期、性别、年龄、15-18位转换

复制以下蓝色代码：

根据身份证计算年龄

当需要在单元格内输入18位身份证号码时，往往会出现数字太长，在单元格内变成科学记数法的现象，并且身份证末尾3位也会变成000的情况，就像下图中所示。

下面有两个比较简单的Excel技巧可以来解决这个问题，使身份证号码能完全在单元格内显示。

方法一：设置单元格格式

这里需要在输入身份证号码之前设置好单元格格式。

*选中目标单元格。

*使用快捷键Ctrl+1调出“设置单元格格式”对话框。

*在分类里面选择文本。

然后就可以输入身份证号码了，显示出来的就是完成的数字，并且左上角有一个绿色的箭头，代表单元格格式目前是以文本形式存储的数字。

但是在编辑栏中我们会发现，号码之前会自动有一个单引号的，这就是下面的第二个方法。

方法二：英文状态下输入单引号

这里是在输入身份证号码前，输入法为英文状态，输入英文的单引号“’”，然后再输入身份证号码。

这个方法也能实现完全显示身份证号码，并且前面有一个绿色标志，内容的最前面有一个单引号。两个方法不同的是，第一种的单元格格式为“文本”，而第二个则还是“常规”。

初入职场的办公小白，经常会遇到这样一个让人抓狂的问题：在Excel中录入员工信息等很多类似的表格时，常会输入诸如，身份证号、银行卡号这样较长的数字。但像输入身份证号一般都是15位或者18位数字，如果我们直接在Excel中输入的话，就会出现下图的情况。

这是神马情况呢？

（1）为什么变成5.13021E+17了？（2）为什么编辑栏中最后3位数都变成0了？

原因一：Excel中，当单元格输入的数字位数超过11位时，Excel就会自动以科学记数形式显示。其默认的数字格式是“常规”，也就是说，单元格最多可以显示11位有效的数字。

原因二：Excel处理数字的精度为最大为15位，当单元格中输入的数字超过16位的部分将被自动以0进行补位（即变成0）。

因此，当输入身份证号、银行卡号这样较长的数据时，Excel默认以数字来存储，就会出现上述错误。

那么，遇到这种情况怎么解决呢？如何输入才能得到我们需要的身份证号码呢？今天给大家介绍几种比较简单快速的方法。

先输入一个英文单引号’再输入数字

在输入身份证号码时，在号码前面加上一个英文状态下的单引号“'”，其作用就是将该单元格属性设置为“文本”。即可让身份证号码完全显示出来，（该单引号在确认后是不会显示出来的），如下图所示。

将单元格格式设置为文本

技巧一：选中要输入身份证号的单元格，在“开始”/“数字”组的“常规”下拉格式列表，选择最下方的“文本”选项，即可在单元格内输入身份证号。

技巧二：选中要输入身份证号的单元格，右击鼠标，选择“设置单元格格式”；在“数字”选项卡的“分类”列表中选择“文本”选项。单击“确定”按钮。再输入身份证号码即可。（一定要设置好格式，再输入号码）；

注意：如果不想要单元格左上角的小箭头，可以单击小箭头，选择“忽略错误”。

自定义@格式

选中要输入身份证号的单元格，右击鼠标，选择“设置单元格格式”；在“数字”选项卡的“分类”列表中选择“自定义”选项。在“类型”文本框中输入“@”，单击“确定”按钮，再输入身份证号码即可。

技巧提示：

我们都知道，身份证号码理论上是有一定的规律，如前6位数，是省、市、区(县)的代号(如达州是“513021”等)，第7~14位数字表示：出生年、月、日。如果输入的信息多为同一地区的，怎样输入提高效率呢？

其实可以通过自定义单元格格式来实现：打开“单元格格”对话框，选择“数字”/“自定义”选项，在右侧“类型”文本框中输入："513021"@或”51302119”@，单击“确定”按钮。

这时，只需在单元格中输入身份证号码后续的数字，系统会自动在前面加上相应的513021或51302119，形成完整的身份证号码。

利用等于+双引号输入

在需要输入身份证号码的单元格中首先输入 =””（在英文状态下），接着在双引号中间位置输入身份证号码，这样才能正常地显示身份证号码。

知识扩展：身份证号的长度验证

通常，身份证号码固定为18位，而在输入身份证号码的时候，太多数字串看起来很费劲，难免出现错误。这时候我们可以用“数字有效性”来限制数字个数必须是多少位，当多余或者少于限定个数时即提醒输入有误。

方法为：选择要输入身份证号码的单元格，选择“数据”/“数据工具”组，单击“数据有效性”按钮。打开“数据有效性”对话框，在“允许”下拉列表中选择“文本长度”选项，“数据”选择“等于”，“长度”为“18”，单击“确定”按钮即可。

End.

来自：excel教程

职场中的精英们，相信大家在使用EXCEL的时候，您肯定曾经对处理身份证号信息头疼不已，本节以EXCEL2010为准，将从以下五个方面对身份证号在EXCEL中的处理方式进行详细讲解。

一、身份证号的输入

方式一：单元格输入身份证号前，将单元格格式设置为“文本”格式（Ctrl+1，可快捷调出单元格格式设置页面）后，然后再输入身份证号。

方式二：在单元格输入身份证号时候，首先输入英文的单引号“'”，然后再输入身份证号，神奇的事情发生了，引号消失不见，身份证号自动变为文本。

为什么输入要这样啰嗦呢，因为Excel单元格默认情况下为“常规”格式，该状态会自动按照自己的喜好自动将内容进行归类，它会将输入的身份证号视同为“数字”格式，所以当单元格长度不够的情况下，您输入一串身份证号后会变成科学计数法；即使当前长度足以显示身份证号，您将该单元格宽度变小，也会变成科学计数法，再加长，会发现份证号后几位会丢失变成0。

二、身份证号信息的输出

假设在A1单元格存储了身份证号：370983201705040067

（一）通过身份证号采集出生日期

mid函数语法：

mid(text,start_num,num_chars)，中间某位置开始截取部分字符串；其中text表示要截取的字符串，start_num表示从第几位字符串开始截取，num_chars表示要截取的字符数。

1、如果显示格式为：20170504，那么我们应该在单元格中输入函数：=mid(A1,7,8)，就可以得到20170504；不过此时显示的内容为文本状态，并不是日期格式，需要通过其他方法再转化为日期。

2、如果显示格式为：2017年05月04日，那么我们应该在单元格中输入函数：

=mid(A1,7,4)&"年"&mid(A1,11,2)&"月"&mid(A1,13,2)&"日"

不过此时显示仍然为文本状态，并不是日期格式，需要通过其他方法进行转化。

该函数的意思是：从A1单元格中的第7位开始选，往后数4位，返回年2017；从A1单元格中的第11位开始选，往后数2位，返回月05；从A1单元格中的第13位开始选，往后数2位，返回日04；同时通过连接字符串连接“年”“月”“日”，即得到2017年05月04日。

知识补充：

“&”(输入方式：shift键+大键盘上的7)，相当于数学运算附中的+，“&”可以将两个单元格的内容，或者字符串拼接在一起，例如假设A1单元格为：张，A2单元格为：三，我们在A3中输入：=A1&A2，则A3中会显示：张三。

3、前面两种方式显示都为文本格式，假设有人想一步到位，直接将身份证中的日期采集成日期格式，如2017-5-4，那么我们应该在单元格中输入函数：

=DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2))

此时直接为日期格式（可以通过设置单元格中日期的格式，变为2017年5月4日或者2017/5/4等等）。

函数的意思是：从A1单元格中的第7位开始选，往后数4位，返回年2017；从A1单元格中的第11位开始选，往后数2位，返回月05；从A1单元格中的第13位开始选，往后数2位，返回日04；然后通过DATE函数将返回的字符串转化为日期格式。

（二）通过身份证号采集性别

mod函数语法：

mod(number,pisor), 用于返回两数相除的余数；其中number：表示被除数，pisor表示除数。如果该参数为 0，MOD 函数将返回错误值 #p/0!。

我们通过mod函数与mid函数组合，在单元格中输入函数：

=IF(MOD(MID(A1,17,1),2)=1,"男","女")，即可得到性别。

函数的意思是：首先从A1单元格中的第17位开始选，往后数1位,得到1个数字，然后用这个数字除以2，得到余数；假设余数为1，则显示“男”；假设余数不为1，则显示“女”。

知识补充：18位的身份证号的第17位如果是奇数，代表性别是男，如果是偶数，代表性别是女。

（三）通过身份证号采集年龄

-----------------------------

NOW函数语法：now()，用于返回当前日期。

YEAR函数语法：

YEAR（serial_number），用于返回日期中的年份；其中serial_number表示一个日期值。

DATEDIF函数语法：

DATEDIF(start_date,end_date,unit)，两个日期之间做差额；其中：

Start_date 为一个日期，它代表时间段内的第一个日期或起始日期；

End_date 为一个日期，它代表时间段内的最后一个日期或结束日期；

Unit 为所需信息的返回类型。

Unit 返回

注：结束日期必须大于起始日期

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"YM" 起始日期与结束日期的间隔月数，忽略日期中年份。

1、YEAR、NOW和MID函数组合，计算年数差额即年龄，函数如下：

=YEAR(NOW())-MID(A1,7,4)，显示为**年。

2、DATEDIF、NOW和MID函数组合，计算年数差额即年龄，函数如下

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y")，显示为**年。

函数的意思是：首先通过DATE函数将从身份证中采集的字符串转化为日期格式；通过NOW函数调取当前日期；然后通过DATEDIF函数将两个日期以“年”为单位做差额。

3、补充：

（1）月数差额：计算当前日期与身份证日期的月数差额，函数如下：

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"m")，显示为**月。

（2）天数差额：计算当前日期与身份证日期的天数差额，函数如下：

=DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"d")，显示为**天。

（3）年月差额：计算当前日期与身份证日期的年月差额，函数如下：

=CONCATENATE（DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y"),"年",DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"ym"),"个月"），显示为**年**个月。

（四）通过函数检验身份证号码的正确性

我们有的时候手动输入的身份证号会不小心发生错误，我们可以通过函数来自动判定是否自己输错了。

原理：18位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。

函数表达式如下：

=MID("10X98765432",MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(A1,18,1)，显示结果为TRUE或者FALSE，如果身份证号符合规则显示TRUE，如果身份证号不符合规则显示FALSE。

（五）身份证号18位与15位的转化

一代身份证是15位（个别也有18位的），二代身份证为18位，工作中难免有时候会发生需要通过一个人15位的老身份证号推算18位身份证号，也可能需要通过一个人的18位身份证号来推算15位身份证号的情况。

1、身份证号15位升级为18位

20世纪的人：

=IF(LEN(A1)=15,REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)

21世纪的人：

=IF(LEN(A1)=15,REPLACE(A1,7,,20)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,20),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)

2、身份证号18位降级为15位

=IF(LEN(A1)=18,LEFT(REPLACE(A1,7,2,),15),A1)

相信看到本节内容的读者们肯定有眼前一亮的感觉，估计大家都会收藏或者转发，不过提醒大家，如果提高EXCEL水平，最好是自己实际操作一遍，增强记忆哦。

如果我们直接在Excel表格中输入身份证号，默认显示的是科学计数法表达方式，没有办法看到身份证号码原来的格式。比如下表：

第一招：设置单元格式：数字作为文本

1、那如何可以在输入身份证号码后，保持原来的格式呢？先教大家一招，这里我们需要先进行单元格的设置。我们选中单元格

2、鼠标右键-设置单元格格式

3、弹出的单元格格式对话框，数字选项卡页面的分类选择 文本 ，然后确定

4、当我们再次在刚才设置的单元格下输入身份证号码后，还保持了原来的18位格式

第二招：身份证号码前加一个英文的分号'

1、输入身份证号码我们还有另外一招也能实现，方法是：在输入身份证号码之前，输入一个英文的分号’（该符号在键盘回车键Enter键前面），然后在分号’的后面输入身份证号码，结果如下：

2、当我们回车后，身份证号码就保持了原来的格式

本百家号：时代新生分享工作、生活、技能方面的经验，愿和您一起成长。请多多关注哦！