substitute函数语法（Excel字符替换:SUBSTITUTE函数常见用法）

本文目录

• Excel字符替换:SUBSTITUTE函数常见用法
• EXCEL函数公式 如图，想要实现下面的公式，函数怎么写求大神指点
• EXCEl函数 怎样删除单元格部分字符，再添加字符
• excel SUBSTITUTE 能使用多个条件吗
• excel怎么批量修改单元格内容,统计,SUBSTITUTE函数使用技巧
• 在Excel中常用函数
• EXCEL的替换函数怎么用
• excel中=IF（SUBSTITUTE（I2,“兴业“,““）=I2,“否“,“是“） 这个公式是什么意思呢

Excel字符替换:SUBSTITUTE函数常见用法

 当在实际的工作中，我们需要对指定的字符串进行替换时，我们就会想到SubStitute函数，但是关于此函数你真的了解吗？

SUBSTITUTE函数功能

对指定的字符串进行替换

SUBSTITUTE函数语法

翻译白话格式：

参数说明

Text: 必需。 需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。

Old_text： 必需。 需要替换的文本。

New_text：必需。 用于替换 old_text 的文本。

Instance_num：可选。 指定要用 new_text 替换 old_text 的事件。 如果指定了 instance_num，则只有满足要求的 old_text 被替换。 否则，文本中出现的所有 old_text 都会更改为 new_text。

SUBSTITUTE函数使用方法实例

示例1： 把字符串ExcelHSGS中的HSGS替换为“函数公式”。

方法：在目标单元格中输入公式：=SUBSTITUTE(B3,"HSGS","函数公式")

备注：

在实际工作中，如果遇到字符串中含有大量的空格，这是可以用SubStitute函数全部进行替换。

示例2： 替换掉空值再查找。

空格可以影响Vlookup函数的查找，这是只需要用SubStitute函数来辅助，替换掉空格，就可以顺利的达到目的。

方法：在目标单元格中输入公式：=VLOOKUP(SUBSTITUTE(F4," ",""),A2:B8,2,0)。

释义： =VLOOKUP（要查找的值、要在其中查找值的区域、区域中包含返回值的列号、精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE）。而【要查找的值】我们用函SUBSTITUTE(F4," ","")来替代，我们可以在F4单元中发现“C”前面有一个空格，如果不予处理，我们不会查找到正确的值。
你学会了吗？

谢谢大家的观看，我们下一期再见，如果有什么问题欢迎评论区留言或私信我们

想学习更多的函数知识，欢迎关注转发

EXCEL函数公式 如图，想要实现下面的公式，函数怎么写求大神指点

公式=TEXTJOIN(" ",TRUE,TRIM(TEXT(MID(A2,ROW($1:$100),1),)))，按三键确认数组公式。

具体如下：

EXCEl函数 怎样删除单元格部分字符，再添加字符

在F1中输入公式，如图所示：

关于此例中用到的Substitute函数的相关知识：

Excel中SUBSTITUTE函数的用途介绍：

在文字字符串中用new_text替代old_text。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。

SUBSTITUTE函数的语法为：

SUBSTITUTE(text, old_text, new_text, )

SUBSTITUTE 函数语法具有下列参数：

  第一，Text：必需。需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。 

  　第二，Old_text：必需。需要替换的旧文本。 

  第三，New_text：必需。用于替换 old_text 的文本。 

  　第四，Instance_num：可选。用来指定要以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则会将 Text 中出现的每一处 old_text 都更改为 new_text。

excel SUBSTITUTE 能使用多个条件吗

1. substitute函数的含义：对指定的字符串进行替换。

2. substitute函数的语法格式

   =substitute(text,old_text,new_text,)

   =substitute（需要替换的文本，旧文本，新文本，第N个旧文本）

   参数Instance_num ——为一数值，用来指定以 new_text （新文本）替换第几次出现的 old_text（旧文本）。

   参数Instance_num 可省略，这表示用 new_text（新文本）替换掉所有的old_text（旧文本）。

