Excel中的替换技巧，SUBSTITUTE函数使用技巧详解

各位老铁们好，相信很多人对Excel中的替换技巧，SUBSTITUTE函数使用技巧详解都不是特别的了解，因此呢，今天就来为大家分享下关于Excel中的替换技巧，SUBSTITUTE函数使用技巧详解以及的问题知识，还望可以帮助大家，解决大家的一些困惑，下面一起来看看吧！

在Excel中，说到变化，你首先想到的是什么功能？

如果你对函数了解得比较多，你可能会想到REPLACE函数和Substitute函数。

这两个函数的区别在于REPLACE函数侧重于位置点，而Substitute函数侧重于内容替换。

首先，我们来看看SUBSTITUTE函数的基本用途和含义。

SUBSTITUTE函数的目的是替换指定的字符。

函数定义：

替换（文本，旧文本，新文本，[instance_num]）

text：字符串，可以是文本内容或对包含文本的单元格的引用。

old_text：要替换的字符

new_text : 替换的字符内容

Instance_num：可以省略，代表替换文本中的old_text。

现在我们已经了解了它的基本语法，让我们向您展示实际的应用程序。

案例一、姓名更换

我们将单元格A1内容中的“彩虹”替换为“学习”，我们可以输入公式：

=SUBSTITUTE(A1,’彩虹’,’学习’)

情况2：删除字符之间的空格

要删除A 列列表中间的空格，我们可以输入公式：

=替换(A2,”,”)

情况3：手机号码中间4位显示“*”

有时候为了保护我们个人的手机信息，我们常常需要隐藏手机号码的中间部分。在这种情况下，我们可以使用替换功能。

在C2中输入公式：

=替换(B2,MID(B2,4,4),’****’)

MID函数取手机号码的中间4位数字，然后使用Substitute函数替换它们。

案例4：使用Vlookup函数搜索

当我们使用Vlookup函数进行查找时，可能会遇到看起来一样但找不到的情况。这时候我们就需要考虑是否有空格。我们可以将它与替代函数一起使用。

=VLOOKUP(SUBSTITUTE(F2,’ ‘,”),B1:D15,3,0)

案例5、统计部门人数

现在每个部门都有一些成员，每个成员之间用“，”分隔。我们想要统计每个部门有多少人。我们可以使用Len 函数和Substitute 函数来实现这一点。

=LEN(B2)-LEN(替换(B2,’,’,”))+1

Len(B2)表示计算B2的总长度

SUBSTITUTE(B2,’,’,”))表示将人员列表中的’,’全部替换为空；

然后使用Len函数来统计替换字符的长度。两者相减即可统计‘、’的数量，最后加1即可得到人数。

案例6：用单位对文本求和

以下每位员工的销售额带有后缀“元”。如果我们要计算所有员工的总销售额，不能直接使用SUM函数。这种情况下，我们可以使用SUBstitute函数来替换字符，然后计算

=SUMPRODUCT(SUBSTITUTE(B2:B10,’元’,”)*1)

注意：

1、由于SUBSTITUTE(B2:B10,’yuan’,”)是数组计算，所以我们不能使用SUM函数，所以使用了SUMPRODUCT函数。如果使用SUM 函数，则必须按Ctrl+Shift+Enter 结束它。

2、由于SUBSTITUTE(B2:B10,’yuan’,”)替换的金额为文本类型，需要转换为数值类型，因此进行*1计算。

案例7. 数据分解

我们有以下数据。在项目字段中，周数和课程名称写在一起，用“-”分隔。我们想将它们排序成列。这时候我们还可以使用Substitute功能。

=TRIM(MID(替换($A2,’-‘,REPT(‘ ‘,99)),列(A1)*100-99,100))

公式解释：

SUBSTITUTE($A2,’-‘,REPT(‘ ‘,99))：表示将A2单元格中的’-‘替换为99个空格

COLUMN(A1)*100-99：向右拖动时，分别生成1,101,201个数字。

然后使用MID函数提取从1,101,201.等开始的数字，并取第100位。

为什么要取100位数字？

因为A列字符串至少包含1个字符，所以添加99个空格后，总位数至少为100

