excel函数:汉字转全拼_30天中的30个Excel函数：27

excel函数:汉字转全拼

Yesterday, in the 30XL30D challenge, we used the OFFSET function to return a reference, and saw that it is similar to the non-volatile INDEX function.

昨天，在30XL30D挑战中，我们使用OFFSET函数返回了引用，并发现它类似于非易失性INDEX函数。

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

For day 27 in the challenge, we'll examine the SUBSTITUTE function. Like the REPLACE function, it replaces old text with new text, in a text string, but can replace multiple instances of the same text.

在挑战的第27天，我们将检查SUBSTITUTE函数。像REPLACE函数一样，它将文本字符串中的旧文本替换为新文本，但可以替换同一文本的多个实例。

In some situations though, it's quicker and easier to use the Find/Replace command on the Excel Ribbon, with Match Case option turned on, for case sensitive replacement.

但是，在某些情况下，使用Excel功能区上的“查找/替换”命令(启用“区分大小写”)可以更快，更轻松地进行区分大小写的替换。

So, let's take a look at the SUBSTITUTE information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下SUBSTITUTE信息和示例，如果还有其他提示或示例，请在评论中分享。

功能27：替代 (Function 27: SUBSTITUTE)

The SUBSTITUTE function replaces old text with new text, in a text string. It will replace all instances of the old text, unless a specific occurrence is selected, and SUBSTITUTE is case sensitive.

SUBSTITUTE函数将文本字符串中的旧文本替换为新文本。它将替换旧文本的所有实例，除非选择了特定的实例，并且SUBSTITUTE区分大小写。

您如何使用SUBSTITUTE？ (How Could You Use SUBSTITUTE?)

The SUBSTITUTE function replaces old text with new text, in a text string, so you could use it to:

SUBSTITUTE函数将文本字符串中的旧文本替换为新文本，因此您可以使用它来：

Change region name in report title
在报告标题中更改区域名称
Remove non-printing characters
删除非打印字符
Replace last space character
替换最后一个空格字符

替代语法 (SUBSTITUTE Syntax)

The SUBSTITUTE function has the following syntax:

SUBSTITUTE函数具有以下语法：

- text is the text string or cell reference, where text will be replaced.
  text是文本字符串或单元格引用，其中将替换文本。
- old_text is the text that will be removed
  old_text是将要删除的文本
- new_text is the text that will be added
  new_text是将要添加的文本
- instance_number is the specific occurrence of old text that you want replaced
  instance_number是您要替换的旧文本的特定出现
SUBSTITUTE(text,old_text,new_text,instance_num)
替换(text，old_text，new_text，instance_num)

替补陷阱 (SUBSTITUTE Traps)

The SUBSTITUTE function can replace all instances of the old text, so use the instance_num argument if you want only a specific occurrence of old text replaced.
SUBSTITUTE函数可以替换旧文本的所有实例，因此，如果只希望替换特定的旧文本，请使用instance_num参数。
For replacements that are not case sensitive, you can use the REPLACE function.
对于不区分大小写的替换，可以使用REPLACE函数。

示例1：在报告标题中更改区域名称 (Example 1: Change region name in report title)

With the SUBSTITUTE function, you can create a report title that changes automatically, based on the region name that is selected.

使用SUBSTITUTE功能，您可以创建一个报告标题，该标题根据所选的区域名称自动更改。

In this example, the report title is entered in cell C11, which is named RptTitle. The "yyy" in the title text will be replaced with the region name, selected in cell D13.

在此示例中，报表标题输入单元格C11中，名为RptTitle。标题文本中的“ yyy”将替换为在单元格D13中选择的区域名称。

=SUBSTITUTE(RptTitle,"yyy",D13)

= SUBSTITUTE(RptTitle，“ yyy”，D13)

示例2：删除非打印字符 (Example 2: Remove non-printing characters)

When you copy data from a website, there might be hidden, non-printing space characters in the text.

从网站复制数据时，文本中可能包含隐藏的非打印空格字符。

If you try to remove space characters from the text in Excel, the TRIM function can't remove them. The characters aren't normal space characters (character 32); they are non-breaking space characters (character 160).

如果您尝试从Excel中的文本中删除空格字符，则TRIM函数无法删除它们。这些字符不是普通的空格字符(字符32)；它们是不间断的空格字符(字符160)。

Instead, you can use the SUBSTITUTE function to replace each of the non-printing spaces with a normal space character. Then, use TRIM to remove all the extra spaces.

而是，您可以使用SUBSTITUTE函数用普通的空格字符替换每个非打印空格。然后，使用TRIM删除所有多余的空格。

=TRIM(SUBSTITUTE(B3,CHAR(160)," "))

= TRIM(SUBSTITUTE(B3，CHAR(160)，“”))

示例3：替换最后一个空格字符 (Example 3: Replace last space character)

Instead of replacing all instances of a text string, you can use the SUBSTITUTE function's instance_number argument to select a specific instance. In this list of recipe ingredients, we want to replace the last space character only.

代替替换文本字符串的所有实例，可以使用SUBSTITUTE函数的instance_number参数选择特定的实例。在此配方成分列表中，我们只希望替换最后一个空格字符。

In cell C3, the LEN function calculates the number of characters in cell B3. The SUBSTITUTE function replaces all the space characters with empty strings, and the second LEN function finds the length of the revised string. The length is 2 characters shorter, so there are two spaces.

在单元格C3中，LEN函数计算单元格B3中的字符数。 SUBSTITUTE函数用空字符串替换所有空格字符，第二个LEN函数查找修改后的字符串的长度。长度短了2个字符，所以有两个空格。

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

= LEN(B3)-LEN(SUBSTITUTE(B3，“”，“”))

In cell D3, the SUBSTITUTE function replaces only the 2nd space character with the new text – " | "

在单元格D3中，SUBSTITUTE函数仅将第二个空格字符替换为新文本–“ |”

=SUBSTITUTE(B3," "," | ",C3)

= SUBSTITUTE(B3，“”，“ |”，C3)

Instead of using two columns for this formula, you could combine them into one long formula.

您可以将它们合并为一个长公式，而不是为此公式使用两列。

=SUBSTITUTE(B3," "," | ",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))

= SUBSTITUTE(B3，“”，“ |”，LEN(B3)-LEN(SUBSTITUTE(B3，“”，“”)))

下载SUBSTITUTE功能文件 (Download the SUBSTITUTE Function File)

To see the formulas used in today's examples, you can download the SUBSTITUTE function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载SUBSTITUTE函数示例工作簿 。该文件已压缩，并且为Excel 2007文件格式。