文本函数1课件

文本函数left、right、mid1substitute、replace、rept2len、find3char、code4text51left、right、mid1．用法讲解及示例2．作业返回一LEFT和LEFTB函数二RIGHT和RIGHTB函数三MID和MIDB函数2使用LEFT和LEFTB函数返回左边字符

这两个函数都有两个参数。Text表示包含要提取字符的文本字符串，num_chars指定函数LEFT所要提出的字符数，num_bytes按字节指定。参数num_chars和num_bytes必须大于或等于0。等于0返回值为空；小于0，则返回错误值；省略默为1。具体见示例1。

LEFT和LEFTB函数作用是基于所指定的字符数返回文本字符串中的第一个或前几个字符。所不同的是LEFT以字符为单位，而LEFTB以字节为单位。这两个函数表达式如下：LEFT(text,num_chars)LEFTB（text，num_bytes)3示例14例题例1：现要求从人名中提取出姓氏进行称呼的输入，要求输入为“某某市某先生/女士”。具体步骤如下：

Step1:制作如图所示表格。

Step2：要想得到第一个人的称呼，即可在单元格D2中输入公式：

=CONCATENATE(C2,"市",LEFT(A2,1),IF(B2="男","先生","女士"))

回车后则可得到第一个人的称呼。（concatenate函数实现合并字符串）

step3:拖拽公式自动填充。5使用RIGHT和RIGHTB函数返回右边字符

RIGHT和RIGHTB函数作用是基于所指定的字符数返回文本字符串中的最后一个或多个字符。所不同的是RIGHT以字符为单位，而RIGHTB以字节为单位。这两个函数表达式如下：RIGHT(text,num_chars)RIGHTB（text，num_bytes)

这两个函数都有两个参数。Text表示包含要提取字符的文本字符串，num_chars指定函数所要提出的字符数，num_bytes按字节指定。参数num_chars和num_bytes必须大于或等于0。等于0返回值为空；小于0，则返回错误值；省略默为1。具体见示例。问题1：LEFT、LEFTB函数和RIGHT、RIGHTB函数有什么异同点？6

MID和MIDB函数作用是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。所不同的是MID以字符为单位，而MIDB以字节为单位。这两个函数表达式如下：MID(text,stsrt_num,num_chars)MIDB（text，stsrt_num,num_bytes)使用MID和MIDB函数查找特定的字符

这两个函数都有3个参数。Text表示包含要提取字符的文本字符串，start_num表示文本中要提取的第一个字符的位置，文本中第一个字符的位置为1，以此类推。num_chars指定函数从文本中返回的字符个数，num_bytes按字节指定。参数num_chars和num_bytes必须大于或等于0。等于0返回值为空；小于0，则返回错误值；省略默为1。具体见示例。7示例2水果英文名售量（箱）香蕉Banana50苹果Apple67

公式结果说明=MID(A2,1,2)香蕉从第一个字符开始，返回两个字符=MID(B2,3,3)nan从第三个字符开始，返回三个字符=MID(B2,3,6)nana要返回的字符大于总长度，则返回直到最后的字符=MID(C2,3,2)

start_num超过字符长度，返回空值=MID(B3,0,2)#VALUE!start_num小于1，返回错误值=MID(B3,1,-1)#VALUE!num_chars小于0，返回错误值=MIDB($A2,1,2)香从第一个字节开始，返回两个字符=MIDB($B2,3,3)nan从第三个字节开始，返回三个字符=MIDB($B2,3,6)nana要返回的字节大于总长度，则返回直到最后的字节=MIDB($C2,3,2)

start_num超过字节长度，返回空值=MIDB($B2,0,2)#VALUE!start_num小于1，返回错误值=MIDB($B2,1,-1)#VALUE!num_chars小于0，返回错误值8例题2在网上注册一些表格时经常要填写身份证号码，填写完毕系统会自动生成你的出生日期。现以某公司员工为例，根据身份证号码提取员工出生年月日，具体的操作步骤如下：

Step1:制作如图所示表格。

Step2:根据身份证号码提取员工出生年月日，在单元格C2中输入公式“=MID(B2,7,8)”，回车即可。

Step3:拖拽公式自动填充。9substitute、replace、rept返回一substitute二replace三rept10

使用SUBSTITUTE函数替换文本

