Replace data with SUBSTITUTE function in Excel

The REPLACE function replaces existing words, text or characters with new data. Use the REPLACE function to remove non-printing characters from imported data, replace unwanted characters with spaces, and create different versions of the same table.

Remark . The instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

Content
  1. REPLACE Function Syntax
  2. Use the REPLACE function
  3. REPLACEMENT VS REPLACEMENT Features

REPLACE Function Syntax

Function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.

The syntax for the REPLACE function is:

 = ЗАМЕНА ( Текст ,  Старый_текст ,  Новый_текст ,  Instance_num ) 

Arguments for the function:

  • Text (necessary). Data containing the text to be replaced. This argument can contain the actual data in quotes (see line 2 in the image above) or a cell reference to locate the text data on the sheet (see lines 3 and 4).
  • old_text (mandatory): The text to be replaced.
  • new_text (required): Text to replace old_text.
  • instance_num (optional): number. If this number is omitted, any instance of Old_text will be replaced with New_text. If this number is included, instances of the specified Old_text will be replaced (see line 5).

The arguments to the SUBSTITUTE function are case sensitive. If the data entered for the Old_text argument does not match the data in the cell with the Text argument, then no replacement occurs.

Use the REPLACE function

Although you can manually enter an entire formula cell in a worksheet cell, another option is to use the Function Arguments dialog box (or the Formula Builder in Excel for Mac) to enter the function and its arguments into the cell.

 = ЗАМЕНИТЬ (A3, "Продажи", "Доход") 

When you use the dialog box, Excel makes sure to separate each argument with a comma and enclose the old and new text data in quotes.

  1. Enter the data for the tutorial in a blank Excel sheet.

  2. Select cell B3 to make it active.

  3. Select formulas .

  4. Select Text to open the Text Functions drop-down list.

  5. Select TO REPLACE in the list to open the Function Arguments dialog box. Excel for Mac opens the Formula Builder.

  6. Place cursor in text field Text .

  7. Select cell A3 on the sheet to enter a reference to that cell.

  8. Place cursor in text field old_text .

  9. Enter Sale . This is the text that will be replaced. It is not necessary to enclose the text in quotation marks.

  10. Place cursor in text field new_text .

  11. Enter Income . This text will be replaced by Sales.

  12. The instance_num argument is left blank because there is only one instance of the word Sales in cell A3.

  13. Select Okay . Except for Excel for Mac where you choose Finished .

  14. text on the income statement appears in cell B3.

REPLACEMENT VS REPLACEMENT Features

REPLACE differs from the REPLACE function in that it is used to exchange specific text anywhere in the selected data. REPLACE is used to replace text that occurs in a particular data location.

Leave a Reply

Your email address will not be published.