【原】折腾了3天，我整理出史上最全的文本函数用法案例，实用到爆！（建议收藏）-第二期

每天一点小技能

职场打怪不得怂

编按：昨天，我们学习了九大文本函数系列案例用法的第一期——《最魔性的文本函数，75%的Excel人都被它坑过，或许下一个轮到你……》。今天，我们继续学习文本函数！第二期，小E给大家带来的是REPLACE和SUBSTITUTE两大函数！它们被隐藏起来的秘密即将被公布……

各位读者好，小可今天继续九大文本函数的系列教程！

大多数文本函数的语法虽简单，却暗藏着许许多多结构语法不介绍的潜规则。

比如，REPLACE函数第三参数的省略妙用、SUBSTITUTE函数一二三参数鲜为人知的【秘密】……是不是迫不及待了呢~~

快跟小编一起来学习吧！

本期目录先呈上~~~

目录先呈上：

一、REPLACE

1.1替换字符

1.2插入字符

1.3删去字符

1.4分段显示

二、SUBSTITUTE

2.1统计每小组人数

2.2计算平均分

2.3删除补位的0值

一、REPLACE

知识乐园：
REPLACE(被替换字段，开始位置，替换长度，替换字段)。
敲黑板：如果第三参数为0或省略参数，可以实现类似插入字符串的功能!

1.1替换字符

要求：如图，B列是电话,为了保护个人信息，现需要将电话号码中的第4-7位号码隐藏。

微信扫码下载练习课件

方法：在C3单元格输入公式：=REPLACE(B3,4,4,"****")，向下复制填充公式。

解读：从电话号码的第4个字符开始，用字符串“****”替换掉其中的4个字符。

1.2插入字符

要求：A列是姓名加数字，现需要将姓名与数字在一个单元格分行显示，即完成<Alt+Enter>的强制换行效果。

方法：在F3单元格输入公式：=REPLACE(E3,LENB(E3)-LEN(E3)+1,,CHAR(10))，向下复制填充公式。

解读：LENB(E3)-LEN(E3)+1部分，先用LENB(E3)-LEN(E3)算出双字节字符的长度（即汉字的长度），再+1得到汉字后面的位置；REPLACE函数省略第三参数，可在汉字后面插入字符CHAR(10)，CHAR（10）是强制换行的代码。
小提醒：若正确输入公示后木有得到正确结果，请将单元格格式设置为自动换行。

1.3删去字符

要求：如图，A列是工号，现在要求去掉“-”及其后面的内容。

方法：在B14单元格输入公式：=REPLACE(A14,FIND("-",A14),99,"")，向下填充复制公式。

解读：FIND("-",A14)部分，是查找出"-"在文本中的位置，再用REPLACE把从"-"起和其以后的内容全部替换为""(即空)。

1.4分段显示

要求：将F列的电话号码，分段显示到G列。例如，“18285756946”显示成“1828575 6946”。

方法：在G14单元格输入公式：=REPLACE(REPLACE(F14,4,," "),9,," ")，向下复制填充公式。

解读：这是REPLACE函数的嵌套。先在电话号码的第4个位置插入" "（空格），完成第一次分段；再在修改后的文本的第9个位置插入一个" "（空格），完成第二次分段。

二、SUBSTITUTE

知识乐园：
（1）该函数区分大小写查找，当第一参数源字符串中没有包含第二参数指定的字符串时，函数结果返回源文本。
例如：将B20中的“excel”替换为“123”，由于B20单元格字符串中没有“excel”只有“Excel”,所以结果返回源文本。

（2）当第三参数为空文本或者是省略该参数的值而只保留参数之前的逗号时，相当于将需要替换的文本删除。
例如：在C21单元格输入公式：=SUBSTITUTE(B21,"教程",)。结果只返回字符串“Excel”。

（3）当第四参数省略时，源字符串中所有与第二参数相同的文本都将会被替换。如果第四参数指定次数时，只有指定次数的第二参数文本会被替换。

① 省略第四参数，将文本中所有“教程”换成“excel”。

② 第四参数指定为2，则B23单元格中第二次的“教程”替换为“excel”。

2.1统计每小组人数

要求：统计出每一小组的人数。

方法：在C3单元格输入公式：=LEN(B3)-LEN(SUBSTITUTE(B3,"、",""))+1，向下复制填充公式。

解读：利用SUBSTITUTE函数将文本中所有的“、”换为""（空），再用LEN函数分别得出源文本长度和删除“、”后的文本长度，两者相减，得出文本中“、”的个数，再+1就是每组人数啦~

2.2计算平均分

要求：F列是每个人的分数，但是有的分数后面有单位，有的木有，需要在F9单元格计算出所有人的平均分。

方法：=AVERAGE(--SUBSTITUTE(F3:F8,"分",))，按<Ctrl+Shift+Enter>三键结束。

解读：用SUBSTITUTE函数将“分”换为””(空)；接着用“--”减负进行运算，将所有文本数字转换为数值型数字；最后用AVERAGE函数计算出F列的平均分。

2.3删除补位的0值

要求：A列是以逗号（，）隔开的“00”格式的数字，现需要删除多余补位的0。

方法：在B13单元格输入公式：=MID(SUBSTITUTE(A13,",0",","),1+(LEFT(A13)="0"),99)，向下填充复制公式。

解读：SUBSTITUTE(A13,",0",",")部分，是将文本中所有的“，0”替换为“，”，即删去了除第一位数就是0以外的所有0。1+(LEFT(A13)="0"部分，表示若第一位数不是0，1+FALSE=1，则MID函数从去掉0后的文本的第1位开始提取出99个字符串，即提取出替换后所有的字符串；若第一位数是0，1+TRUE=2，MID函数则从替换后的文本的第二位数开始提取出99个字符串，即提取出除第一个0后的所有字符串。

今日分享暂时到这里啦，有木有涨知识呢~~~

读者老爷们，小可与您相约，第三期见！！！