SUBSTITUTE函数的作用是在文本字符串中用new_text代old_text,其表达式如下：

substitute（text,old_text,new_text,instance-num)

从表达式中可以看出该函数有4个参数。text为需要替换其中字符的文本，或对含有文本的单元格的引用；

old_text为需要替换的旧文本；new_text为用于替换old_text的文本；instance-num为一个数值，用来指定以替换第几次出现的。该函数的具体应用见示例3。11示例3水果香蕉4854654substitute

公式结果说明=SUBSTITUTE(A1,"香蕉","苹果")水果苹果用“苹果”替换“香蕉”=SUBSTITUTE(B1,4,9,2)4859654用“9”替换第二个“4”=SUBSTITUTE(B1,4,9)9859659用“9”替换的所有的“4”=SUBSTITUTE(C1,"s","S",1)Substitute用“S”替换第一个“s”=SUBSTITUTE(C1,"s","T")TubTtitute用“T”替换的所有的“s”=SUBSTITUTE(B1,4,9,0)#VALUE!instance_num小于1，返回错误值12

使用replace函数替换文本replace函数的作用是使用new_text根据指定的字符数替换old_text中的部分文本,其表达式如下：replace（old_text,start_num,num_chars,new_text)从表达式中可以看出该函数有4个参数。old_text为需要替换其部分字符的旧文本；start_num表示要用new_text替换的old_text中字符的位置；num_chars表示函数replace使用new_text替换old_text中字符的个数；该函数的具体应用见示例4。问题2：replace和substitute函数有什么异同点？13示例4水果香蕉4854654replace2004

公式结果说明=REPLACE(A1,3,2,"苹果")水果苹果用“苹果”替换“香蕉”=REPLACE(B1,1,3,"*")*4654用“*”替换前三位字符=REPLACE(B1,4,9,"Excel")repExcel用“Excel”替换第四位以后的字符=REPLACE(A2,4,1,"5")2005用“5”替换“4”=REPLACE(A2,-2,3,"Excel")#VALUE!start_num小于0，返回错误值=REPLACEB(A1,3,2,"苹果")水苹果香蕉以字节为单位，用“苹果”替换2个字节=REPLACEB(B1,1,3,"*")*4654用“*”替换前三个字节=REPLACEB(C1,4,9,"Excel")repExcel用“Excel”替换第四位以后的字节=REPLACEB(A2,4,1,"5")2005用“5”替换“4”=REPLACEB(A2,-2,3,"*")#VALUE!start_num小于0，返回错误值14

例题例题3

某市的电话号码要升位，在原来的电话号码前加一个“8”，现使用REPLACE函数完成某公司几位员工的电话号码的升位。具体步骤如下：1)制作如图所示的表格；2)计算升位的电话号码。输入公式“=REPLACE(B2,1,3,”0218”)”，回车后得到“李宁”的新的电话号码；3)拖拽公式自动填充。姓名性别原电话号码新电话号码李宁男0216845231张明男0216562623陈晓女0216984520赵强男0216666452刘玉梅女0216000213邵刚男0216444852李燕女021699998815

使用rept函数复制文本

rept函数的作用是按照给定的次数复制显示文本，其表达式如下：

rept（text,num_times)

其中text表示需要重复显示的文本，num_times为指定文本重复次数的正数。如果num_times等于0，返回空值；如小于0，返回错误；若为小数则截尾取整。rept函数的结果不能大于32767个字符，否则返回错误。具体应用见示例5。16示例5公式结果说明=REPT("函",4)函函函函将“函”复制了5次=REPT("♂",3)♂♂♂将“♂”复制了3次=REPT("*",7)*******将“*”复制了7次=REPT("*",0)

num_times为0，返回空值=REPT("★",5.6)★★★★★num_times为小数，截尾取整=REPT("★",-2)#VALUE!num_times小于0，返回错误值17例题例题4现有部分销售人员某种化妆品如下图，在单元格中绘制出销售量除以50的条形图。具体的操作步骤如下：step1：制作表格；step2：选中单元格，单击【插入】中【符号】菜单项打开【符号】对话框，切换到【符号】选项卡中，在【字体】下拉列表中选择【普通文本】，在【子集】下拉列表中选择【方块元素】；step3：输入格式“=REPT($I$12,INT(G13/50))”回车得到销量条图。姓名销量条形图▂李宁216▂▂▂▂

