substitute函数(substitute函数替换多个符号)

前沿拓展：

除了使用查找替换功能来批量替换字符，还可以使用替换类函数将字符串中的部分或全部内容替换成新的字符串。

如图 5-74 所示，A 列是一些从系统导出的内容，同一个单元格内包含有科目代码和各级科目名称，不同项目之间使用“/”间隔，需要将这些内容分别拆分到右侧各列中。

substitute函数(substitute函数替换多个符号)

B2 单元格输入以下公式，将公式**填充到 B2:E25 单元格区域。

=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMN(A1)*99-98,99))

公式中用到了多个文本类函数的组合。

REPT 函数的作用是根据指定的次数重复显示字符。

TRIM 函数的作用是清除字符中的多余空格。

COLUMN 函数的作用是返回参数的列号，如果省略参数，则返回公式所在单元格的列号。本例中的“COLUMN(A1)*99-98”部分，先使用 COLUMN 函数返回 A1 单元格的列号1，再用 COLUMN 函数的结果乘以 99 减 98，即 1*99-98，结果仍然是 1。当公式向右**时，COLUMN 函数会依次得到 B1、C1……的列号，再将这些列号乘以 99 减 98，即相当于 2*99-98、3*99-98……，最终得到按 99 递增的序号 1、100、199……，以此作为 MID函数的第二参数。

SUBSTITUTE 函数的作用是将字符串中的指定字符替换为新的字符，函数第一参数是需要替换字符的文本或单元格引用。

第二参数指定要替换哪些字符。

第三参数指定要将原有字符替换成什么内容，如该参数为空文本或仅保留参数之前的逗号时，相当于将需要替换的字符删除。

第四参数是可选参数，当第一参数中包含有多个要替换的字符时，该参数指定要替换第几个。省略该参数时，则表示全部替换。

公式中的“SUBSTITUTE($A2,"/",REPT(" ",99))”部分，先使用“REPT(" ",99)”将空格重复 99 次，最终得到 99 个空格。再使用 SUBSTITUTE 函数将 A2 单元格中的每一个分隔符“/”都替换为 99 个空格，使其变成以下样式的新字符串。

"119301 往来中转通赔"

接下来使用 MID 函数，从 SUBSTITUTE 函数返回的字符串中提取字符，提取的起始位置是“COLUMN(A1)*99-98”得到的序号 1，提取长度为 99 个字符，结果如下。

"119301 "

最后使用 TRIM 函数清除字符串中的多余空格，得到科目代码“119301”。

当公式向右**时，MID 函数分别从 SUBSTITUTE 函数返回字符串中的第 1 位、第100 位、第 199 位……依次提取出 99 个字符，并使用 TRIM 函数清除多余空格，最终得到不同的科目代码和科目名称。

公式中的 99 可以是其他一个较大的数字，目的是增加原有字符串中各个科目之间的间隔宽度，以便于 MID 函数分段截取出带空格的字符。

推荐阅读北京大学出版社Excel数据处理与分析应用大全

substitute函数(substitute函数替换多个符号)

拓展知识：