excel中查找替换函数_30天中30个Excel函数：25 –替换

excel中查找替换函数

Yesterday, in the 30XL30D challenge, we used the INDEX function to return a value or reference, based on a row and/or column number. For day 25 in the challenge, we'll examine the REPLACE function, which is in the Text category. It replaces a specified number of characters in a text string, with new text.

昨天，在30XL30D挑战中，我们使用INDEX函数根据行和/或列号返回值或引用。 在挑战的第25天，我们将检查“替换”功能，该功能位于“文本”类别中。 它用新文本替换文本字符串中指定数量的字符。

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). So, let's take a look at the REPLACE information and examples, and if you have other tips or examples, please share them in the comments.

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。 因此，让我们看一下REPLACE信息和示例，如果还有其他提示或示例，请在评论中分享。

功能25：更换 (Function 25: REPLACE)

The REPLACE function replaces characters within text, based on the number of characters, and starting position, specified.

REPLACE函数根据指定的字符数和起始位置替换文本中的字符。

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

The REPLACE function can replace characters in a text string, such as:

REPLACE函数可以替换文本字符串中的字符，例如：

• change area code in phone number
  更改电话号码中的区号
• Replace first space with colon and space
  用冒号和空格替换第一个空格
• Use nested REPLACE to insert hyphens
  使用嵌套的REPLACE插入连字符

替换语法 (REPLACE Syntax)

The REPLACE function has the following syntax:

REPLACE函数具有以下语法：

• • old_text is the text string in which characters will be replaced.
    old_text是将替换字符的文本字符串。
  • start_num is the position of the old characters
    start_num是旧字符的位置
  • num_chars is the number of old characters that will be replaced
    num_chars是将要替换的旧字符数
  • new_text is the text that will replace the original text
    new_text是将替换原始文本的文本

  REPLACE(old_text,start_num,num_chars,new_text)

  替换(old_text，start_num，num_chars，new_text)

更换陷阱 (REPLACE Traps)

The REPLACE function replaces a specified number of characters at the indicated starting position. To replace a specific text string, anywhere in the original text, you can use the SUBSTITUTE function, which we'll see later in the challenge.

REPLACE函数在指示的起始位置替换指定数量的字符。 要在原始文本中的任何位置替换特定的文本字符串，您可以使用SUBSTITUTE函数，我们将在后面的挑战中看到该函数。

示例1：更改电话号码中的区号 (Example 1: Change area code in phone number)

With the REPLACE function, you can change the first three digits in a phone number, when a new area code is introduced. In this example, the new area code is entered in column C, and the revised phone numbers are shown in column D.

使用替换功能，当引入新的区号时，您可以更改电话号码的前三位数字。 在此示例中，新的区号输入到C列中，而修改后的电话号码显示在D列中。

=REPLACE(B3,1,3,C3)

= REPLACE(B3,1,3，C3)

示例2：用冒号和空格替换第一个空格 (Example 2: Replace first space with colon and space)

To identify the starting position for the REPLACE function, you can use the FIND function, to locate a specific text string or character. In this example, we want to replace the first space character with a colon and space character.

要确定REPLACE函数的开始位置，可以使用FIND函数来查找特定的文本字符串或字符。 在此示例中，我们要用冒号和空格字符替换第一个空格字符。

=REPLACE(B3,FIND(" ",B3,1),1,": ")

= REPLACE(B3，FIND(“”，B3,1)，1，“：”)

示例3：使用嵌套的REPLACE插入连字符 (Example 3: Use nested REPLACE to insert hyphens)

The REPLACE function can be nested, so multiple replacements are made in the old text string. In this example, the list of phone numbers needs to have hyphens inserted, after the first 3 numbers, and after the second 3 numbers. By using zero as the number of characters to replace, none of the numbers will be removed, and hyphens will be inserted.

REPLACE函数可以嵌套，因此可以在旧文本字符串中进行多次替换。 在此示例中，电话号码列表需要在前三个数字之后和后三个数字之后插入连字符。 通过使用零作为要替换的字符数，不会删除任何数字，并且会插入连字符。

=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")

= REPLACE(REPLACE(B3,4,0，“-”)，8,0，“-”)

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

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

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

观看REPLACE视频 (Watch the REPLACE Video)

To see a demonstration of the examples in the REPLACE function sample workbook, watch this short Excel video tutorial.

要观看REPLACE函数示例工作簿中示例的演示，请观看此简短的Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2011/01/26/30-excel-functions-in-30-days-25-replace/

excel中查找替换函数