使用TRIM 函数删除末尾多余的空格。

案例8：拆分相似项目

我们需要将A和B的水平数据拆分为D和E列的垂直数据。我们也可以使用Substitute函数来实现这一点。

=IFERROR(TRIM(MID(替换(查找(,0/(A$2:A$6=D2),B$2:B$6),’,’,REPT(‘ ‘,99)),100*COUNTIF(D$2:D2, D2)-99,100)),”)

用户评论

雨后彩虹

终于有人讲了 SUBSTITUTE 函数！我每次用这个功能都感觉自己是在打仗似的，不知道怎么才能搞定各种复杂的替换场景。这篇文章讲解得真不错，受益匪浅！

    有8位网友表示赞同！

余温散尽ぺ

说的是“神技”吧？我觉得对于普通用户来说，这个函数的实用性还是挺有限的。除非经常会遇到大量的文字替换需求，不然用下方的Find & Replace 功能就足够了。

    有12位网友表示赞同！

涐们的幸福像流星丶

我终于明白了 SUBSTITUTE 函数可以用在公式中！之前一直以为它只用于文本操作，原来还可以用来动态构建数据呢！ 太棒了！

    有5位网友表示赞同！

良人凉人

看了一半后决定把这篇文章下载下来留着备用。这可是个宝藏级教程啊！下次遇到替换需求再翻出来看看

    有7位网友表示赞同！

温柔腔

讲得真详细，涵盖了几乎所有使用场景。我之前就经常会遇到文本重复出现的问题，这个函数简直是救星啊！

    有8位网友表示赞同！

一笑抵千言

SUBSTITUTE 函数这东西学起来还是挺不容易的。这篇教程用了一些生动的例子来解释，比较容易理解。建议把代码和案例放在一起更好学习

    有19位网友表示赞同！

凉笙墨染

感觉作者的教学思路很清晰，从基础用法到进阶技巧都讲解了非常详细，而且还附带了一些实际应用案例，这对想要深入学习这种函数的人来说很有帮助

    有15位网友表示赞同！

米兰

我一直在用 Excel 办公多年了，居然连这个强大的函数都没听说过！太遗憾啦！赶紧试试看，看看能不能解决一些我之前困扰的问题

    有12位网友表示赞同！

水波映月

SUBSTITUTE 函数的确很棒，但是它有个局限性在于只能一次替换一个文本。如果需要进行多处替换的话，那就得循环公式或者用其他方法了

    有10位网友表示赞同！

瑾澜

这个“神技”感觉用起来还是挺累的啊，复杂的替换条件可能还得配合其他的函数使用才行。不过这篇文章讲解得很清楚，我争取能尽快掌握它的技巧

    有11位网友表示赞同！

念旧是个瘾。

这篇 文章把函数的使用场景分析得非常到位，尤其是那些常见的错误分析和解决方案，真的很实用！希望以后还有更多关于 Excel 函数的教程

    有10位网友表示赞同！

晨与橙与城

感觉SUBSTITUTE 函数的功能还是挺强大的，能够帮助我们快速完成各种文本替换操作，提高工作效率!

    有7位网友表示赞同！

安好如初

我之前也用过这个函数，但是总觉得很陌生。这篇文章的讲解非常详细，让我对 SUBSTITUTE 函数有了更清晰的理解，原来它还能用在公式中!

    有13位网友表示赞同！

孤单*无名指

看完这篇文章，真是收获满满。感谢作者分享这么实用且宝贵的Excel知识！

    有9位网友表示赞同！

﹎℡默默的爱

这篇教程讲得太棒啦！简直是 Excel 学习者的必修课程！我要好好练习一下，把SUBSTITUTE 函数的技巧熟练掌握

    有13位网友表示赞同！

灼痛

SUBSTITUTE 函数确实非常有用，尤其是对于需要频繁进行文本重写的场合。这篇文章很值得推荐给学习Excel的朋友们

    有18位网友表示赞同！

江山策

虽然我平时用 Excel 不是很多，但看到这篇关于 SUBSTITUTE 函数的文章还是很有启发。 或许下次用到的时候可以试试，说不定会有新的发现。

    有12位网友表示赞同！