问题来源

输入身份证号码是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，并且单元格不是空时，填充某种颜色。

新来一同事，个人信息录入员工档案。当我输入身份证号码后，嫉妒的发现，他竟然是......雄县的。

在每个人的身份证号码中，不仅有出生年月、性别信息，还有个人籍贯信息。

1~2 两位是省、直辖市、自治区信息3~6位是市县信息7~14位是出生日期15~16位是当地派出所代码17位是性别18位是身份证校验码

如：13 2434 19860303 38 1 5中

13代表河北省132434 是保定地区 -雄县。19860303 ：生日38：当地派出所代码1 ：奇数男，偶数女5：身份证校验码

所以，只要有一个代码表（本文最后提供下载），就可以用 Left + Vlookup组合从身份证中提取籍贯信息。

取值公式：

=IF(B2="","",VLOOKUP(LEFT(B2,2),地址码!A:B,2,0)&VLOOKUP(LEFT(B2,6),地址码!A:B,2,0))

附身份证相关公式：

提取性别公式：

=IF(B2="","",IF(MOD(MID(B2,17,1),2),"男","女"))

提取出生日期公式：

=TEXT(MID(B2,7,8),"0-00-00")

计算年龄公式：

=IF(B2="","",YEAR(TODAY())-MID(B2,7,4))提取籍贯的公式并不复杂，本文有两个用意：一是普及身份证中个人信息知识，告诉大家身份证中可以提取籍贯的。二是这个地址代码表你需要用时在网上不好找，这里提供给同学们。

新从事财务人员和人力资源工作的朋友一开始可能都会遇到同样一个问题，在电子表格Excel中输入超过12位的数值后，电子表格本身系统会自动将其转换为科学记数格式，输入的数值超过15位的时候，16-18位数值会自动转换为0，今天就说一下这个办公室文职工作的基础知识，希望对大家有所帮助。

例1：

相信这是大家在初从事办公室文职工作时都会遇到的问题，其实很容易解决，下面就给大家介绍两种。

第一种方法：选中数值所在单元格，右键-设置单元格，将单元格格式选为（文本）格式，单击确定，这样身份证号码就可以正常显示了。

第二种方法：在输入数值之前，首先在单元格第一位输入文本符号（'）。之后正常输入身份证号按下回车，身份证号可正常显示。

以上介绍的两种在电子表格excel输入身份证号原理其实非常简单，就是改变单元格的属性为文本类型。希望对大家的工作有所帮助。不知道大家有没有更多更好的方法，欢迎大家踊跃留言！相互学习

EXCEL日常工作中经常会输入身份证号码，一不小心就可能出错，今天大飞老师就分享这样一个示例EXCEL规范数据输入教程-验证身份证号。

我们首先要保证在EXCEL中能正确输入身份证号码（PS：身份证号18位，直接输入就会变成科学计数法形式1.235E+17），可以通过两种方式。方式一：单引号+身份证号码如：’123456789111111111，方式二：先改变单元格类型为文本格式，然后再输入身份证号码。

我们要学习一个EXCEL函数LEN,可以返回字符的个数，我们填写一个身份证号码，通过LEN可以获取一下个数，如下图所示

这样我们会得到一个结果18，其实现在同我们想要的结果，还有差距，在编辑栏上=LEN(J4)=18，输入等于18，最后会返回一个结果TRUE。

下边我们就可以通过EXCEL的数据有效性完成其它操作了。选择K列，通过数据有效性（数据验证）我们把允许改成自定义，下边公式填成=LEN(K1)=18，如下图所示

给大家说一下，这个公式没有添加停止符，这是表示K列都需要使用公式验证。（PS:在验证之前先把标题输入了）

完善一下输入信息，给用户一个提示，当在选择K列时，就会出现如下图所示的提示信息了。

来源：http://huixue8/a/Exceljichu/160.html

文/小雨

图/网络

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

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

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

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

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

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

第一种解决方法：

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

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

具体设置方法：

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

第二种解决方法：

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

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

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

在Excel中，输入超过12位的数值，系统自动将其转换为科学记数格式，

如果输入超过15位的数值，系统自动将15位以后的数值转换为“0”。

为了能让这些长位数数值完整显示出来，就必须用下面两种方法，

将单元格转换成文本格式，然后再输入。 

方法一：在输入这些长数值时，先输入一个英文状态下的单引号（“'”回车前面那个），

然后接头输入数值即可。 

这个方法有利有弊，不显示单引号，

但是不显示不代表不存在，

上图中A1='123456789

B1=123456789

C1中的函数是=A1=B1，返回FALSE

那么在进行VLOOKUP等函数的时候会造成很大的麻烦，

建议在不用进行数据处理的情况下使用。方法二：选中相应的单元格区域，单元格格式改为文本。

注意：先改单元格格式，再输入身份证号。

这种情况下可以进行编辑，但也有弊端，就是在经常更新，多人操作的时候，有可能发生因粘贴，误操作等把这列数据的格式改成常规，甚至后三位变0无法修复的情况。

（本文内容由百度知道网友环保氰化钾贡献）

初入职场的办公小白，经常会遇到这样一个让人抓狂的问题：在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教程

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

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

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

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

图1

4、单击【确定】按钮。

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

图2

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

图3

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

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

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

方法一：设置单元格格式

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

*选中目标单元格。

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

*在分类里面选择文本。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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