SUBSTITUTE函数是Excel常用的文本函数之一，类似于基础操作的查找替换功能，当然，两者亦有不同之处。

SUBSTITUTE函数的用法是：

SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])

最后一个参数，[替换第几个]， 是可以省略的。

---

今天通过几个小栗子，和大家分享下SUBSTITUTE函数的常用技巧和套路。

例子一：将数据中的某个值替换为另一个值

比如将B列数据里的二班，替换为一班。

C2输入公式，并向下复制填充：

=SUBSTITUTE(B2,"二班","一班")

http://ww1/large/005IRek6gw1f5ajwy3pmzj309h05vmxs.jpg

这里没啥好解释的了，就是将B列中单元格中的“二班”全部替换为“一班”。

http://ww1/large/005IRek6gw1f5ak2bkv0ej30gz01edfn.jpg

例子二：隐藏手机号的中间5位

C2输入公式，并向下复制填充：

=SUBSTITUTE(B2,MID(B2,4,5),"*****")

http://ww2/large/005IRek6gw1f5ajxjcxi9j308g05ut9a.jpg

先使用MID函数取得B列号码中的中间五位，再用字符串“*****”替换掉这部分内容。

http://ww4/large/005IRek6gw1f5ak269dz2j30gz01edfn.jpg

例子三：含单位的数据求和

B7输入公式：

=SUMPRODUCT(SUBSTITUTE(B2:B6,"人",)*1)

http://ww1/large/005IRek6gw1f5ajyfxxyhj308006xgly.jpg

先用SUBSTITUTE替换掉B列单元格中的“人”，得到文本型数字，乘以1后变成可以计算的数值，再用SUMPRODUCT函数进行数组求和。

http://ww1/large/005IRek6gw1f5ak21cnwej30gz01edfn.jpg

例子四：数据分列，将B列数据按顿号进行分列。

C2单元格输入公式横向拖动，并向下复制填充。

=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))

http://ww1/large/005IRek6gw1f5ajzebxovj30dt066gmj.jpg

REPT(" ",100)

先使用REPT函数，将空格重复100次，得到100个空格。

SUBSTITUTE($B2,"、",REPT(" ",100))

使用SUBSTITUTE函数将姓名中的的间隔符号（、）顿号替换为100个空格。

MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100)

再使用MID函数，依次从这个带有空格的新字符串中的第1、第101、第201位……开始截取长度为100的字符。

这样得到的字符串是带有多余空格的，因此再使用TRIM函数将多余空格删除掉，OK了。

http://ww2/large/005IRek6gw1f5ak1cwgsej30gz01edfn.jpg

例子五：混合文本中，计算人数个数

http://ww3/large/005IRek6gw1f5bbnnrevbj30cn044jrr.jpg

B2公式：

=LEN(A2)-LEN(SUBSTITUTE(A2,"、",)) 1

LEN(A2)取得A2字符串的长度。

LEN(SUBSTITUTE(A2,"、",)) 1，替换掉人名之间的间隔符，也就是顿号，再用LEN计算该值的长度，最后加1，是因为最后一个人名没有顿号，需要补上。

用A2数值原有的长度减去被替换掉人名之间间隔符的长度，也就是人名的个数。

http://ww4/large/005IRek6gw1f5ak67b85aj30gz01edfn.jpg

例子六：混合文本中，计算数值最大值。

http://ww4/large/005IRek6gw1f5ak3rrxeoj30ek05pdgf.jpg

B2数组公式：

=MAX((SUBSTITUTE(A2,ROW($1:$98),)<>A2)*ROW(1:98))

SUBSTITUTE(A2,ROW($1:$98),)<>A2

依次将数值1到98从A2替换为空，然后把替换后的结果和被替换值（1-98）进行比较  ，如果不相等，则证明A2中存在该数值。

最后将证明的结果，真和假，也就是逻辑值TRUE和FALSE，乘以被替换的值(1-98)，用MAX函数从中取得最大值。

http://ww3/large/005IRek6gw1f5ak6ctt6mj30gz01edfn.jpg

例子七：计算某个值在某个范围的最大连续次数

http://ww4/large/005IRek6gw1f5ak51p90wj30cx07dq3b.jpg

B2数组公式：

=MAX((SUBSTITUTE(PHONETIC(A2:A9),REPT("A",ROW(1:9)),)<>PHONETIC(A2:A9))*ROW(1:9))

PHONETIC(A2:A9)

将A2:A9的文本值黏合成一个值，以便SUBSTITUTE函数进行操作。

REPT("A",ROW(1:9))

把“A”重复1到9次。

SUBSTITUTE(PHONETIC(A2:A9),REPT("A",ROW(1:9)),)<>PHONETIC(A2:A9)

思路回到示例6，SUBSTITUTE函数将REPT函数的结果，在PHONETIC函数结果里替换掉，然后和PHONETIC函数原值进行比较。如果后者存在替换值，则被替换掉，和原值不相等，返回FALSE，否则返回TRUE。

最后依然把 逻辑值TRUE和FALSE，乘以ROW(1:9)，用MAX函数从中取得最大值。

http://ww3/large/005IRek6gw1f5ak6hxgxqj30gz01edfn.jpg

转载请注明出处，谢谢。更多精彩，请持续关注呗。