EXCEL核心课件-截取和截除.docVIP

EXCEL核心课件：截取和截除 一、截取的基本方法 　　MID函数应用 　　格式：MID 单元格,起始位置,截取位数 从大字段中截取指定N个字符。如截取出生年月。 　　例：身份证号码在A1单元格：座机电话号码1223591 　　具体应用为： 　　在B1单元格中输入： MID A1,7,6 注意, 　　A1是数据所在单元格 　　7是出生年月的起始位置 　　6是从第6位到出生年月结束 二、截取证件号的出生年月,检验一列数据的字符数 　　先对一列数据排序，确定对哪几位排序，再进行截取，如身份证号码号。 1、LEN： 长度LENGTH 检查单元格里的字符数，如： 　　A1 A2 　　EXCEL 5 　　A2单元格的公式为：LEN（A1） 　　A2单元格中的“5”表示A1单元格中的字符有5位 2、MID：提取字符串里的字符 　　A1 A2 　　EXCEL CEL 　　A2单元格的公式为：MID（A1，3，3） 　　表示从A1单元格中的第三个字符开始连续截取3个字符 　　3、IF（LEN（A1） 5） 　　检查A1单元格中的字符是否是5个字符 比如某人的身份证号码是： 　　A1 A2 　　xxxxxx座机电话号码xx， 7909 要提取其中的出生年月，如A2中所示（有的身份证是18位数字），则公式设置应为： 　　A2 IF（LEN（A1） 15，MID（A1，7，4），MID（A1，9，4））意为：如果A1单元格中的数字是15位，那么就从第7位开始连续截取4位数，如果不是15位，那么从第9位开始连续截取4位数。 三、截取数据的前N位、中N位、后N位字符 　　截取出某个单元格数据前N位、中间N位、后N位，如要取出身份证中的N个字符，在EXCEL里用LEFT、MID、RIGHT函数分别取出前N位、中N位及后N位的方法。 1、LEFT函数用法：LEFT 取值的数值,取值位数 设在A1单元格有一字符串，如01座机电话号码，要截取出前4位字符，方法如下： 　　点击B1单元格（设把结果返回到B1单元格）,函数栏中输入公式： LEFT A1,4 2、RIGHT函数用法：RIGHT 取值的数值,取值位数 这里依然用上面的例子，假设要取出右边4个数字，方法如下： 首先点击B1单元格（这里假设把显示结果放在B1单元格中），在函数栏中输入如下公式即可： RIGHT A1,4 3、MID函数用法：MID 取值的数值,取开始位置序号,取值位数 这里依然用上面的例子，假设要取出“4567”4个数字，方法如下： MID A1,5,4 四、截除前缀的方法 　　EXCEL中截除186xxxxxxxx前的86或者12593，以及类似的编码86186xxxxxxxx，+86186xxxxxxxx，1座机电话号码xxxxxxxx,1座机电话号码xxxxxxxx等编码的前缀有两种方法，公式如下： IF LEN E6 11,RIGHT E6,11 ,E6 IF E6 座机电话号码000，RIGHT E6,11 ,E6 注意，前者使用IF函数和LEN函数相结合 　　后者是使用了IF函数 　　缺点是座机电话号码999999的也被做了同样处理 五、截除后缀的方法 　　如何把HS1XXXXXXXXXXM末几位用EXCEL公式截除 HS1座机电话号码001L 　　HS1座机电话号码001M 　　HS1座机电话号码001XL 　　HS1座机电话号码001XXL 　　HS1座机电话号码100L 　　HS1座机电话号码100M 　　排序之后,输入以下公式: 设数据在A列，则在B1输入公式 LEFT A1,LEN A1 -3 B1公式向下复制 　　公式中的3指要截除的右侧字符数