Excel SUBSTITUTE函数的用法

在前面的文章中，有多次提到SUBSTITUTE的使用，那么今天我们专门以这篇文章来讨论一些SUBSTITUTE的使用方法和技巧。

SUBSTITUTE函数可以对指定的字符串进行替换。

上一篇的文章中提到了用TEXT中“0”做为占位使用。比如123456要变成0000123456，可以使用TEXT(123456,”0000000000”)来得到。那反过来，要去掉0000123456中占位的“0”，，怎么操作呢，其实很简单，只要做一次数值的计算就 可以了，假设A3=0000123456,即--A3=123456,A3*1=123456，A3+0=123456；

但往往实际使用中问题就没有那么简单了。假设A4=09,03,11,40,06,02,00,00，想要 去掉这些多余的“0”，要如何操作？其实也不复杂：

MID(SUBSTITUTE(A4,",0",","),1+(LEFT(A4)="0"),99)= 9,3,11,40,6,2,0,0；

简单的讲解一下公式，仔细的看，公式中SUBSTITUTE需要替换的文本是“,0”（这是逗号和零），这样的话，除了第一个数09外，其它以0开头的都被替换没有了。

(LEFT(A4)="0")是一个技巧，其实你可以把它看作是一个判断IF函数的简写，它的作用就是判断第一个字符是否为"0"，在Excel的计算中，TRUE是被当做1来处理的，同 样FALSE是被当做0来 处理，所(LEFT(A4)="0")返回的结果是TRUE，它和1相加，则为2；

然后用MID从第2位开始取值，最后取99个值，你也可以写199，都不 影响，如果你想把这个取值长度精确化，那么你可以使用LEN(SUBSTITUTE(A4,",0",","))来计算它。

上面这段文字需要慢慢的领会。至少有几个非常实用的技巧。

SUBSTITUTE包含的内容非常的 多，可以收藏这篇文章后慢慢的琢磨。

再说说SUBSTITUTE的自动换行。假设A5=”我爱你，我爱她，我爱大家“，通过以下公式进行换行

=SUBSTITUTE(A5,",",""&CHAR(10))

输完公式，记得单元格设置自动换行。其中CHAR(10)是换行符。

用SUBSTITUTE可以来统计单元格中某个字符出现的次数。比如A6=”abcdabcdaa”，先用SUBSTITUTE把要统计的字符替换成空 ，然后用原有的单元格长度减去剩下的长度，即为字符出现的次数。

LEN(A6)-LEN(SUBSTITUTE(A6,"a",))=4

下面这个应用会比较复杂一些。我会具体的解释一下。

假设单元格A7的值为99分,98分,97分,89，我们要对其中的数字求和。公式如下 ：

=SUMPRODUCT(--(0&TRIM(MID(SUBSTITUTE(A7,"分,",REPT(" ",100)),ROW($1:$9)*100-99,99))))=383

这里的难点就是要把单元格里的文字去掉，然后转换成数组，再对数组求和。

首先用SUBSTITUTE把“分,”（分和逗号），替换掉空白，这边是用了100个空（REPT(" ",100)）;

然后用MID分别取进行取值，这边有一个技巧，ROW($1:$9)*100-99的意思是从{1;101;201;301;401;501;601;701;801}开始对前面替换的数组开始取值，取99个值。这实际上就是运用了数组。

这里再补充一下，为什么用100个空?主要的目的是把A7单元格里包含的数值想象成位数比较多。当然也可只替换成10个空，公式就要再改一改。

所以MID取值范围是与前面替换的空白相关联的。

再使用TRIM去掉空白后，再用“--”转换成数值。

最后用SUMPRODUCT对数组求和。

SUBSTITUTE复杂应用

这个公式中应用到数组，可能会比较难明白。后续再详细的讲解。

特别要说明的是SUMPRODUCT也是一个非常非常强大的函数，它的计算速度非常的快。在VBA写程序的时候，为了提高程序运算的速度，我们 也会用到它。