EXCEL函数技巧-文本处理

文本处理的函数◆文本函数的应用技巧◇字符转化技巧◇生成A～Z字母序列◇重复文本◇字符串比较及清理垃圾字符◇计算字符出现的次数◇字符合并的妙用◇重复记录自动编号◇提取身份证中8位出生日期◇利用文本提取函数进行数字分列◇数字转化为英文序数字符转化技巧英文字母大小写转换、字符与ANSI字符代码转换及半角与全角的转换。字符串2函数名称功能"iloVeexcEl""ILOVEEXCEL"=UPPER(B8)全部转化为大写英文字母"iloveexcel"=LOWER(B8)全部转化为小写英文字母"ILoveExcel"=PROPER(B8)第一个字母转为大写，其余直接转化为小写"iloVeexcEl"=ASC(B8)将全角字母转换为半角字母"ILOVEEXCEL"＂Ｉ　ＬＯＶＥ　ＥＸＣＥＬ＂=WIDECHAR(B12)将半角字符转化为全角字符"iloveexcel"＂ｉ　ｌｏｖｅ　ｅｘｃｅｌ＂=WIDECHAR(B13)将半角字符转化为全角字符＂Ｉ　ＬＯＶＥ　ＥＸＣＥＬ＂"ILOVEEXCEL"=ASC(E12)将全角字母转换为半角字母换行文本手工输入WelcometoClub.excelhome.net公式输入WelcometoClub.excelhome.net="Welcometo"&CHAR(10)&"Club.excelhome.net"利用CHAR（）函数生产A～Z序列序号生成26个字母序列生成AA~ZZ的循环序列生成字符代码1AAA
备注：2BAB3CAC有些字符代码没有显示，是因为字符很小。4DAD5EAE6FAF7GAG8HAH9IAI10JAJ11KAK12LAL13MAM14NAN15OAO16PAP17QAQ18RAR19SAS20TAT21UAU22VAV23WAW24XAX25YAY26ZAZ27BA28BB29BC30BD31BE32BF33BG!34BH"35BI#36BJ$37BK%38BL&39BM'40BN(41BO)42BP*43BQ+44BR,45BS-46BT.47BU/48BV049BW150BX251BY352BZ453CA554CB655CC756CD857CE958CF:59CG;60CH<61CI=62CJ>63CK?64CL@65CMA66CNB67COC68CPD69CQE70CRF71CSG72CTH73CUI74CVJ75CWK76CXL77CYM78CZN79DAO80DBP81DCQ82DDR83DES84DFT85DGU86DHV87DIW88DJX89DKY90DLZ91DM[92DN\93DO]94DP^95DQ_96DR`97DSa98DTb99DUc100DVd101DWe102DXf103DYg104DZh105EAi106EBj107ECk108EDl109EEm110EFn111EGo112EHp113EIq114EJr115EKs116ELt117EMu118ENv119EOw120EPx121EQy122ERz123ES{124ET|125EU}126EV~127EW128EX€129EY130EZ131FA132FB133FC134FD135FE136FF137FG138FH139FI140FJ141FK142FL143FM144FN145FO146FP147FQ148FR149FS150FT151FU152FV153FW154FX155FY156FZ157GA158GB159GC160GD161GE162GF163GG164GH165GI166GJ167GK168GL169GM170GN171GO172GP173GQ174GR175GS176GT177GU178GV179GW180GX181GY182GZ183HA184HB185HC186HD187HE188HF189HG190HH191HI192HJ193HK194HL195HM196HN197HO198HP199HQ200HR201HS202HT203HU204HV205HW206HX207HY208HZ209IA210IB211IC212ID213IE214IF215IG216IH217II218IJ219IK220IL221IM222IN223IO224IP225IQ226IR227IS228IT229IU230IV231IW232IX233IY234IZ235JA236JB237JC238JD239JE240JF241JG242JH243JI244JJ245JK246JL247JM248JN249JO250JP251JQ252JR253JS254JT255JUÿ重复文本---REPT函数函数REPT用来将特定字符按给定的次数重复填充。1、填充字符，本例中将C列存放的数字转化为10位数的编码，原位数不足的前面补0或空格。填补“0”填补空格5211460000521146NO.5211464352100000435210NO.4352109779470000977947NO.9779471120280000112028NO.112028710030000071003NO.710039597840000959784NO.9597846303360000630336NO.630336536140000053614NO.536142210710000221071NO.2210718645600000864560NO.8645602、制作文本直方图，本例中利用PEPT函数，可以在不创建Excel图表的情况下制作一个块状的直方图。进度计划实际进度超额完成进度滞后月份超额完成1月3203303.13%1月■■■2月2402608.33%2月■■■■■■■■3月300280-6.67%■■■■■■■3月4月3203406.25%4月■■■■■■5月3203509.38%5月■■■■■■■■■6月360350-2.78%■■■6月7月4204404.76%7月■■■■■8月420400-4.76%■■■■■8月9月3804005.26%9月■■■■■10月3603805.56%10月■■■■■■11月3804005.26%11月■■■■■12月4004102.50%12月■■■字符串比较及清理垃圾字符在Excel中针对某些数据进行查找与引用操作时，表面看起来相同的字符串，却经常无法查询到相匹配的结果，这往往是因为目标字符串与关键字不符导致的。例子1：EXACT函数（比较）ohOHohohEXACT函数结果说明=EXACT(C59,D59)false大小写不同，返回false=EXACT(C59,D60)falseC2中含有空格，返回false=EXACT(C59,C60)true字体不同，但忽略，返回true例子2：Substitute函数（替换）我好像见过你?函数结果说明=SUBSTITUTE(C68,"我","他",1)他好像见过你?将字符串中第1个“我”替换为“他”=SUBSTITUTE(C68,"你","他")我好像见过他?省略instance_num，则将字符串中第1个“你”替换为“他”例子3：Trim函数Goodafternoon！函数结果说明=TRIM(C74)Goodafternoon！清除B1中字符串前后的空格=TRIM("你好吗?")你好吗？清除字符串前面的空格例子4：LEN函数（求字符长度）HappyBirthdaytoYou!函数结果说明=LEN(C80)22返回A1中字符串的长度=LEN("")1空格也作为字符进行计数=LEN("你好")2返回字符串“你好”的长度例子5：本例中利用介绍的公式将两个字符串进行比较。字符串1字符串2字符串1的长度字符串2的长度Exact函数结果JohnsmithJohnSmith1010falsetrueJohnsmithJohnsmith1011falsetruetrue中文英文中文英文75falseJohnsmith替换掉C84单元的的空格2得到C84单元中的空格数量计算字符出现的次数提取字符串包括：从原有文本中截取一部分用于形成新的文本是常见的文本运算，主要的文本截取函数有LEFT函数、MID函数和RIGHT函数；（1）从完整路径中提取文件名；E:\MyDocuments\函数\Sample\文本处理.xls函数结果公式文本处理.xls=RIGHT(C122,LEN(C122)-FIND("/",SUBSTITUTE(C122,"\","/",LEN(C122)-LEN(SUBSTITUTE(C122,"\","")))))文本处理.xls=REPLACE(C122,1,FIND("々",SUBSTITUTE(C122,"\","々",LEN(C122)-LEN(SUBSTITUTE(C122,"\",)))),)（2）提取全角字符－－－文本和数字分离源数据文字数字提取汉字取其他Jdaabb123Jdaabb123Jdaabb123SMB3312SMB3312SMB3312苹果430苹果430苹果430哈密瓜234哈密瓜234哈密瓜234（3）提取任意位置的汉字文本方法一方法二方法二的容错优化We热爱ExcelHome热爱#VALUE!苹果430苹果#REF!哈密瓜234哈密瓜#REF!ABC提取连续汉字123提取连续汉字#REF!ABCDEFG#N/A#REF!ILikeIt#N/A#REF!字符合并妙用文本合并符“&”将符号两边的内容作为文本加以连接，除了比CONCATENATE函数更加简洁灵活，没有30个参数限制外，还有多种妙用，如连接名字与姓氏，连接注释文字和计算结果及去除引用空单元格返回的0。产品名称产品规格数量合并结果数量合并AAX0187AAX01162BBX0261BBX02121CCX0332CCX0350AAX0418AAX0418BBX0577BBX0577AAX0175AAX01162BBX0260BBX02121CCX0318CCX0350重复记录自动编号在一些情况下，用户希望将重复记录按出现的先后顺序进行编号以作区别，不重复记录则保持不变。本例D列单元格输入介绍的公式，可以为重复记录加上数字编号。源数据加上数字编号SN1SN2ProcessdatetimeHt.603?002Pad1-1物料甲物料甲111M32340137.016:09-0.60229物料甲物料甲222M32340137.020:21-0.60242物料丙物料丙33M32340138.000:59-0.60227物料甲物料甲344M32340138.008:03-0.60219物料乙物料乙155M32340138.013:16-0.60211物料甲物料甲466M32340138.016:20-0.60221物料甲物料甲577M32340138.021:07-0.60227物料乙物料乙288M32340139.000:14-0.60232物料甲物料甲699M32340139.005:09-0.60221物料甲物料甲7110M32440137.021:00-0.60269物料甲物料甲8211M32440138.000:35-0.60262物料丙物料丙2312M32440138.004:29-0.60252物料甲物料甲9413M32440138.007:58-0.60265物料乙物料乙3514M32440138.013:17-0.60248物料甲物料甲10615M32440138.016:13-0.60262物料甲物料甲11716M32440138.020:32-0.60266物料乙物料乙4817M32440139.000:15-0.60259物料甲物料甲12118M40740137.016:23-0.60287物料甲物料甲13219M40740137.021:22-0.60306物料甲物料甲14320M40740138.000:55-0.60291物料丙物料丙3421M40740138.005:09-0.60288物料甲物料甲15522M40740138.007:53-0.60280物料乙物料乙5123M40840138.000:11-0.60309物料甲物料甲16224M40840138.004:06-0.60313物料甲物料甲17325M40840138.007:38-0.60325物料乙物料乙6426M40840138.013:17-0.60318物料甲物料甲18527M40840138.015:45-0.60322提取身份证中8位出生日期姓名身份证号码性别出生日期年龄张三110221650815224女1965081544李四510221197412010219男1974120135王五132426590620123男1959062050周六140121700228420女1970022839钱七350583197810120072男1978101231彭九511801520925520女1952092557刘二620123790513150女1979051330利用文本提取函数进行数字分列Excelhome模拟发票地址：2009年12月22日填发品名规格单价数量价值金额十万千百十元角分WORD非常接触52.4381,991.20199120Excel实战技巧精粹58.65432,521.95252195合计金额(大　写)4,513.15￥451315填制人：经办人：业户名称（盖章）11st1111th2121st22nd1212th2222nd33rd1313th2323rd44th1414th2424th55th1515th2525th66th1616th2626th77th1717th2727th88th1818th2828th99th1919th2929th1010th2020th3030thTEXT函数应用技巧日期时间格式年月日星期应用示例时分秒总秒yyyyyyyyyymmmmmmmmmmddddddddddaaaaaaam月份报表hhh[h]h时[m]TEXT($A2,"hmm")-TEXT($A2,"h")*100MOD((TEXT($A2,"[S]")-s)/60,60)INT(MOD(TEXT($A2,"[S]")/60,60))MOD(TEXT($A2,"[m]"),60)&"分"s[s]\Sm/d/yyd-mmd-mmd-mmmd-mmmmd-mmmm-yyyy-mmmmmmmmyyhh:mmAM/PMh:mm:ssa/p1985/03/1105:55:18858519851985303MarMarch1111MonMonday一星期一3月份报表5057468135时4480883555555555分182688530118S3/11/8511-0311-0311-Mar11-March11-March-8585-MarchMarch8505:55AM5:55:18a1986/04/1206:56:19868619861986404AprApril1212SatSaturday六星期六4月份报表6067563426时4538057656565656分192722834579S4/12/8612-0412-0412-Apr12-April12-April-8686-AprilApril8606:56AM6:56:19a1987/05/1407:57:20878719871987505MayMay1414ThuThursday四星期四5月份报表7077658717时4595231757575757分202757139040S5/14/8714-0514-0514-May14-May14-May-8787-MayMay8707:57AM7:57:20a1988/06/1408:58:21888819881988606JunJune1414TueTuesday二星期二6月份报表8087754008时4652405858585858分212791443501S6/14/8814-0614-0614-Jun14-June14-June-8888-JuneJune8808:58AM8:58:21a1989/07/1609:59:22898919891989707JulJuly1616SunSunday日星期日7月份报表9097849299时4709579959595959分222825747962S7/16/8916-0716-0716-Jul16-July16-July-8989-JulyJuly8909:59AM9:59:22a1990/08/1711:00:23909019901990808AugAugust1717FriFriday五星期五8月份报表111179445911时476675400000分232860052423S8/17/9017-0817-0817-Aug17-August17-August-9090-AugustAugust9011:00AM11:00:23a1991/09/1812:01:24919119911991909SepSeptember1818WedWednesday三星期三9月份报表121280398812时482392811111分242894356884S9/18/9118-0918-0918-Sep18-September18-September-9191-SeptemberSeptember9112:01PM12:01:24p1992/10/1913:02:259292199219921010OctOctober1919MonMonday一星期一10月份报表131381351713时488110222222分252928661345S10/19/9219-1019-1019-Oct19-October19-October-9292-OctoberOctober9201:02PM1:02:25p1993/11/2014:03:269393199319931111NovNovember2020SatSaturday六星期六11月份报表141482304614时493827633333分262962965806S11/20/9320-1120-1120-Nov20-November20-November-9393-NovemberNovember9302:03PM2:03:26p1994/12/2215:04:279494199419941212DecDecember2222ThuThursday四星期四12月份报表151583257515时499545044444分272997270267S12/22/9422-1222-1222-Dec22-December22-December-9494-DecemberDecember9403:04PM3:04:27p2000/01/0100:00:00000020002000101JanJanuary101SatSaturday六星期六1月份报表0008766240时525974400000分03155846400S1/1/001-011-011-Jan1-January1-January-0000-JanuaryJanuary0012:00AM12:00:00a数值格式格式数据00000000000.00.000.0000.0000##.##,###,##.##,##.###,##.####,##.###0.###0#,##0.0;-#,##0.0$#,##0;($#,##0)$#,##0.00;($#,##0.00)#%#.00%0.00E+000.00E-00##0,.0000##0,,.00011.1900010100100011.21.191.1901.190011.211.21.191.191.1901.1901.2$1$1.19119%119.00%1.19E+001.19E000.00120.0001102.313461021021020102102.3102.31102.313102.3135102102.3102102.3102.31102.313102.3135102.3135102.3$102$102.3110231%10231.35%1.02E+021.02E020.10230.0001203.436912032032030203203.4203.44203.437203.4369203203.4203203.4203.44203.437203.4369203.4369203.4$203$203.4420344%20343.69%2.03E+022.03E020.20340.0001304.560373053053050305304.6304.56304.560304.5604305304.6305304.6304.56304.56304.5604304.5604304.6$305$304.5630456%30456.04%3.05E+023.05E020.30460.0001405.683824064064060406405.7405.68405.684405.6838406405.7406405.7405.68405.684405.6838405.6838405.7$406$405.6840568%40568.38%4.06E+024.06E020.40570.0001506.807285075075070507506.8506.81506.807506.8073507506.8507506.8506.81506.807506.8073506.8073506.8$507$506.8150681%50680.73%5.07E+025.07E020.50680.0011-405.68382-406-406-406-0406-405.7-405.68-405.684-405.6838-406-405.7-406-405.7-405.68-405.684-405.6838-405.6838-405.7($406)($405.68)-40568%-40568.38%-4.06E+02-4.06E02-0.40570.0001-304.56037-305-305-305-0305-304.6-304.56-304.560-304.5604-305-304.6-305-304.6-304.56-304.56-304.5604-304.5604-304.6($305)($304.56)-30456%-30456.04%-3.05E+02-3.05E02-0.30460.0001-203.43691-203-203-203-0203-203.4-203.44-203.437-203.4369-203-203.4-203-203.4-203.44-203.437-203.4369-203.4369-203.4($203)($203.44)-20344%-20343.69%-2.03E+02-2.03E02-0.20340.0001-102.31346-102-102-102-0102-102.3-102.31-102.313-102.3135-102-102.3-102-102.3-102.31-102.313-102.3135-102.3135-102.3($102)($102.31)-10231%-10231.35%-1.02E+02-1.02E02-0.10230.0001-1.19000-1-01-001-0001-1.2-1.19-1.190-1.1900-1-1.2-1-1.2-1.19-1.19-1.190-1.190-1.2($1)($1.19)-119%-119.00%-1.19E+00-1.19E00-0.00120.00010.0010000000000000.00.000.0010.0010....001.0010.00100.0$0$0.00%.10%1.00E-031.00E-030.00000.0001101.124461011011010101101.1101.12101.124101.1245101101.1101101.1101.12101.124101.1245101.1245101.1$101$101.1210112%10112.45%1.01E+021.01E020.10110.0001165478965165478965165478965165478965165478965165478965.0165478965.00165478965.000165478965.0000165478965165478965.165,478,965165,478,965.165,478,965.165,478,965.165,478,965.0165478965.0165,478,965.0$165,478,965$165,478,965.0016547896500%16547896500.00%1.65E+081.65E08165478.9650165.4791应用说明下面 表格 关于规范使用各类表格的通知入职表格免费下载关于主播时间做一个表格详细英语字母大小写表格下载简历表格模板下载 标识一些可用来创建用户自定义日期/时间格式的字符：字符说明(:)时间分隔符。在一些国别，可能用其他符号来当时间分隔符。格式化时间值时，时间分隔符可以分隔时、分、秒。时间分隔符的真正字符在格式输出时取决于系统的设置。(/)日期分隔符。在一些国别，可能用其他符号来当日期分隔符。格式化日期数值时，日期分隔符可以分隔年、月、日。日期分隔符的真正字符在格式输出时取决于系统设置。d以没有前导零的数字来显示日(1–31)。dd以有前导零的数字来显示日(01–31)。ddd以简写来表示日(Sun–Sat)。dddd以全称来表示日(Sunday–Saturday)。m以没有前导零的数字来显示月(1–12)。如果m是直接跟在h或hh之后，那么显示的将是分而不是月。mm以有前导零的数字来显示月(01–12)。如果m是直接跟在h或hh之后，那么显示的将是分而不是月。mmm以简写来表示月(Jan–Dec)。mmmm以全称来表示月(January–December)。y或yy以两位数来表示年(00–99)。yyy或yyyy以四位数来表示年(0000–9999)。H以没有前导零的数字来显示小时(0–23)。Hh以有前导零的数字来显示小时(00–23)。M以没有前导零的数字来显示分(0–59)需跟在h或hh之后。Mm以有前导零的数字来显示分(00–59)需跟在h或hh之后。S以没有前导零的数字来显示秒(0–59)需跟在h或hh之后。Ss以有前导零的数字来显示秒(00–59)需跟在h或hh之后。AM/PM在中午前以12小时配合大写AM符号来使用；在中午和11:59P.M.间以12小时配合大写PM来使用。am/pm在中午前以12小时配合小写am符号来使用；在中午和11:59P.M.间以12小时配合小写pm来使用。A/P在中午前以12小时配合大写A符号来使用；在中午和11:59P.M.间以12小时配合大写P来使用。a/p在中午前以12小时配合小写a符号来使用；在中午和11:59P.M.间以12小时配合小写p来使用。下面表格标识了一些可用来创建用户自定义数值格式的字符：(0)数字占位符。显示一位数字或是零。如果表达式在格式字符串中0的位置上有一位数字存在，那么就显示出来；否则，就以零显示。如果数值的位数少于格式表达式中零的位数（无论是小数点的左方或右方），那么就把前面或后面的零补足。如果数值的小数点右方位数多于格式表达式中小数点右面零的位数，那么就四舍五入到有零的位数的最后一位。如果数值的小数点左方位数多于格式表达式中小数点左面零的位数，那么多出的部分都要不加修饰地显示出来。(#)数字占位符。显示一位数字或什么都不显示。如果表达式在格式字符串中“#”的位置上有数字存在，那么就显示出来；否则，该位置就什么都不显示。此符号的工作原理和“0”数字占位符大致相同，不同之处只有在当表达式中数值的位数少于“#”的位数（无论是小数点左方或右方）时，不会把前面或后面的零显示出来。(.)小数点占位符。在一些国别是用逗号来当小数点的。小数点占位符用来决定在小数点左右可显示多少位数。如果格式表达式在此符号左边只有正负号，那么小于1的数字将以小数点为开头。如想在小数前有“0”的话，那么请在小数点占位符前加上“0”这个数字占位符。小数点占位符的实际字符在格式输出时要看系统的数字格式而定。(%)百分比符号占位符。表达式乘以100。而百分比字符(%)会插入到格式字符串中出现的位置上。(,)千分位符号占位符。在一些国别，是用句点来当千位符号。千位符号主要是把数值小数点左边超过四位数以上分出千位。如果格式中在数字占位符（0或#）周围包含有千分位符号，则指定的是 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 的千分位符号使用法。两个邻近的千分位符号或一个千分位符号紧接在小数点左边（不管小数位是否指定），其意思为“将数值除以1000，按需要四舍五入”。例如，可以用格式字符串"##0,,"将1亿表示成100。数值小于1百万的话表示成“0”，两个邻近的千分位符号除了紧接在小数点左边以外，在任何位置出现时均简单地视为指定了使用千分位符号。小数点占位符的真正字符在格式输出时，需视系统识别的数字格式而定。(E-E+e-e+)科学格式。如果格式表达式在E-、E+、e-或e+的右方含有至少一个数字占位符（0或#），那么数值将表示成科学格式，而E或e会被安置在数字和指数之间。E或e右方数字占位符的个数取决于指数位数。使用E-或e-时，会用减号来表示负的乘幂。使用E+或e+时，会用减号来表示负的乘幂并用加号来表示正的乘幂。-+$()显示一个原义字符。如想显示那些列出的字符之外的字符时，可以用反斜杠(\)字符作前缀或以双引号("")括起来。(\)将格式字符串中下一个字符显示出来。如想显示一作为原义字符并有特殊含义的字符，可将此字符置于反斜杠(\)之后。反斜杠本身并不会显示出来。而使用反斜杠(\)的效果和使用双引号是一样的。如想显示反斜杠字符，可使用两个反斜杠(\\)。那些不能显示为原义字符的字符是日期格式字符和时间格式字符（a,d,h,m,s,y,/和:）、数值格式字符（#,0,%,E,e,逗点和句点）和字符串格式字符（@,&,和!）。可以用下面任意字符来创建格式表达式用于字符串：@字符占位符。显示字符或是空白。如果字符串在格式字符串中@的位置有字符存在，那么就显示出来；否则，就在那个位置上显示空白。除非有惊叹号字符(!)在格式字符串中，否则字符占位符将由右而左被填充。&字符占位符。显示字符或什么都不显示。如果字符串在格式字符串中和号(&)的位置有字符存在，那么就显示出来；否则，就什么都不显示。除非有惊叹号字符(!)在格式字符串中，否则字符占位符将由右而左被填充。!强制由左而右填充字符占位符。缺省值是由右而左填充字符占位符。用户自定义日期/时间格式示例下面表格是以用户自定义日期/时间格式来显示December7,1998的示例：格式显示结果m/d/yy12/7/98d-mmm7-Decd-mmmm-yy7-Dec-98dmmmm7-DecmmmmyyDec-98hh:mmAM/PM8:50PMh:mm:ssa/p8:50:35PMh:mm20:50h:mm:ss20:50:35m/d/yyh:mm 12/7/9820:50下面的表格包含一些数值格式表达式的示例。（这些示例皆假设系统之国别设置为English-U.S.）第一列包含格式字符串，其他列则为输出结果，假设格式化数据有列表头中给定的值。Text（格式）正5负50.505-510.005.00-5.000.50#,##05-51#,##0.00;Nil5.00Nil0.50$#,##0;($#,##0)$5($5)$1$#,##0.00;($#,##0.00)$5.00($5.00)$0.500%500.00%-500.00%50.00%0.00%500.00%-500.00%50.00%0.00E005.00E00-5.00E005.00E-010.00E-005-50