3. 参数Instance_num 可省略，这表示用 new_text（新文本）替换掉所有的old_text（旧文本）。

   如图所示，屏蔽手机号码的后四位。这里参数Instance_num省略，替换掉所有的old_text（旧文本）。

   如图所示，屏蔽手机号码也可以使用replace函数。Replace函数的含义

   用新字符串替换旧字符串，而且替换的位置和数量都是指定的。建议结合起来学习，可以观看小编的经验Excel中replace函数的使用方法 

4. 如图所示，substitute函数经常用来去掉文本之间的空格。
   

5. 参数Instance_num ——为一数值，用来指定以 new_text （新文本）替换第几次出现的 old_text（旧文本）。

   参数Instance_num 可省略，这表示用 new_text（新文本）替换掉所有的old_text（旧文本）。

   如图所示，去掉文本之间的空格。

   输入公式=SUBSTITUTE(A1," ","",2)

   这里的2表示用新文本（空）替换掉第2次出现的旧文本（空格）

excel怎么批量修改单元格内容,统计,SUBSTITUTE函数使用技巧

在文本字符串中用 new_text 替代 old_text。

如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；

如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数 REPLACE。 

语法  
SUBSTITUTE(text,old_text,new_text,instance_num) 
Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。 
Old_text 为需要替换的旧文本。 
New_text 用于替换 old_text 的文本。 
Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。

如果指定了 instance_num，则只有满足要求的 old_text 被替换；

否则将用 new_text 替换 TEXT 中出现的所有 old_text。 

当我们想把表格中的某一文本字符串替换成指定文本时，可以用SUBSTITUTE函数，使用这个函数需要输入四个值。

字符串指要替换的数据，原字符串是要被替换的内容，新字符串是要替换掉原字符串的内容，替换序号要填入一个

数值，如果要被替换的数据在字符串中重复出现，我们可以选择替换该参数指定的第几个，如果不填，则全部替换。

假设我要将表格中的“2022”改成“2021”

在（B2）单元格插入SUBSTITUTE函数，点击确定。

在字符串的位置填（A2），原字符串填入“2”，新字符填入“1”。

因为在“2022”这个字符串中，“2”出现了3次，我们要替换的是第3个“2”，所以在替换序号中填入“3”点击

确定，可以看到“2021”出现了。

将光标定位到（B2）单元格右下角，呈+字形时下拉填充公式，即可完成批量修改。

当我们要替换表格中的大量重复错误内容时，使用这个函数非常快速！

在Excel中常用函数

函数1：MID函数

函数功能：输出文本字符串中从指定位置开始的特定数目的字符。

函数语法：MID(text, start_num, num_chars)

参数解释：

text：表示包含要提取字符的文本字符串。

start_num：表示文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，依此类推。

num_chars：表示指定希望MID从文本中返回字符的个数。
函数2：FIND函数

函数功能：在第二个文本串中定位第一个文本串，并输出第一个文本串的起始位置的值。

函数语法：FIND(find_text, within_text, )

参数解释：在第二个文本串中定位第一个文本串，并输出第一个文本串的起始位置的值。

find_text：表示要查找的文本。

within_text：表示包含要查找文本的文本。

start_num：表示指定要从其开始搜索的字符。within_text中的首字符是编号为1的字符。如果省略start_num，则默认其值为1。

函数3：LEFT函数

函数功能：输出从文本左侧开始指定个数的字符。

函数语法：LEFT(text, )

参数解释：

text：表示包含要提取的字符的文本字符串。

num_chars：可选。指定要由LEFT提取的字符的数量。

如图，在B2收件地址中，从左提取出省市的名称。公式为：

=LEFT(B2,FIND({"市","省"},B2))
函数4：RIGHT函数

函数功能：输出文本字符串中最后一个或多个字符。

函数语法：RIGHT(text,)

参数解释：

text：表示包含要提取字符的文本字符串。

num_chars：可选。指定了要由RIGHT提取的字符的数量。

如图，在B2单元格中提取出市级之后的详细地址信息。公式为：

=RIGHT(B2,FIND("市",B2)+1)

函数5：LOWER函数

函数功能：将大写字母转换为小写字母。

函数语法：LOWER(text)

参数解释：

text：表示要转换为小写字母的文本。函数LOWER不改变文本中的非字母的字符。

如图，将A2中的大写字母转换成小写字母。公式为：

=LOWER(A2)
函数6：PROPER函数

函数功能：首字母转换成大写，并将其余的字母转换成小写。

