EXCEL中SUBSTITUTE函数与TRIM函数的组合妙用

1、在制作EXCEL表格时，有时由于手误或者前后的初衷不一致，导致输入的数据形式不够理想，想修改时，数据众多，手动修改不仅麻烦而且耗时且容易出错，如能找到共性规律，用公式来做就变得方便了。比如下图中的目录，根目录、子目录间用“-”分隔，但一个“-”看上去有时会误为“一”，所以全部以两个“-”，即“--”来分隔，但由于操作时疏忽，有的输成了“---”，有的输成了“-”，不统一，现在想统一替换为“--”。

2、直接使用替换，“---”变成“--”好处理，但“-”想变成“--”就难了，因为它会将相同的“-”都替换成“--”，变得更乱了，况且这里只是举例，事实上并不知道最多的有几个“-”。

3、这时想到用公式来做，EXCEL中有个替换函数SUBSTITUTE，此函数是将字符串中的指定字符（串）用新的字符（串）来替换。

4、但是使用SUBSTITUTE函数，也同样面临这个问题，无法将多个不同的字符转换成指定的字符，如果能有个函数能将多个不同的字符全部转换成指定个数的字符就好了，这时想到利用TRIM函数，此函数是删除字符间多余的空格，各字符间原来不管有多少个空格，只保留一个空格，原来没有的依然没有。

5、因此可以借助TRIM的特性，先用替换函数SUBSTITUTE将指定字符转换为空格：=SUBSTITUTE(A2,"-"," ")

6、再用TRIM函数去掉多余的空格：=TRIM(SUBSTITUTE(A2,"-"," "))

7、然后再替换，将空格替换为指定的字符：=SUBSTITUTE(TRIM(SUBSTITUTE(A2,"-"," "))," ","--")，这就是想要的结果。

8、但是如果原来字符串本来就有空格，直接这样用，也会同时替换为指定字符串，出现非期望结果。

9、因此先要将空格变成其它字符（所有字腿发滏呸符串皆没有的字符）：=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2," ","#"),"-"," "))," ","--")

10、等转换目的达到后，再反替换回来：=SUBSTITUT苇质缵爨E(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,&鳎溻趄酃quot; ","#"),"-"," "))," ","--"),"#"," ")

11、另外要注意下，TRIM函数对于字符串中最前最后的空格是统统去除的，如果有这种情况，还要再结合LEFT、RIGHT函数使用，达到最终效果。