第5章 文本处理函数.xls

日日期期水水果果名名称称价价格格标标注注 IF B1 B2 B2 IF B1 B2 B2 2007 5 9桔子3 10桔子桔子 IF EXACT B1 B2 B2 2007 5 10桔子3 20 IF EXACT B2 B3 B3 2007 5 11桔子3 00 IF EXACT B3 B4 B4 2007 5 1苹果2 50苹果苹果 IF EXACT B4 B5 B5 2007 5 4苹果2 80 IF EXACT B5 B6 B6 2007 5 5苹果2 60 IF EXACT B6 B7 B7 2007 5 3葡萄2 70葡萄葡萄 IF EXACT B7 B8 B8 2007 5 11葡萄3 30 IF EXACT B8 B9 B9 2007 5 12葡萄2 90 IF EXACT B9 B10 B10 2007 5 7香蕉2 90香蕉香蕉 IF EXACT B10 B11 B11 2007 5 8香蕉3 00 IF EXACT B11 B12 B12 2007 5 2雪梨2 60雪梨雪梨 IF EXACT B12 B13 B13 2007 5 8雪梨3 00 IF EXACT B13 B14 B14 2007 5 3杮子3 00杮子杮子 IF EXACT B14 B15 B15 2007 5 11杮子2 90 IF EXACT B15 B16 B16 2007 5 12杮子3 10 IF EXACT B16 B17 B17 ApplicationApplication Application IF EXACT B17 B18 B18 applicationapplication IF EXACT B18 B19 B19 FALSE EXACT C18 C19 TRUE C18 C19 标识首次出现的记录 要求在 标注 列标出首次出现的水果名称 其他的单元格显示为空 EXACT 比较两个字符串是否完全相同 区分大小写 如果它们完全相同则返回TURE 否则返回FALSE EXACT 比较两个字符串是否完全相同 区分大小写 如果它们完全相同则返回TURE 否则返回FALSE AA大小写字母转换 BBLOWER 函数 将一个文本字符串中的所有大写字母转换为小写字母 对于文本中的非字母的字符不做改变 CCi love excelhome LOWER I love excelhome DDUPPER 函数 将文本字符串中的所有小写字母转换为大写字母 对于文本中的非字母的字符不做改变 EEI LOVE EXCELHOME I LOVE EXCELHOME FFPROPER 函数 将文本字符串的首字母及任何非字母字符之后的首字母转换成大写 将其余的字母转换成小写 GGI Love Excelhome PROPER I love excelhome HH苹果Apple香蕉Banana PROPER 苹果APPLE香蕉banana II JJ KK全角半角字符转换 LLWIDECHAR 函数 在简体中文环境下 将字符串中的半角 单字节 字母转换为全角 双字节 字符 MM WIDECHAR Excelhome NNASC 函数 与WIDECHAR函数相反 是将全角 双字节 字符转换为半角 单字节 字符 OO我爱Excel ASC 我爱 PP QQ RR生成常见的A Z SSS SUBSTITUTE ADDRESS 1 ROW 4 1 TTT CHAR ROW 64 UU VV WW生成可换行的文本 XX中中文文英英文文合合并并 YY苹果Apple 苹果 Apple D25 女 把身份证号码从第15开始取3位数字 如果身份证号码为18位 则取第15 17位 如在B3单元格中取出 007 如果身份证号码为15位 则取出第15位 如在B2单元格中取出 4 再用这个数除以2 如果余数为正数结果显示为 男 为负数显示为空 为0则显示为 女 思路解析 IF MOD MID B2 15 3 2 男 女 或者 IF MOD MID B2 15 3 2 1 男 女 在IF函数中如果判断条件结果为数值 非0数值则为TRUE 结果返回第2个参数的值 若数值为0即为FALSE 结果返回第3个参数的值 思路解析 TEXT LEN B2 15 19 MID B2 7 6 LEN B2 18 2 0 00 00 LEN B2 15 19 如果身份证号码为15位 则取19 否则为0 MID B2 7 6 LEN B2 18 2 从身份证码的第7位开始取值 如果号码为18位则取6 2位数 否则取6 0位数 把两段数字连接起来 如B2取出 19650815 B3取出 019781012 使用TEXT函数把这个字符串设置成 0 00 00 的数字格式 B2和B3结果分别为 1965 08 15 和 1978 10 12 最后使用不影响原值的 运算 减负运算 即可把以文本字符串表示的日期转换为真正的日期序列号 只要把单元格数字格式设置为日期 即可以单元格中显示为日期格式的日期 思路解析 DATEDIF D2 TODAY y 该公式计算从D2单元格的日期到系统日期之间所间隔的完整年份数 即计算周岁 把身份证号码从第15开始取3位数字 如果身份证号码为18位 则取第15 17位 如在B3单元格中取出 007 思路解析 IF MOD MID B2 15 3 2 男 女 或者 IF MOD MID B2 15 3 2 1 男 女 MID B2 7 6 LEN B2 18 2 从身份证码的第7位开始取值 如果号码为18位则取6 2位数 否则取6 0位数 使用TEXT函数把这个字符串设置成 0 00 00 的数字格式 B2和B3结果分别为 1965 08 15 和 1978 10 12 最后使用不影响原值的 运算 减负运算 即可把以文本字符串表示的日期转换为真正的日期序列号 ExcelhomeExcelhome模模拟拟发发票票 地址 2007年10月31日填发 品品名名规规格格单单价价数数量量价价值值 金金额额 十十 万万 千千 百百 十十 元元 角角 分分 WORD非常接触52 4381 991 20 199120 Excel实战技巧精粹58 65432 521 95 252195 自填23 46517398 91 39891 合计金额 大 写 零拾零万肆仟玖佰壹拾贰元零角陆分 4 912 06 491206 填制人 kevin经办人 gvntw业户名称 盖章 思路解析 IF F5 LEFT RIGHT F5 100 COLUMNS G N LEFT 从一个文本字符串的第一个字符开始返回指定个数的字符 Text 要提取字符的字符串 Num chars 要LEFT提取的字符数 如果忽略 为1 RIGHT 从一个文本字符串的最后一个字符开始返回指定个数的字符 Text 要提取字符的字符串 Num chars 要提取的字符数 如果忽略 为1 COLUMNS 返回某一引用或数组的列数 Array 要计算列数的数组 数组公式或是对单元格区域的引用 此公式利用把金额乘以100 把数字扩大100倍以去掉小数点 使用LEFT和RIGHT函数 以及在 COLUMNS G N 中对G列的相对引用和对N列的绝对引用 使公式向右复制后得到不同的结果 分别截取字符串的某个字符 并巧妙地利用人民币符号 前面的一个空格 以达到把还不必填入数字的单元格置空 注意 公式中人民币符号 的前面有一个空格 同时为了避免出现多余的小数 目前已经在F5 F10中的计算公式中使用ROUND函数进行了四余五入 ROUND D5 E5 2 使用LEFT和RIGHT函数 以及在 COLUMNS G N 中对G列的相对引用和对N列的绝对引用 使公式向右复制后得到不同的结果 分别截取字符串的某个字符 并巧妙地利用人民币符号 前面的一个空格 以达到把还不必填入数字的单元格置空 待查找字符串查查找的字符串FIND函数查找结果SEARCH函数查找结果 Applea VALUE 1 Applicationa71 Application第2位为c c VALUE 5 FIND和SEARCH的异同 FIND B2 A2 SEARCH B2 A2 FIND 区分大小写 不支持通配符 FIND B3 A3 SEARCH B3 A3 SEARCH 不区分大小写 支持通配符 FIND c A4 SEARCH c A4 序序号号姓姓名名部部门门联联系系电电话话提提取取电电话话号号码码 2007 5 3张三销售手机 1370123456713701234567 2007 5 4李四产品固定电话0756333123407563331234 2007 5 7王五人事手机 1598765432115987654321 2007 5 8赵六综合小灵通0102345678901023456789 2007 5 9刘三生产手机 1311234567813112345678 巧用模糊查找分离数据 思路解析 MIDB D11 SEARCHB D11 LEN D11 此公式利用 SEARCHB 函数配合通配符 查找字符串中第1个半角字符的位置编号 再用MIDB函数取出电话号码 日日期期产产品品名名称称销销售售数数量量前前字字符符串串后后字字符符串串 2007 5 10Seagate硬盘20Seagate硬盘 2007 5 11HPLaserjet打印机35HPLaserjet打印机 2007 5 14Dell电脑92Dell电脑 2007 5 15Diamond硬盘17Diamond硬盘 分离全角和半角字符 前半角字符串 LEFT B22 LEN B22 2 LENB B22 后全角字符串 RIGHT B22 LENB B22 LEN B22 利用 SEARCH 函数可以提取由连续的 全角 半角 组成文本字符串中的 半角字符串 但如果文本数据是由连续的 半角 全角 组成 还可以利用 LENB 函数和 LEN 函数相减的技巧来解决 这两个公式主要是利用 LENB 和 LEN 处理全角字符的原理 即 LENB 全角字符 2 LEN 全角字符 通过两者相减即可确定连续的全角字符个数 此公式利用 SEARCHB 函数配合通配符 查找字符串中第1个半角字符的位置编号 再用MIDB函数取出电话号码 但如果文本数据是由连续的 半角 全角 组成 还可以利用 LENB 函数和 LEN 函数相减的技巧来解决 这两个公式主要是利用 LENB 和 LEN 处理全角字符的原理 即 LENB 全角字符 2 LEN 全角字符 期期号号开开奖奖号号预预测测号号统统计计不不重重复复数数字字 2007015916168905 2007016441785067 2007017370101374 2007018829127386 2007019592163547 2007020990149705 在预测开奖号码表中 使用下面的公式可以在D2 D7统计B2 B7的开奖号码与C2 C7预测号连起来共有几个不重复的数字 COUNT FIND 0 1 2 3 4 5 6 7 8 9 B2 C2 COUNT FIND 0 1 2 3 4 5 6 7 8 9 91616890 COUNT 8 2 VALUE VALUE VALUE VALUE 3 VALUE 6 1 公式利用 FIND 函数查找每个数字在B2 C2字符串的位置编号 如果字符串包含有某个数字 则返回数字在字符串中的位置编号 否则返回错误值 VALUE 因为COUNT函数只统计参数中数字的个数 而忽略文本值 逻辑值及错误值 因此最后用COUNT函数统计出最终的数字个数 在预测开奖号码表中 使用下面的公式可以在D2 D7统计B2 B7的开奖号码与C2 C7预测号连起来共有几个不重复的数字 因为COUNT函数只统计参数中数字的个数 而忽略文本值 逻辑值及错误值 因此最后用COUNT函数统计出最终的数字个数 实实例例MID SUBSTITUTEMID SUBSTITUTEREPLACE SUBSTITUTEREPLACE SUBSTITUTE Microsoft Office Word 2003Word 2003Word 2003 Microsoft Office Excel 2003Excel 2003Excel 2003 MS Office PowerPoint 2003PowerPoint 2003PowerPoint 2003 MS Office Access 2003Access 2003Access 2003 MS Office InfoPath 2003InfoPath 2003InfoPath 2003 Microsoft Office Outlook 2003Outlook 2003Outlook 2003 MS Office Publisher 2003Publisher 2003Publisher 2003 MID 和 SUBSTITUTE 函数 MID A2 FIND SUBSTITUTE A2 2 1 LEN A2 REPLACE 和 SUBSTITUTE 函数 REPLACE A2 1 FIND SUBSTITUTE A2 2 上面的两个公式有一个共同点 即都使用 FIND SUBSTITUTE A2 2 来定位第2个空格的位置编号 SUBSTITUTE A2 2 把字符串中第2个空格字符替换为文本串中没有的字符 然后再查找 的位置编号 就是第2个空格的位置编号 第1个公式使用MID函数来截取字符串 即从第2个空格位置编号 1 位开始截取 长度为字符串长度 从而取得后面的所有字符串 第2个公式使用REPLACE函数 把字符串从第1位开始到第2个空格为止的字符串替换为空文本 只留下了第2个空格后面的所有文本 如果不能确定字符串中是否包含 全角空格 还可以利用ASC函数将所有的全角空格全部转换为半角空格 如修改第2个公式如下 REPLACE A2 1 FIND SUBSTITUTE ASC A2 2 上面的两个公式有一个共同点 即都使用 FIND SUBSTITUTE A2 2 来定位第2个空格的位置编号 第1个公式使用MID函数来截取字符串 即从第2个空格位置编号 1 位开始截取 长度为字符串长度 从而取得后面的所有字符串 第2个公式使用REPLACE函数 把字符串从第1位开始到第2个空格为止的字符串替换为空文本 只留下了第2个空格后面的所有文本 如果不能确定字符串中是否包含 全角空格 还可以利用ASC函数将所有的全角空格全部转换为半角空格 如修改第2个公式如下 职职称称统统计计表表 职职称称姓姓名名人人数数 高级经济师 赵健坚 褚虹玉 杨生华 李红玉 孙展忠 李真丹 孙强仁 孔霞香 8 经济师 郑生军 赵娟丹 孔志辉 杨芳巧 卫健仁 卫虹惠 沈裕坚 杨芳楠 沈文宏 周彩红 10 助理经济师 冯展生 严红楠 沈刚刚 魏丹雯 褚展健 蒋香娟 赵毅强 周彩彩 褚强国 李彩筱 10 高级会计师 褚发艺 钱霞芳 蒋毅德 王芳弟4 会计师 韩仁发 陈楠筱 褚军宏 孙芬云 周展强 陈霞楠 周艺文 沈芳真 8 助理会计师 周炎裕 陈妙梅 褚毅生 孔妙彩 吴艺裕 严红芬6 工程师沈智忠 孙宏毅 冯仁健 魏仁生4 助理工程师 郑惠彩 孔虹华 杨君花3 思路解析 LEN C3 LEN SUBSTITUTE C3 1 由于各姓名间都是以 号分隔 因此只要求出单元格内的 号个数N就可以统计出人数 即N 1人 公式通过 SUBSTITUTE 函数把单元格中所有的 替换为空 再求替换后的字符串字符数 然后用未替换前的字符串的字符数减去替换后的字符数 得到 的个数 最后加1即为该项职称人员数 注意 如果希望统计包含两个及两个以上字符的字符串个数 可以使用以下的通用公式进行处理 LEN 源文本 LEN SUBSTITUTE 源文本 查找字符串 LEN 查找字符串 由于在本技巧中查找单个字符其长度LEN 查找字符串 为1 所以可以省略不写 由于各姓名间都是以 号分隔 因此只要求出单元格内的 号个数N就可以统计出人数 即N 1人 公式通过 SUBSTITUTE 函数把单元格中所有的 替换为空 再求替换后的字符串字符数 注意 如果希望统计包含两个及两个以上字符的字符串个数 可以使用以下的通用公式进行处理 姓姓名名1515位位身身份份证证号号码码验验证证码码1818位位身身份份证证号号码码 欧阳芬1102216508152247110221196508152247 李可法3505837810120072350583197810120072 朱菊5118015209255201511801195209255201 周桂香1102212908152248110221192908152248 西门雄110101700626551张美女6201237905131505620123197905131505 张娟3502214908152241350221194908152241 令孤强1101016006065513110101196006065513 张生3502217108010337350221197108010337 卓婧3502125506140027350212195506140027 诸葛云5102217412010219510221197412010219 孔贵1324265906201239132426195906201239 孙莜芳1401217002284204140121197002284204 秦昭福510221680915251孙倩倩5102217008023544510221197008023544 张水清3502048012252516350204198012252516 李清水1401216512315512140121196512315512 吴花5118015603152243511801195603152243 蔡培养6201237508251511620123197508251511 思路解析 REPLACE B2 7 19 C2 同 REPLACE B2 7 0 19 C2 此公式利用 REPLACE 函数把B2单元格中的数据 从第7位开始把0个字符替换为 19 即从第7位中插入 19 再用连接运算符 把C2单元格的验证码连接起来 最终得出升为18位的身份证号码 即从第7位中插入 19 再用连接运算符 把C2单元格的验证码连接起来 最终得出升为18位的身份证号码 序序号号设设备备编编号号添添加加前前导导0 0 新新设设备备编编号号 TEXT B2 00000000 TEXT B2 00000000 显显示示格格式式里里的的下下一一个个字字符符 11009100010091 00010091 2120400001204 00001204 3137000001370 00001370 4482900004829 00004829 51059200010592 00010592 6999000009990 00009990 7867400008674 00008674 85324600053246 00053246 98087000080870 00080870 108302500083025 00083025 111415800014158 00014158 126147900061479 00061479 136243100062431 00062431 144842100048421 00048421 155858900058589 00058589 166997100069971 00069971 17590000005900 00005900 188214600082146 00082146 196199000061990 00061990 TEXT B2 00000000 TEXT B2 REPT 0 8 REPT主要用于生成连续的8位0的 文本字符串 00010091 00010091 00001204 00001204 00001370 00001370 00004829 00004829 00010592 00010592 00009990 00009990 00008674 00008674 00053246 00053246 00080870 00080870 00083025 00083025 00014158 00014158 00061479 00061479 00062431 00062431 00048421 00048421 00058589 00058589 00069971 00069971 00005900 00005900 00082146 00082146 00061990 00061990 转转换换中中文文年年度度 TEXT A3 DBNum1 0年 TEXT A3 DBNum2 0年 拟拟转转换换数数字字中中文文小小写写中中文文大大写写 1999一九九九年壹玖玖玖年 2000二 年贰零零零年 2005二 五年贰零零伍年 2008二 八年贰零零捌年 DBNum1 0 后面加一个0的意思是让数字逐位显示 二 五 TEXT 2005 dbnum1 0 二千 五 TEXT 2005 dbnum1 转转换换中中文文月月份份 TEXT A10 DBNum1 d月 TEXT A10 DBNum2 d月 拟拟转转换换数数字字中中文文小小写写中中文文大大写写 1一月壹月 8八月捌月 10十月壹拾月 12十二月壹拾贰月 DBNum1 d月 把数字1 12设置为日期格式 即对应1900年1月1日 1900年1月12日 而之所以使用 d月 是为了满足中文月份的读法 DBNum1 d日 把数字1 31设置为日期格式 即对应1900年1月1日 1900年1月31日 取日期中的天数 并显示为几日 d 使用没有前导零的数字来显示日期 1 31 转转换换中中文文天天数数 TEXT A17 DBNum1 d日 TEXT A17 DBNum2 d日 拟拟转转换换数数字字中中文文小小写写中中文文大大写写 1一日壹日 5五日伍日 10十日壹拾日 15十五日壹拾伍日 21二十一日贰拾壹日 31三十一日叁拾壹日 NUMBERSTRING A3 3 年 NUMBERSTRINGNUMBERSTRING 一九九九年 二 年 二 五年 二 八年 NUMBERSTRING A10 1 月 NUMBERSTRINGNUMBERSTRING 一月 八月 一十月 一十二月 NUMBERSTRING A17 1 日 NUMBERSTRINGNUMBERSTRING 一日 五日 一十日 一十五日 二十一日 三十一日 DBNum1 d月 把数字1 12设置为日期格式 即对应1900年1月1日 1900年1月12日 而之所以使用 d月 是为了满足中文月份的读法 DBNum1 d日 把数字1 31设置为日期格式 即对应1900年1月1日 1900年1月31日 取日期中的天数 并显示为几日 部门姓名加班时间 人力资源部王生华3 01 人力资源部孙德强5 22 人力资源部孔炎生7 27 人力资源部杨忠3 21 人力资源部吴智志3 04 科技信息部赵军国4 15 科技信息部李忠仁2 44 科技信息部严生强3 11 科技信息部吴炎展4 39 科技信息部魏刚艺2 42 销售部李健文1 43 销售部魏智展4 39 销售部吴德文4 06 销售部韩展生4 04 销售部蒋华强4 38 销售部韩艺仁2 47 销售部蒋生刚5 22 销售部魏梅虹3 12 财务部钱楠真0 22 财务部曹丹君4 12 财务部严妙雯3 25 财务部韩雯芬2 18 财务部冯芳玉3 38 财务部钱梅华3 46 合合计计87 5887 58 TEXT SUM C2 C25 h mm 自定义数字格式 h mm 小时累计数 自定义数字格式 m ss 显示累计分钟或秒数 8 87 7 5 58 8 ExcelhomeExcelhome模模拟拟发发票票 地址 2007年10月31日填发 品品名名规规格格单单价价数数量量价价值值 金金额额 十十 万万 千千 百百 十十 元元 角角 分分 WORD非常接触52 4381 991 20 199120 Excel实战技巧精粹58 65432 521 95 2521