SUBSTITUTE函数的几个妙用

马震安

1.规避带单位统计出错问题

以图1为例，要计算带单位数字的合计结果，使用公式“=SUMPRODUCT（SUBSTITUTE （B2：B9，"万元"，""）*1）&"万元"”，就会规避带单位统计出错的问题，能够顺利完成数据统计（图1）。

公式解释：先用SUBS TITUTE函数把B2：B9单元格区域中的“万元”替换成空，乘以1，将提取出的文本型数字转换成数字型数字，再利用SUMPRODUCT函数进行求和，最后将求出的各和值用“&”连接符加上单位。

当然，这个公式也可换成“=SUM（--SUBSTITUTE（82：B9，“万元”，“"万元"”，按下“Ctrl+Shift+Enter”组合键使函数生效（图2）。

2.隐藏数字

在信息发布时，为了保护隐私，有时需要将电话或身份证中某几个数字加以隐藏，这时使用SUBSTITUTE函数就非常方便。例如，使用如圖所示的公式“=SUBSTITUTE（B2，MID（B2，12，5），"*****"）”，即可实现隐藏身份证号码数字的目的（图3）。

公式解释：用MID函数从身份证号的第12位开始提取5位，再利用SUBSTITUTE函数将身份证号中的这5位数字替换成“*****”。

3.名单人数统计

在登记参会人员时，有可能将多个人名存放在一个单元格中，统计每个单元格中所包含的人数，同样也可以使用SUBSTITUTE函数。如图所示，只需利用公式“=LEN（B2）-LEN（SUBSTITUTE（B2，"、"，""））+1”即可完成统计（图4）。

公式解释：先利用LEN函数计算单元格原来值的长度，再用LEN函数计算由SUBSTITUTE函数将单元格中的“、”替换成空格后得到的文本长度，两个长度相减后再加1，得到的结果就是单元格中所包含的人数了。