大家都在搜
关注稻壳领福利
Excel常用函数之CEILING
Excel常用函数之TEXT
Excel常用函数之LEN
Excel常用函数之MATCH
Excel常用函数之SUBTOTAL
Excel常用函数之SUMIF
员工申诉汇总表
采购统计汇总表
产值统计汇总表
记账凭证汇总表
Excel常用函数之COUNTIF
Excel常用函数之INDIRECT
设计作品1042 独家高品质原创文档
举报
进销存汇总表
销售员季度业绩汇总表
销售员日常工作汇总表
学生信息登记汇总表（含家长）通用版
招聘需求情况汇总表
收支汇总表-图表结合
（WPS技巧教程）一键美化表格
身份证号码信息提取
我的
福利
反馈
微信咨询在线客服
客服
回到顶部
![办公职员文本函数应用汇总表-表格模板EXCEL文本处理公式应用与实例目录,NO.功能超链接备注,1.0字符与字节 字符与字节,2.0LEFT、RIGHT函数LEFT、RIGHT函数,3.0MID函数 MID函数,4.0 LEN函数 LEN函数,5.0FIND函数FIND函数,6.0SEARCH函数SEARCH函数,7.0综合运用一综合运用一,8.0综合运用二 综合运用二,9.0 REPLACE函数REPLACE函数,10.0SUBSTITUTE函数SUBSTITUTE函数,11.0CHAR与CODECHAR与CODE,12.0UPPER与LOWER EXACT UPPER与LOWER EXACT,13.0REPTREPT,14.0TRIMTRIM,15.0自定义格式自定义格式,16.0TEXT TEXT,17.0综合练习（发票设置） 综合练习（发票设置）,,4.1 字符与字节,字节：,字节(Byte): 字节是通过网络传输信息（或在硬盘或内存中存储信息）的单位。,字节是计算机信息技术用于计量存储容量和传输容量的一种计量单位,1B=8b,对象例子占用字节数备注,英文字母（小写）a,英文字母（大写）A,数字,中文中,韩文글,日文ぁ,标点（英文）,,标点（中文），,字符:,字符是指计算机中使用的字母、数字、字和符号，只是一个符号。,字符 人们使用的记号，抽象意义上的一个符号。 '1', '中', 'a', '$', '￥', …… ,注意：,当启用支持 DBCS 的语言的编辑并将其设置为默认语言时，有些文本类函数会将每个双字节字符按 2 计数,支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。,,= LEFT ( TEXT , Num_chars )= RIGHT ( TEXT , Num_chars ),1.如果省略 num_chars，则假设其值为 1。,2.Num_chars 必须大于或等于零。,3.如果 num_chars 大于文本长度，则 LEFT 返回全部文本。,示例一,LEFTRIGHT,数据,我通,51zxw.net551zx5.net,函数,ll,67854.0667856478544,示例二示例三,编号修正后性别称呼工作天数小时数,88.0赵玉晓赵玉晓12个小时12.0,43.4小时43.4,123.0勒智智勒智智,4.028时28.0,2345.0臧弟惠臧弟惠27.3小时27.3,8900.031小时31.0,76.09小时9.0,89.08个小时8.0,1.0181.743.4,作业：男称呼为先生，女称呼为小姐,但姓名中可能有复姓。,性别称呼复姓表,欧阳玉晓欧阳欧阳,徐令狐,令狐智智令狐司马,魏,司马惠司马,王,刘=IF(OR(LEFT(B39,2)="欧阳",LEFT(B39,2)="令狐",LEFT(B39,2)="司马"),LEFT(B39,2),LEFT(B39,1)),吴,,= MID ( text , start_num , num_chars )MIDB(text, start_num, num_bytes),1.如果 start_num 大于文本长度，则 MID 返回空文本 ("")。,2.如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。,3.如果 start_num 小于 1，则 MID 返回错误值 #VALUE!。,4.如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。,5.如果 num_bytes 是负数，则 MIDB 返回错误值 #VALUE!。,示例一,数据,我是网通我是网15是网通,51zxw.net51z51z1zx,函数函数函 数,leftleflefeft,67854.0678678785,示例二：将下列数字拆分成个位数，再求和？,数字数字之和,3442.0,12.0330722197511084527,432.0,222.0,1112.019751108,100.0,作业:用MID判断出下列分数的等级,姓名分数等级,张三74.0>=90优,李四52.0>=80良,王五100.0>=60中,周六91.0<60差,谢七94.0,吴丽91.0,周华64.0,,=LEN(text)=LENB(text),abcd efg569.0,示例一3.0,数据2.0,我是网通8.03.0,51zxw.net,函数2.0,left,67854.05.05.0,示例二,信息数据号码类型号码,13900876545678手机号13900876545678手机号,028-78678987坐机号028-78678987坐机号,546677887655666677银行卡号546677887655666677银行卡号,分析：,546502445开心号546502445开心号24.0,15.0,,FIND( find_text , within_text , [start_num] )FINDB(find_text, within_text, [start_num]),within_text 中的首字符是编号为 1 的字符。如果省略 start_num，则假设其值为 1。,FINDFINDBFIND第三参数FINDB第三参数,我要自 学函数10.010.01.0,数很好函,学习函数很重要,29,find_text 为空，结果是start_num的指定的位置（匹配搜索字符串中的首字符）,start_num 不大于 0，则返回 #VALUE!。,start_num 大于 within_text 的长度，则返回#VALUE!。,FIND 与 FINDB 区分大小写并且不允许使用通配符51ZXW.NET3.0,使用 start_num 可跳过指定的字符数,Q784F8QWE,Q3FFWQDW,DQEFDFDFQD9.09.0,示例一：将第二个Q后面的内容提取出来？,数据提取内容,Q78QWEQWE,Q3FFWQDWQDW,DQEFDFDFQDQDabcdfghjkl,WWWWQEQ123Q123,QQ123456Q123456,3.0,作业:找出人名在表中出现的次数,投票次数被投票人,第1人牛三斤、古朋、张世杰1.0,第2人古朋、张世杰,第3人李行、古朋、牛三斤,第4人古朋、牛三斤 张世杰,第5人张世杰、李行,第6人张世杰、牛三斤,第7人张世杰、牛三斤,第8人古朋、张世杰、牛三斤,张世杰李行牛三斤古朋,答题区:,正确结果：,=COUNT(FIND(B48,$B$38:$B$45,1)),作业二：,句子姓名事件,JIM把衣服洗了JIMJIM衣服洗了,李四被评为三好生李四李四评为三好生,张无忌把函数学完了张无忌张无忌函数学完了,司马小小被提拔了司马小小司马小小提拔了,,SEARCH( find_text , within_text , [start_num] )SEARCHB(find_text,within_text,[start_num]),51zxw.NET,SEARCH 和 SEARCHB 函数不区分大小写。可以使用通配符,使用通配符:"?"匹配任意单个字符；"*"匹配任意一串字符。文本数据,要查找实际的问号或星号，请在该字符前键入波形符 (~)。数字,个数1515,数学,*数字*偶数:52.02.0,*是通配符,代表任意字符：*15.08.08.0,示例一：统计出来号码以131开头，尾号为8的手机号有多少个？,手机号结果,13157945598.0,13257945599.0,13157945600.0,13157945601.0,13157945603.0,13857945604.00.0,13157945605.011.0,7.0,13157945607.0,13957945609.0,13157945610.0,13157945611.0,13157945612.0,示例二：提取号码信息,信息数据提取号码,游戏卡号：546502445546502445,手机号：1390087654567813900876545678,坐机号：028-78678987028-78678987,银行卡号码：546677887655666000546677887655666000,作业：,数据提取数字,我有10元,你是35034哈哈,爱你1314呀,520是真的,好的，88,,示例一：提取城市名,城市名,中山市三角镇中山市,广州市开发区广州市,沪市浦东区沪市,大连市区老边弯大连市,龙城市江北区龙城市,台北市新林区台北市,北京市朝阳区北京市,乌鲁木齐市成峰大道乌鲁木齐市,保定市无人区保定市,景洪市江北区景洪市,佳木斯市连运区佳木斯市,佛山市顺德区佛山市,示例二：提取地区名,地区名,中山市石岐区金三大道11号石岐区,广州市开发区永和大道55号开发区,乌鲁木齐市新区成峰大道1号新区,沪市浦东区新龙大道120号浦东区,佳木斯市连运区万福龙大道126号连运区,大连市燕山区老边弯九龙大道789号燕山区,龙城市江北区化新大道456号江北区,台北市新林区护城大道110号新林区,北京市朝阳区新庄大道56号朝阳区,成都市青白江区平安大道78号青白江区,景洪市江北区古龙大道36号江北区,佛山市顺德区吉大新大道469号顺德区,作业：提取街道名？,街道名,中山市石岐区金三大道11号金三大道金三大道,广州市开发区永和大道55号永和大道永和大道,乌鲁木齐市新区成峰路1号成峰路成峰路,沪市浦东区新龙大道120号新龙大道新龙大道,佳木斯市连运区万福龙大街126号万福龙大街万福龙大街,大连市燕山区老边弯九龙大道789号老边弯九龙大道老边弯九龙大道,龙城市江北区化新大道456号化新大道化新大道,台北市新林区护城大道110号护城大道护城大道,北京市朝阳区新庄新街56号新庄新街新庄新街,成都市青白江区平安大道78号平安大道平安大道,景洪市江北区古龙大道36号古龙大道古龙大道,佛山市顺德区吉大新路469号吉大新路吉大新路,,示例一：根据身份证判断性别？,姓名身份证号方法1方法2,许莘发3505001991020300101,徐强强3505001991020300202,臧弟惠3505001991020300303,吴基琛3505001991020300404,郑梅瑜3505001991020300505,魏荣智3505001991020300606,刘梅瑜3505001991020300707,判断要求：,18位看第17位，15看15位。奇数为男，偶数为女。,示例二：根据身份证号算出年龄？,姓名身份证号方法一方法二,徐强350500199102030010,徐强强350500198602030037,臧弟惠543322980809768,吴基琛350500198202030141,郑梅瑜35050019890203031X,魏荣智510322198301041235,刘梅瑜132426590620124,注意：18位有“19**”，15位没有"19",示例三：提取号码信息？知识点：5/19,信息数据提取号码,QQ546502445,手机号13900876545678,坐机号028-78678987,银行卡号546677887655666000,MSN号7878876677,,=REPLACE( old_text , start_num , num_chars , new_text )=REPLACEB( old_text , start_num , Num_bytes , new_text ),示例一:将编号前三个统一替换为："YYY"？,DDD-F123-112yyy-F123-112YYY-F123-112,FGF-AS23-114yyy-AS23-114YYY-AS23-114,AAA-FD345-SS1yyy-FD345-SS1YYY-FD345-SS1,CCC-DF352-422yyy-DF352-422YYY-DF352-422,YYY,示例二：将编号的头号去掉？,DDD-F1233-112,F-AS23-11432AS23-11432AS23-11432,AAAA-FD345-SSFD345-SSFD345-SS,CC-DF352-4DF352-4DF352-4,示例三：将编号的尾号去掉？,效果,DDD-F1233-112DDD-F1233112DDD-F1233,F-AS23-11432F-AS2311432F-AS23,AAAA-FD345-SSAAAA-FD345SSAAAA-FD345,CC-DF352-4CC-DF3524CC-DF352,,=SUBSTITUTE( text , old_text , new_text , [instance_num] ),注意：如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则会将 Text 中出现的每一处 old_text 都更改为 new_text。,121202,REPLACE与SUBSTITUTE的区别：,1.如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数 REPLACE。,2.如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；,示例一：将“季”字替换成“季度”,季度销量结果,第1季456.0第1季度,第2季124.0第2季度,第3季654.0第3季度,第4季245.0第4季度,注意：如果遇到这个问题最好的解决方法还是查找替换注意：如果遇到这个问题最好的解决方法还是查找替换,示例二：求出每种职称的人数？,职称姓名人数,高级经济师洪天同、褚虹玉、杨生华、李心,经济师郑生军、赵娟丹2.02.0,助理经济师冯展生、严红楠、沈刚刚、魏丹雯、褚展健、蒋香娟6.06.0,高级会计师褚发艺、钱霞芳、蒋毅德、王芳弟,会计师古行月、陈霞楠、王庆林、沈芳真,助理会计师曾杰明,工程师沈智忠、孙宏毅、冯仁健、魏仁生,助理工程师赵丽、孔虹华、杨君花3.03.0,作业：求李心在左表每个姓名单元中出现的次数？,职称姓名出现次数结果,高级经济师李心、褚虹玉、杨生华、李心,经济师郑生军、李心,助理经济师冯展生、李心、沈刚刚、魏丹雯、李心、李心,高级会计师褚发艺、钱霞芳、李心、王芳弟,会计师古行月、陈霞楠、王庆林、沈芳真0.0,助理会计师李心,工程师沈智忠、孙宏毅、冯仁健、魏仁生0.0,助理工程师赵丽、孔虹华、李心,作业二：求出每个人上班小时数？,日期张三李四王二重八,41000.08.5小时,41001.0,41002.09.4小时,41003.0,41004.0,41005.06.8小时,41006.0,41007.09.6小时,41008.08.8小时9.3小时,41009.0,41010.08.2小时,41011.0,41012.0,41013.0,41014.0,41015.0,上班小时数,,= CHAR ( number ),Y,89.0,85.0,返回对应于数字代码的字符。函数 CHAR 可将其他类型计算机文件中的代码转换为字符。,a,= CODE ( text )Y,121.0,返回文本字符串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字符集。,示例一,数字字符数字,86.0V86.0学习excel53671.0,55.0755.0函数学习47791.0,59.0;59.0好好的学47811.0,33.0!33.023.050.0,88.0X88.0,示例二：将花生与对应的英语名放置在同一行,品名结果,苹果,apple,花生,peanut換行符char(10),梨子,pear,桃子,peach,示例三：将汉字词语放在第一行，英文单词放在第二行,品名结果,苹果apple,花生peanut,电冰箱refrigerator,梨子pear,桃子peach,电视机television,,4.12 UPPER\LOWER\EXACT,= UPPER ( text )= EXACT ( text1 , text2 ), 该函数用于比较两个字符串：如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 区分大小写，但忽略格式上的差异。利用 EXACT 函数可以测试在文档内输入的文本。,= LOWER ( text ),示例一：示例二：将单词的第一个字母是大写的打沟？,apple苹果APPLE苹果apple苹果单词判断1判断2,APPLE,ann,ANKANG,jim,ZHANGQI,AndyANDYandyAndy,ANGLE,BILL,FrankyFRANKYfrankyFranky,,=REPT ( text , number_times ),aaa,1.如果 number_times 为 0，则 REPT 返回 ""（空文本）。 ,2.如果 number_times 不是整数，则将被截尾取整。 ,3.REPT 函数的结果不能大于 32,767 个字符，否则，REPT 将返回错误值 #VALUE!。 ,示例一,字符重复数结果,★2.0★★,●4.0,※3.0※※※,√5.0√√√√√,×1.0×,示例二：根据分数评星级,姓名分数星级,梅花生51.0>=60★,周琴89.0>=70★★,古柏明50.0>=80★★★,常遇青75.0>=90★★★★,示例三：将编号中第1个“-”前的字母替换为："Y",编号结果,ABC-F123-112,FSSGF-AS23-114,QQ-FD345-SS1,C-DF352-422,作业：根据分数评星级。,姓名分数星级效果>=60●,梅花生55.0>=70●●,周琴86.0●○○○>=80●●●,古柏明85.0●●●○>=90,常遇青100.0○○○○100.0●●●●●,说明：结果如果不满5颗实心圆，则用空心圆补齐,○,,= TRIM ( text ),示例一, 数据去除空格,周 林林 周 林林周 林林周林林, MY HOME MY HOMEMYHOME, WHO AM I ?WHO AM I ?WHOAMI?,MR. JOB MR. JOBMR.JOB,总结：1.去除两边多余的空格,2.单元格内容中间有多个空格，会只留下一个空格。不会去除完。,解决方法,示例二：去除单元格中所有的空格,数据结果, 我 是 excel 爱好者我 是 excel 爱好者, 周 明杰 周 明杰,J I MJ I M, T R I M 函数T R I M 函数,示例三：将编码的最后一串编号提取出来？,编码结果,DDD-F1233-112-XL,F-AS23-11432-M,AAAA-FD345-SS-dff-XXL,CC-DF352-4-XS,DD-L,FGH-SA-XXXL,,自定义格式,注释原始数据最终结果,G/通用格式以常规数字显示，相当于”分类”列表中的”常规”选项。45.745.7G/通用格式,#数字占位符。只显有意义的零而不显示无意义的零。一般与","结合使用，实现千位分隔19.819.8#.##,数字占位符。统一数字的位数，不足位数都，则用0补足。000.00,?数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还可以表示分数0.1230.123?/?,@文本占位符，如果只使用单个@，作用是引用原始文本。如果使用多个@，则可以重复文本。excelexcel@@,空表示隐藏wewwew;;;,_留出与后面字符一样宽的空位55.055.00_3,*重复下一次字符，直到充满列宽。姓名姓名@*_,,千位分隔符，一般与“#”配合使用，简化数字#,###.003000.00,,""显示在自定义格中输入的文本输入的文本1000.01000.0"人民币"0,\格式化，强制显示。和""用途相同都是显示输入的文本，且输入后会自动转变为双引号或！表达0\元1001.0-99.0-99.0,！与"\"功能一样，而且更强,可以把不能显示出来的一些字符显示出来0!0,颜色指定的颜色显示字符。有八种颜色可选：红色、黑色、黄色，绿色、白色、蓝色、青色和洋红。也可以用颜色索引号[绿色]0;[红色]0;[洋红]0;[蓝色]@,条件可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。条件要放到方括号中。必须进行简单的比较98.098.0[>=90]"优秀";[>=80]"良好";"合格"1.完整的代码结构为：“大于条件值”格式;"小于条件值"格式;"等于条件值"格式;文本格式,2.没有指定条件时为0，格式为：正数格式;负数格式;零值格式;文本格式,y按四位（1900~9999）或两位（00~99）显示年yyyy,m以两位（01~12）或一位（1~12）表示月40961.040961.0mm,d以两位（01~31）或一位（1-31）来表示天dd,h以一位（0~23）或两位（01~23）显示小时0.3846759259260.384675925926hh,m以一位（0~59）或两位（01~59）显示分钟0.0576504629630.057650462963,s以一位（0~59）或两位（01~59）显示秒ss,[h] [m] [s]显示大于24小时的小时或显示大于60的分或秒。,hh:mm:ss.00excel还支持毫秒h:mm:ss.00,[DBNUM1]中文小写[DBNum1]G/通用格式,[DBNUM2]中文大写[DBNum2]G/通用格式,dddd将日期显示为英文星期，"ddd"星期简写41019.041019.0dddd,aaaa将日期显示为中文星期，"aaa"星期简写41020.041020.0aaaa,AM/PM英文显示上下午0.506250.50625AM/PM,上午/下午中文显示上下午0.4645833333330.464583333333上午/下午,注释日期结果将格式化的单元格转换成正真的内容：,英文简写月份40930.040930.0mmm步骤1：将目标单元格设置为文本格式,英文完整月份40931.040931.0mmmm步骤2：复制,英文月份首字母40932.040932.0mmmmm步骤3：在剪贴板中粘贴,中文月份40933.040933.0[DBNum1]m"月"步骤4：在浮动标签中选择"匹配目标格式",示例一：自定义格式下面的内容,序号要求用法模拟公式结果数据,3位显示0077.0,2.0保留2位小数位数1.541.5365,判断><=0<0-26.0,>050.0,=0,4.0隐藏0值 ,-809-809.0666666666.0,112112.0,5.0隐藏负值-80.0,226226.0,0,6.0隐藏正值100.0,-100-100.0,0,7.0隐藏0、正值，负值显示正值650-650.0,2008.0,8.0转换百分比，保留2位小数95.00%0.95,加美元符号、千分符、保留2位小数$5,000,000.005000000.0,10.0用"_"将数字对齐 正数 0.460.4555, 负数 -12000.00-12000.0, 正数 28.0028.0,11.0转换星期星期二40694.0,转换星期(英文)Wednesday40695.0,13.0改变时间显示格式现在时间：05/24 8:40 AM40687.3616551,14.0汉字时间二○一一年五月二十四日43889.0,15.0时间转换成多少秒318200.368287037037,16.0自动显示几月份产值6月份产值,17.0加字符显示（20层30栏）20层30栏2030.0,18.0加字符显示（本月我的工资x元）本月我的工资8000元8000.0,19.0重复4次你好你好你好你好你好,20.0重复4次并用；号分开大家好;大家好;大家好,21.0判断分数：,优>=90、良>=60、差<60优95.0,良82.0,不及格50.0,22.0显示多小K+零头、保留3位小数5130K+560.5815130560.5812,23.0正数显示存入、负数支出、0平衡存入9811.0,平衡,支出-1189.0,24.0小于1显示百分比，,否则保留两位数,0.230.23,1.61.6,25.0转为百万123.12百万123123321.0,26.0转为万1.2万,27.0转为千2.31千2311.0,28.0转为百1.23百123.0,29.0转为十2.3十元23.0,30.0转化为分数2.252.25,,= TEXT ( value , format_text ),示例一,7月份各地区销售金额（万元）,地区盈亏金额判断结果,中山4500.0盈利¥4500,惠州-1000.0亏损¥1000,广州7400.0盈利¥7400,潮州未营业未营业,珠海5600.0盈利¥5600,深圳0.0平衡¥0,说明：如果盈亏金额：是正数则为盈利;是负数则为亏损;是0则为平衡;是未营业为未营业.,示例二：根据右表标准评先等级,姓名成绩等级标准等级,张三林85.0>=90优,李四明99.0>=80良,王成新52.0>=60中,邱家栋78.0<60差,李煜69.0,苏佳佳59.0,示例三：根据身份证算出年龄,身份证号年龄,110200197009115815,110200198107276713,110200197109251136,110200198201145314,110200198906263257,110200198301159895,110200198705275462,110200197307178486,110200198108276221,110200198803216255,110200197309246323,110200197008157343,11020019850724988834,,叉 叉 公 司 发 票,付款人（客户）：发票代码：,地址：开票日期：,国税征票函[2012]xx号商品名称规格型号数量单位单价金额备注第二联:发票联(顾客报销凭证),十万仟佰拾元角分,EXCEL函数大全16开1.0本69.464,EXCEL函数视频DVD光盘23.0张50.05,DELL电脑2.0套4580.06,合 计374,金额大写:人民币零拾壹万零仟叁佰柒拾玖元肆角零分8.07.06.05.04.03.02.01.0,开票人：收款人：客户名称（盖章）：,1.小写金额,2.大写金额](http://web.docer.wpscdn.cn/docer/new-webmall-prod/img/loading-img-42.ffcbba5.gif)