函数语法：PROPER(text)

参数解释：

text：表示用引号括起来的文本、返回文本值的公式或是对包含文本（要进行部分大写转换）的单元格的引用。
函数7：UPPER函数

函数功能：将文本转换成大写形式。

函数语法：UPPER(text)

参数解释：

text：需要转换成大写形式的文本。text可以为引用或文本字符串。
函数8：LEN函数

函数功能：输出文本字符串中的字符数。

函数语法：LEN(text)

参数解释：

text：表示要查找其长度的文本。空格将作为字符进行计数。
函数9：TEXT函数

函数功能：将数值转换为按指定数字格式表示的文本。

函数语法：TEXT(value,format_text)

参数解释：

value：表示数值、计算结果为数字值的公式或对包含数字值的单元格的引用。

format_text：作为用引号括起的文本字符串的数字格式。通过单击“设置单元格格式”对话框中的“数字”选项卡的“类别”框中的“数字”、“日期”、“时间”、“货币”或“自定义”并查看显示的格式，可以查看不同的数字格式。format_text不能包含星号（*）。

如图，将简化输入的日期，利用TEXT函数转换为日期格式。公式为：
函数10：REPLACE函数

函数功能：使用其它文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。

函数语法：REPLACE(old_text, start_num, num_chars, new_text)

参数解释：

old_text：表示要替换其部分字符的文本。

start_num：表示要用new_text替换的old_text中字符的位置。

num_chars：表示希望REPLACE使用new_text替换old_text中字符的个数。

new_text：表示将用于替换old_text中字符的文本。
函数11：SUBSTITUTE函数

函数功能：在文本字符串中用new_text替代old_text。

函数语法：SUBSTITUTE(text,old_text,new_text,instance_num)

参数解释：

text：表示需要替换其中字符的文本，或对含有文本的单元格的引用。

old_text：表示需要替换的旧文本。

new_text：用于替换old_text的文本。

instance_num：可选。用来指定要以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；否则会将text中出现的每一处old_text都更改为new_text。

如图，要去除A1单元格中多余的空格。公式为：

函数14：TRIM函数

函数功能：当从其他应用程序中获取带有不规则空格的文本时，可以使用TRIM函数，清除文本中的7位ASCII空格字符（值32）。

函数语法：TRIM(text)

参数解释：

text：表示需要删除其中空格的文本。

如图，删除A1中多余的空格。公式为：

=TRIM(A1)
函数15：SEARCH函数

函数功能：在第二个文本字符串中查找第一个文本字符串，并输出第一个文本字符串的起始位置的编号，该编号从第二个文本字符串的第一个字符算起。

函数语法：SEARCH(find_text,within_text,)

参数解释：

find_text：表示要查找的文本。

within_text：表示要在其中搜索find_text参数的值的文本。

start_num：可选。表示within_text参数中从之开始搜索的字符编号。

如图，从A2的市区信息中提取出区的信息。公式为：
常用的正则表达式

一、校验数字的表达式

1 数字：^*$

2 n位的数字：^\d{n}$

3 至少n位的数字：^\d{n,}$

4 m-n位的数字：^\d{m,n}$

5 零和非零开头的数字：^(0|*)$

6 非零开头的最多带两位小数的数字：^({1,2})?$

7 带1-2位小数的正数或负数：^(\-)?\d+(\.\d{1,2})?$

8 正数、负数、和小数：^(\-|\+)?\d+(\.\d+)?$

9 有两位小数的正实数：^{2})?$

10 有1~3位小数的正实数：^{1,3})?$

11 非零的正整数：^*$

12 非零的负整数：^\-\d*$

13 非负整数：^\d+$ 或 ^\d*|0$

14 非正整数：^-\d*|0$ 或 ^((-\d+)|(0+))$

15 非负浮点数：^\d+(\.\d+)?$ 或 ^\d*|0?\.0+|0$

16 非正浮点数：^((-\d+(\.\d+)?)|(0+(\.0+)?))$ 或 ^(-(\d*))|0?\.0+|0$

17 正浮点数：^*))$

18 负浮点数：^-(*)))$

19 浮点数：^(-?\d+)(\.\d+)?$ 或 ^-?(\d*|0?\.0+|0)$

