常用EXCEL函数详解及实例-文本函数.xls

zylkfxy常常用用ExcelExcel函函数数详详解解及及应应用用实实例例四四、文文本本函函数数序序号号函函数数函函数数定定义义页页码码1Asc将字符串中的全角英文字母转换为半角字符12Char返回对应于文字代码的字符13Clean删除其中含有的当前操作系统无法打印的字符.14Code返回文本字符串中第一个字符的数字代码15Concatenate将多个字符文本或单元格中的数据连接在一起(同&)26Dollar可将数字转换为文本格式,并应用货币符号27Exact测试两个字符串是否完全相同28Find从首字符开始返回查找字符的起始位置编号39Findb使用字节数从首字符开始返回查找字符的起始位置编号310Fixed将数值带上千位分隔符和小数点标记进行取整411Left从文本字符串的第一个字符开始截取指定数目的字符412Leftb从文本字符串的最左边开始提取指定字节数的字符413Len返回文本字符串中的字符个数514Lenb返回文本字符串中用于代表字符的字节数515Lower将文本字符串中的所有大写字母转换为小写字母516Mid从文本字符串的指定位置开始,截取指定数目的字符517Midb根据指定的位置和字节提取字符518Numberstring将数值转换成汉字数字的文本(隐藏函数)619Proper将文本字符串的首字母及任何非字母字符之后的首字母转换成大写620Replace根据所指定的字符数替换某文本字符串中的部分文本621Replaceb根据所指定的字符数替换某文本字符串中的部分文本(为双字节字符使用)622Pert按照给定的次数重复显示文本723Right从文本字符串的最后一个字符开始截取指定数目的字符724Rightb从文本字符串的最后一个字符开始提取字节数的字符725Search从开始首次找到特定字符或文本字符串位置上特定字符的编号(不区分大小写)826Searchb根据字符所使用的字节数找到特定字符或文本字符串位置上特定字符的编号827Substitute在文字串中用new_text替代old_text828T将数值转换成文本929Text将数值转换为按指定数字格式表示的文本930Trim除了单词之间的单个空格外，清除文本中的所有的空格931Upper将所有英文字母转换成大写字母932Value将表示数字的文字串转换成数字1033Widechar将字符串中的半角（单字节）字母转换为全角（双字节）字符10整整理理日日期期：20132013年年常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy四四、文文本本函函数数目录4!C41.ASC1.ASC 全全角角换换成成半半角角函数定义：将全角(双字节)字符更改为半角(单字节)字符使用格式：ASC(text)格式简义：ASC(目标单元格或文本)参数定义：Text为文本或对包含要更改文本的单元格的引用.如果文本中不包含任何全角字母，则文本不会更改.例1 全角字符结果公式显示学习函数学习EXCEL函数=ASC(B10)（引用单元格）直接输入函数学习ASC函数=ASC(学习函数)(直接输入)目录4!C52.CHAR2.CHAR 对对应应于于数数字字代代码码的的字字符符函数定义：返回对应于数字代码的字符.函数CHAR可将其他类型计算机文件中的代码转换为字符.使用格式：CHAR(number)格式简义：CHAR(目标单元格)参数定义：Number是用于转换的字符代码,介于1到255之间.使用的是当前计算机字符集中的字符.注意事项：操作环境：Macintosh、Windows；字符集：Macintosh字符集、ANSI.例2(1)英文大写字母（直向）：(2)英文小写字母（横向）：A=CHAR(65)A=CHAR(ROW()+56)A#REF!CB=CHAR(66)B或=CHAR（ROW（A65）=CHAR(COLUMN(A1)+64)a=CHAR(97)Cb=CHAR(98)D例3返回excel的首字符代码，然后再转换为字符。101=CODE(excel)（e字符代码）101e=CHAR(B27)（将代码转换成字符）=CHAR(12)&CHAR(11)例4判断下面单元格中的字符串是否全是字母字符Bin_YangFALSE=AND(N(CODE(RIGHT(UPPER(B31),ROW(INDIRECT(1:&LEN(B31)chrisfangTRUE64,90)=1,0)Qee用FALSE简要说明原理：首先把字符串中所有字符都用Upper函数转为大写字母(有利于判断)，然后用right函数将整个字符串依次分段，再用code函数将分段后的首字符代码取出(分段后的首字符就是字符串中的每一个字符)，最后判断代码是否在6590的区间内(大写英文字母的区间)。举这个例子的目的只是为了让大家了解到，code函数经过与其他函数的组合可以突破返回首字符代码的限制，可以有更广泛的应用。例5将中英文合并，并将中英文分开换行显示中文英文1合并换行显示公式苹果apple苹果apple苹果apple=A42&CHAR(10)&B42香蕉banana香蕉banana香蕉banana注：char(10)换行,同时在单元格格式中设置自狮子lion狮子lion狮子lion 动换行)老虎tiger老虎tiger老虎tiger目录4!C63.CLEAN3.CLEAN 删删除除不不能能打打印印的的字字符符函数定义：对从其他应用程序中输入的文本使用该函数,将删除其中含有的当前操作系统无法打印的字符使用格式：CLEAN(text)格式简义：CLEAN(目标单元格)参数定义：text文本是要从中删除不能打印字符的任何工作表信息.注意事项：单元格中软回车会生成代码,虽然肉眼看不到,事实是存在于表格中,此类字符输出至别的文本,可能会产生乱码.用CLEAN函数删除.例6非打印字符结果公式texttext=CLEAN(B55)(CHAR(7).CHAR(10)非打印字符串)目录4!C74.CODE4.CODE 查查看看字字符符代代码码函数定义：返回文本字符串中第一个字符的数字代码.返回的代码对应于计算机当前使用的字符集.使用格式：CODE(text)格式简义：CODE(目标单元格)ABCDEFGHIJ1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859第 2 页，共 13 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy参数定义：Text为需要得到其第一个字符代码的文本.注意事项：操作环境：Macintosh、Windows；字符集：Macintosh字符集、ANSI.通过code函数可以得知，数字09的编码为4857例7判断全角还是半角：文本code()char(code()“社会与法”电视好频道41392“=CODE(B66)(41392代表全角)社会与法电视好频道34=CHAR(CODE(B67)(34代表半角)例8判断中文、英文还是数字：数据判断code()判断公式精英中文或全角英文48811=IF(CODE(B71)64,CODE(B71)255,0),LENB(B175)-LEN(B175)(汉字)目录4!C139.FIXED9.FIXED 给给数数值值附附加加千千位位分分隔隔符符和和小小数数分分隔隔符符函数定义：将数字按指定的小数位数进行取整，利用句号和逗号，以小数格式对该数进行格式设置,并以文本形式返回结果使用格式：FIXED(number,decimals,no_commas)格式简义：FIXED(目标单元格或数字式,保留位数,是否用千位分隔符)参数定义：Number要进行四舍五入并转换成文本字符串的数.Decimals为一数值，用以指定小数点右边的小数位数.为正数时，在小数点第N+1位四舍五入；为0时，在小数部分全部四舍五入；为负数时，在整数第N位四舍五入；省略时，被看作2，在小数点第3位四舍五入No_commas为一逻辑值.用逻辑值来指定是否用,分隔：true时，不用,分隔；为false时，用,分隔注意事项：1.在Microsoft Excel中,Numbers的最大有效位数不能超过15位,但decimals可达到127.2.如果Decimals为负数，则参数number舍入到小数点左边.3.如果省略小数,则假设其值为2.4.如果其值为FALSE或被省略,则返回的文本中和往常一样包含逗号.5.该函数与使用格式菜单上的单元格命令格式化包含数字的单元格的主要区别在于:函数FIXED将其结果转换成文本,而单元格命令格式化的结果仍是数字.例19 给下列1月份销售量添加千位分隔符姓名性别1月结果公式显示赵一女18431.58618,431.6=FIXED(D224,1,FALSE)(保留1位小数和千位符)钱二男18431.58618431.59=FIXED(D225,2,TRUE)(保留2位小数不用千位符)孙三男18431.58618,432=FIXED(D226,0,0)(不保留小数使用千位符)李四女18431.58618431.6=FIXED(D227,1,1)(保留1位小数不使用千位符)周五男18431.58618,400=FIXED(D228,-2,FALSE)(整数位倒第2位四舍五入)目录4!C1410.LEFTLEFTB10.LEFTLEFTB 从从左左边边截截取取指指定定字字符符个个数数 截截取取指指定定字字节节数数的的字字符符 函数定义：LEFT从一个文本字符串的第一个字符开始,截取指定数目的字符.LEFTB从最左边提取指定字节数的字符使用格式：LEFT(text,num_chars)leftb(text,num_bytes)格式简义：LEFT(目标单元格,从内容的左边开始截取指定的字的个数)参数定义：Text是包含要提取字符的文本字符串.Num_charsLEFT指定要由LEFT所提取的字符数.必须大于或等于0，LEFTB按字节指定要由leftb所提取的字符数注意事项：1.如果num_chars大于文本长度,则LEFT返回所有文本.2.如果省略num_chars,则假定其为1.3.Num_bytes按字节指定要由LEFTB所提取的字符数.例20 从左边截取字符串中华人民共和国万岁中华人民=LEFT(B217,4)(指定从内容开始处截取4个字)中国共产党万岁中=LEFTB(B218,2)(指定从内容开始处截取2个字节字符)我爱大好河山我=LEFTB(B219,3)(指定从内容开始处截取3个字节字符)例21 分别截取“”左右字符串11630177QQ.com11630177QQ.com=LEFT(B247,FIND(,B247)-1)=MID(B247,FIND(,B247)+1,10)(截取前的内容（截取某特征前的字符）)例22 将数量与单位分开文本数字单位28克28 克=LEFT(B253,LEN(B253)*2-LENB(B253)(数字)369公斤369 公斤=RIGHT(B253,LENB(B253)-LEN(B253)(单位)56789吨56789 吨ABCDEFGHIJ191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255第 5 页，共 13 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy目录4!C1611.LENLENB11.LENLENB 计计算算文文本本字字符符串串的的字字符符数数 文文教教文文本本字字符符串串的的字字节节数数 函数定义：LEN返回文本字符串中的字符数LENB返回文本字符串用于代表字符的字节数，此函数用于双字节字符使用格式：LEN(text)LENB(同)格式简义：LEN(目标单元格)参数定义：Text是要查找其长度的文本.空格将作为字符进行计数.例23 计算字符串的长度、汉字个数和非汉字个数字符串字符个数字节个数汉字个数非汉字个数公式中华人3630=LEN(B265)(len的结果)中国888A6824=LENB(B265)(lenb结果)人民AAAEXP81026=LENB(B265)-LEN(B265)(汉字个数)中国A,12 881026=2*LEN(B265)-LENB(B265)(非汉字个数)中A 13；。81135注：汉字个数含中文标点符号目录4!C1812.LOWER12.LOWER 所所有有大大写写字字母母转转换换为为小小写写字字母母函数定义：将一个文本字符串中的所有大写字母转换为小写字母.使用格式：LOWER(text)格式简义：LOWER(目标单元格或文本)参数定义：Text是要转换为小写字母的文本.函数LOWER不改变文本中的非字母的字符.例24 将下列字符串的所有英文字母全部改为小写字符串结果公式也可直接输入字母(AAAaaa)AAAaaaaaaaaa=LOWER(B278)aaaaaa=LOWER(AAAaaa)AAAaaaaBaBaBabababababAaA中国Abaaa中国ab目录4!C1913.MIDMIDB13.MIDMIDB 指指定定位位置置截截取取字字符符数数 指指定定位位置置和和字字节节截截取取字字符符数数 函数定义：MID是从一个文本字符串的指定位置开始,截取指定数目的字符；MIDB是根据指定的位置和字节提取字符的个数使用格式：MID(text,start_num,num_chars)；MIDB(text,start_num,num_bytes)格式简义：MID(目标单元格,内容的左边指定开始位置,指定截取的字的个数)参数定义：Text是包含要提取字符的文本字符串.Start_num是文本中要提取的第一个字符的位置.文本中第一个字符的start_num为1,以此类推.Num_charsMID是指定希望MID从文本中返回字符的个数.MIDB是指定希望从文本中按字节返回字符的个数注意事项：1.如果start_num大于文本长度,则函数返回空文本().2.如果start_num小于文本长度,但start_num加上num_chars(num_bytes)超过了文本的长度,则函数只返回至多直到文本末尾的字符.3.如果start_num小于1,则MID返回错误值#VALUE!.4.如果num_chars(num_bytes)是负数,则MID返回错误值#VALUE!.例25 中国安徽省安庆市安庆市=MID(B297,6,3)(从字符串的第6个字开始截取,共截取3个)中国安徽省安庆市安徽省=MID(B298,3,3)(从字符串的第3个字开始截取,共截取3个)中国安徽省安庆市安徽省=MIDB(B299,5,6)(从字符串的第5个字开始截取,共截取6个字节)例26 单元格提取字符中的数字、数字前部分字符串数字部分数字前部分RM-12345 ABCD12345RM=MID(B303,FIND(-,B303)+1,COUNT(-MID(B303,ABCD RH-12 DC12ABCD RHROW($1:$20),1)(数字部分)ABCDE RN-1 DC1ABCDE RN=LEFT(B303,FIND(-,B303)-1)(数字前部分)ABCDE RB-123 AC123ABCDE RB例27 将金额分别填写到相应的单元格内：金额万千百拾元角分5689.01￥5689012456.78￥2456783918.00￥391800106.09￥10609公式：=MID(RIGHT(￥&$B310*100,7),COLUMN()-2,1)金额万千百拾元角分6789.09￥#REF!78909406.78￥40678500185001800ABCDEFGHIJ256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318第 6 页，共 13 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy1026.09￥102609公式：=LEFT(RIGHT(￥&$B316*100,8-COLUMN(A1),1)例28 多行合计逢10进位计算357982876545123427累计进位合计13579541357954=LEFT(RIGHT(SUMPRODUCT($B323:$I325*107,6,5,4,3,2,1,0),9-COLUMN(B289)=MID(&SUMPRODUCT($B323:$I325*10(7,6,5,4,3,2,1,0),COLUMN(A1),1)目录4!C2114.NUMBERSTRING14.NUMBERSTRING 阿阿拉拉伯伯数数字字换换成成汉汉字字函数定义：将数值转换成汉字数字的文本(隐藏函数)使用格式：NUMBERSTRING(number,format)格式简义：NUMBERSTRING(要转换的阿拉伯数字或所在的单元格,汉字表达方式)参数定义：number为需要转换的阿拉伯数字.format用介于13之间的整数指定汉字的表示方式.1.将数值转换成以一、二、三和表示位数的十、百、千表示文本.2.将数值转换成以壹、贰、叁和表示位数的拾、佰、仟表示文本.3.将数值转换成以一、二、三表示文本注意事项：此函数只能手动在单元格输入函数,帮助中也没有些函数的相关项目.例29 将下列数值改为中文大、小写56238 五万六千二百三十八=NUMBERSTRING(B342,1)3089 叁仟零捌拾玖=NUMBERSTRING(B343,2)79862.12 七九八六二=NUMBERSTRING(B344,3)2016.4￥贰仟零壹拾陆元=￥&NUMBERSTRING(B345,2)&元451.45 肆佰伍拾壹=NUMBERSTRING(B346,2)859621.09捌拾伍万玖仟陆佰贰拾壹元零角玖分=IF(B3474,1,2)*10(15-ROW($1:$15),LEN(B618),1,中),2,国)例49 计算字符串中数字的个数赵一0558-2223334441313=SUM(-ISNUMBER(-MID(B626,ROW($1:$101),1)钱二133777888991111=2*LEN(SUBSTITUTE(B626,-,)-LENB(SUBSTITUTE(B626,-,)例50 计算下列字符串中有多少个不重复的字符精英培训excelexcelpxexcelpx9精英培训EXCELexcelpxexcelpx13=SUM(1/(LEN(B630)-LEN(SUBSTITUTE(B630,MID(B630,ROW(INDIRECT(1:&LEN(B630),1),)例51 计算下列字符串：ExcelXP函数与Excel精英培训求字符串中的字母个数12121212=COUNT(AREAS(INDIRECT(MID(B635,ROW(INDIRECT(1:&LEN(B635),1)&1)ABCDEFGHIJ574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638第 11 页，共 13 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy=SUM(ERROR.TYPE(INDIRECT(MID(B635,ROW(INDIRECT(1:&LEN(B635),1)&1)=3)*1)=SUM(LEN(B635)-LEN(SUBSTITUTE(UPPER(B635),CHAR(ROW(65:90),)=SUM(-(CODE(MID(UPPER(B635),ROW(INDIRECT(1:&LEN(B635),1)-77.513)求最后一个字母的位置15=MAX(IF(ERROR.TYPE(INDIRECT(MID(B635,ROW(INDIRECT(1:&LEN(B635),1)&1)=3)*1,ROW(INDIRECT(1:&LEN(B635)用空格将字母与汉字隔开Excel培训Excel 培训 =LEFT(B635,2*LEN(B649)-LENB(B649)&RIGHT(B649,LENB(B649)-LEN(B649)&将数字两位添加一个分隔符1234567812,34,56,78=SUBSTITUTE(TEXT(B653,REPT(#x,ROUND(LEN(B653)/2-1,0)&#),x,)ABCDEFGHIJ639640641642643644645646647648649650651652653654第 12 页，共 13 页文文本本函函数数例例题题标标题题zylkfxy例01将全角字符转换为半角字符例02对应数字代码的英文字母例03返回excel的首字符代码，然后再转换为字符例04判断单元格中的字符串是否全是字母字符例05将中英文合并，并将中英文分开换行显示例06删除非打印字符例07判断字符是全角还是半角例08判断中文、英文还是数字：例09组合文本例10给数值添加美元符号和千位符例11检查两字符串是否完全相同例12查找字符在字符串中的位置例13将256*18258*122并分别显示三段数字(256.18526.122)例14分别提取邮箱用户名和域名例15将单元格内的品名、单价、单位分开例16查找出成绩和分数连在一起字符串中的每人成绩的最高分数例17按原顺序提取不重复数字例18查找字符串的数字的位置，并将数字和汉字分开例19给销售量添加千位分隔符例20从左边截取指定个数的字符串例21分别截取“”左右字符串例22将数量与单位连在一起的字符串中的数量与单位分开例23计算字符串的长度、汉字个数和非汉字个数例24将字符串的所有英文字母全部改为小写例25按指定位置开始截取字符串例26从混合型字符串中提取数字及数字前部分例27将金额中的数字分别填写到相应的单元格内例28多行单个单元格19的数字合计后逢10进位计算例29将数值改为中文大、小写例30将字符串的英文首字母全部改写成大写例31将待替换新字符按指定位置和个数替换掉老字符例32不用“&”符或连接函数replace将一列数字连接起来例33按指定次数重复数字或文本字符串例34将课时和授课内容组合成目录形式，再将目录形式转换讲义形式例35从右边开始按指定的个数截取字符例36查找指定字符在字符串的位置（不区分大小写）例37用新文本替换掉指定第几次出现的旧文本例38计算字符串中某字符的个数和位置例39把带加号的字符串数字分别单个的显示在不同的单元格中例40除去姓名中的空格并将成绩栏中的全角数字和句号替换为半角数字和小数点例41用函数去掉链接例42将日期转换成不同的格式例43除英文单词间的单个空格外，清除所有空格例44将所有英文字母全部改写成大写例45将不同的文本数字转换成数值例46将字符串中的半角字母全部转换为全角字符例47分离不同组合的字母和数字形式字符串的数字与字母例48将各数位上的数字大于4的则替换为“中”，否则替换为“国”例49计算字符串中数字的个数例50计算下列字符串中有多少个不重复的字符例51计算下列字符串字母的个数、最后一个字母的位置、用空格将字母与汉字分开等