张明356▂▂▂▂▂▂▂

陈晓197▂▂▂

赵强296▂▂▂▂▂

刘玉梅456▂▂▂▂▂▂▂▂▂

邵刚280▂▂▂▂▂

李燕320▂▂▂▂▂▂

18len、find、char、code一二三char、codelen、findtext19使用len和lenb函数计算字符串长度len和lenb函数作用是返回字符串长度。表达式如下：len(text)lenb(text)text表示需要查找其长度的文本，值得注意的是空格也作为字符计算在内。具体应用见示例6。20示例6水果英文名售量（箱）香蕉Banana50苹果Apple67

公式结果说明=LEN(A2)2以字符为单位，返回“香蕉”的长度=LEN(B2)6以字符为单位，返回“Banana”的长度=LEN(C3)2以字符为单位，返回“67”的长度=LEN(D3)00空单元格的长度为0=LENB(A2)4以字节为单位，返回“香蕉”的长度=LENB(B2)6以字节为单位，返回“Banana”的长度=LENB(C3)2以字节为单位，返回“67”的长度=LENB(D3)00空单元格的长度为021使用find和findb函数计算字符串长度find和findb函数作用是查找一个字符串在另一个字符串出现的位置。表达式如下：find(find_text,within_text,start_num)findb(find_text,within_text,start_num)find_text表示需要查找的字符串，within_text是包含要查找字符串的字符串,start_num用以指定开始进行查找的字符参数中的首字符是编号为1的字符，如果忽略则假设其为1。具体应用见示例7。22示例7水果英文名售量（箱）香蕉Banana50苹果Apple67桔子Orange45

公式结果说明=FIND("苹",A3)1从第一个字符开始找，该字符在第一位=FIND("p",B3)2从第一个字符开始找，第一个“p”在第二位=FIND("o",B4)#VALUE!区分大小写，找不到“o”，返回错误值=FIND("a",B2,3)4从第三个字符开始找，第一个“a”在该字符串的第四位=FIND(5,C4)2数字可以不用引号，返回“5”所在的位置=FIND("",C3)1find_text为空，则返回第一个字符所在的位置=FIND("c",B4)#VALUE!找不到该字符，返回错误值=FIND(5,C2,0)#VALUE!start_num参数小于1，返回错误值=FIND(5,C2,3)#VALUE!start_num参数大于该字符的总长度，返回错误值=FINDB("果",A3)3以字节为单位,一个汉字占两个字节，所以返回3=LEFTB("a",B3)#VALUE!区分大小写，找不到“a”，返回错误值=FINDB("a",B2,3)4一个英文字母占一个字节，从第三个字符查找，第一个“a”在第三位=FINDB("c",B4)#VALUE!找不到该字符，返回错误值=FINDB(5,C2,-1)#VALUE!start_num参数小于1，返回错误值=FINDB(5,C2,3)#VALUE!start_num参数大于该字符的总长度，返回错误值23char、code

char函数的作用是将数字代码转换成相应的字符，code函数的作用是将文本字符串中的第一个字符转换成数字代码，这两个函数表达式如下：

char(number)

code(text)number表示用于转换的数字代码，介于1到255之间。

char函数和code函数的区别与联系是什么？答：二者可以相互转换，即CHAR函数中的参数可以作为CODE函数中的结果，不同的是CHAR函数中的参的取值在1到255之间。而CODE函数可以返回中文字符的代码，其数值会超过255。24char函数char函数公式结果说明=CHAR(48)0返回字符表中第48个字符=CHAR(25)返回字符表中第25个字符=CHAR(68)D返回字符表中第68个字符=CHAR(255)ÿ返回字符表中第255个字符=CHAR(3.2)返回字符表中第3个字符=CHAR(52.6)4返回字符表中第52个字符=CHAR(256)#VALUE!大于255，返回错误值=CHAR(-2)#VALUE!小于1，返回错误值25code函数code函数公式结果说明=CODE("A")65返回字符A对应的数字代码=CODE("a")97返回字符a对应的数字代码=CODE("ABDF")65返回第一个字符A对应的数字代码=CODE("5")53返回5对应的数字代码=CODE("")32返回空格对应的数字代码=CODE(A)#NAME?输入不是字符