二、校验字符的表达式

1 汉字：^{0,}$

2 英文和数字：^{4,40}$

3 长度为3-20的所有字符：^.{3,20}$

4 由26个英文字母组成的字符串：^+$

5 由26个大写英文字母组成的字符串：^+$

6 由26个小写英文字母组成的字符串：^+$

7 由数字和26个英文字母组成的字符串：^+$

8 由数字、26个英文字母或者下划线组成的字符串：^\w+$ 或 ^\w{3,20}$

9 中文、英文、数字包括下划线：^+$

10 中文、英文、数字但不包括下划线等符号：^{2,20}$

11 可以输入含有^%&’,;=?$\"等字符：+

12 禁止输入含有~的字符：+

三、特殊需求表达式

1 Email地址：^\w+(\w+)*$

2 域名：{0,62})+/.?

***隐藏网址***

4 手机号码：^(13)\d{8}$

5 电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX)：^(\(\d{3,4}-)|\d{3.4}-)?\d{7,8}$

6 国内电话号码(0511-4405222、021-87888822)：\d{3}-\d{8}|\d{4}-\d{7}

7 身份证号(15位、18位数字)：^\d{15}|\d{18}$

8 短身份证号码(数字、字母x结尾)：^({8,18}?$

9 帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^{4,15}$

10 密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：^\w{5,17}$

11 强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在8-10之间)：^(?=.*\d)(?=.*).{8,10}$

12 日期格式：^\d{4}-\d{1,2}-\d{1,2}

13 一年的12个月(01～09和1～12)：^(0?)$

14 一个月的31天(01～09和1～31)：^((0?)|30|31)$

15 钱的输入格式：

16 1.有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000"：^*$

17 2.这表示任意一个不以0开头的数字,但是,这也意味着一个字符"0"不通过,所以我们采用下面的形式：^(0|*)$

18 3.一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号：^(0|-?*)$

19 4.这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧.下面我们要加的是说明可能的小数部分：^+)?$

20 5.必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的：^{2})?$

21 6.这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样：^{1,2})?$

22 7.这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样：^{1,2})?$

23 8.1到3个数字,后面跟着任意个 逗号+3个数字,逗号成为可选,而不是必须：^({1,2})?$

24 备注：这就是最终结果了,别忘了"+"可以用"*"替代如果你觉得空字符串也可以接受的话(奇怪,为什么?)最后,别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里

25 xml文件：^($

26 中文字符的正则表达式：

27 双字节字符： (包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))

28 空白行的正则表达式：\n\s*\r (可以用来删除空白行)

29 HTML标记的正则表达式：《(\S*?)*》.*?《/\1》|《.*? /》 (网上流传的版本太糟糕，上面这个也仅仅能部分，对于复杂的嵌套标记依旧无能为力)

30 首尾空白字符的正则表达式：^\s*|\s*$或(^\s*)|(\s*$) (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)

31 腾讯QQ号：{4,} (腾讯QQ号从10000开始)

32 中国邮政编码：\d{5}(?!\d) (中国邮政编码为6位数字)

33 IP地址：\d+\.\d+\.\d+\.\d+ (提取IP地址时有用)

34 IP地址：((?:(?:25?\\d?\\d))

EXCEL的替换函数怎么用

方法/步骤

1、首先启动excel2010，选择单元格区域输入一些数字数据。

2、选择单元格b2，点击插入函数，接着弹出插入函数对话框。

3、在插入函数对话框中类别选择全部，选择函数中选择SUBSTITUTE，点击确定按钮。

4、在弹出的函数参数对话框中输入第一个参数为a2，第二个参数为mid（a2,5,4），第三个参数输入四个*，点击确定按钮。

5、这个时候你会看到单元格a2中相应的数据就变成了四个*号。

6、双击单元格b2右下角进行填充操作，这样所有的数据就完成了替换。

excel中=IF（SUBSTITUTE（I2,“兴业“,““）=I2,“否“,“是“） 这个公式是什么意思呢

=IF(SUBSTITUTE(I2,"兴业","")=I2,"否","是")
如果将I2的兴业替换掉与原来的I2相同（表示I2中无“兴业”二字），结果为“否”；
否则，结果为“是”。