原创:卢子1987Excel不加班 我们经常会看见这样的长字符串,从别的地方导入或者因没有设置单元格为文本格式,显示E+17,长字符串超过15位的数字全部变成0。遇到这种情况,是没法重新恢复的! 记住,不管是输入还是导入,必须在操作之前设置为文本格式。下面,卢子跟你先聊聊长字符串的一些相关知识点。 1.将账号的空格去除。 这种长字符串不能采用直接替换的功能,否则会变成E+17,不过可以借助替换函数SUBSTITUTE进行转换。参数2里面有一个空格哦,别以为参数2、3是一样的。 =SUBSTITUTE(A2," ","") 2.将账号前面多余的"去除。 正常情况下,输入一个"就代表文本格式,现在有两个,需要去除一个。复制粘贴标准账号,借助快速填充(Ctrl+E)功能实现转换,Excel2013特有功能。 3.只允许录入11位的字符串。 我们的手机号统一11位,为了防止录入错误,进行相应的设置,效果如动图,多1位或者少1位都会提示出错。 借助数据验证(低版本叫数据有效性),自定义公式实现。 4.长字符串用COUNTIF函数统计每个身份证号的累计次数出错时的解决方法。 第1个身份证号跟第2个明显不同,但却被误认为一样。 解决方法1:A2&"*",这样相当于强制转换成文本格式。 =COUNTIF($A$2:A2,A2&"*") 解决方法2: =SUMPRODUCT(($A$2:A2=A2)*1) 接着详细说明身份证获取各种详细信息。 5.性别:第17位为奇数是男,偶数是女。 =IF(MOD(MID(B2,17,1),2),"男","女") 6.出生日期:7~10位为出生年份,11~12位为出生月份,13~14位为出生日。 =TEXT(MID(B2,7,8),"0-00-00") 7.周岁: =DATEDIF(D2,TODAY(),"y") 8.年龄: =DATEDIF(D2,TODAY(),"y")&"岁"&DATEDIF(D2,TODAY(),"ym")&"个月" 第3、4的TODAY函数也可以改为NOW函数,效果一样。 字符提取,左边用LEFT函数,中间用MID函数,右边用RIGHT函数。