HomeApplicationOffice information

SUBSTITUTE function - The function replaces text strings in Excel

In the course of working you have excel data file need to change a series of data over the years. With a simple operation, you can change all that data. The following article details the SUBSTITUTE function - The function replaces the text string in excel.

Description: The function replaces 1 or the old character string into a new text string and allows alternatives at one or more locations.

Syntax: SUBSTITUTE (text, old_text, new_text, [instance_num]) .

Inside:

- text : The text contains the content to be replaced, required parameters.

- old_text : The old text string to be replaced, is a required parameter.

- new_text : The new text string replaces the old text string, which is a required parameter.

- instance_num : The position you want to replace, is an optional parameter.

For example:

Perform instead in the following data table:

SUBSTITUTE function - The function replaces text strings in Excel Picture 1

- Replace in the first position found: Want to replace the string 2016 in the first position in cell C6 to 2020.

In the cell to calculate enter the formula: = SUBSTITUTE (C6, D6, E6, 1) .

SUBSTITUTE function - The function replaces text strings in Excel Picture 2

Pressing Enter results as:

SUBSTITUTE function - The function replaces text strings in Excel Picture 3

- Replace all strings found:

In the cell to get new values ​​enter the formula: = SUBSTITUTE (C6, D6, E6) .

SUBSTITUTE function - The function replaces text strings in Excel Picture 4

Pressing Enter results as:

SUBSTITUTE function - The function replaces text strings in Excel Picture 5

Thus to replace all you ignore the replacement position.

- The replacement position is outside the value of the original string :

Suppose you want to replace 2016 in cell C6 in 3rd place but 2016 only in 2nd place you do the following:

In the cell to get new values ​​enter the formula: = SUBSTITUTE (C6, D6, E6,3) .

SUBSTITUTE function - The function replaces text strings in Excel Picture 6

Pressing Enter results returns:

SUBSTITUTE function - The function replaces text strings in Excel Picture 7

So if the replacement request is not on the original string, the replacement is ignored and returns the original string.

Similar to the remaining data when replacing 2 words.

SUBSTITUTE function - The function replaces text strings in Excel Picture 8

Also, if you do not leave the replacement string in Excel cells, you put the values ​​in quotation marks to identify it as a string. For example: SUBSTITUTE (C6, "2016", "2020", 1) .

Above is how to use the SUBSTITUTE function to help you save time when working. Good luck!

4.5 ★ | 2 Vote

You should read it

May be interested

  • How to create space after comma in ExcelHow to create space after comma in Excel
    to create a space after a comma in excel, you can use a function or immediately use an available tool to do it, with a quick and very simple method.
  • CONCATENATE function (string concatenation function) in ExcelCONCATENATE function (string concatenation function) in Excel
    use the concatenate function when you want to concatenate existing text strings into a single, complete text string. for the syntax and usage of the concatenate function, follow the article below.
  • MID and MIDB functions to cut strings in ExcelMID and MIDB functions to cut strings in Excel
    the mid () function returns a string from a text string, with the position to start extracting from the text string and the number of characters returned by the string that is specified. functions used in languages ​​use a single-byte character set (sbcs) and always count each character as 1.
  • How to use the IFS function in Excel 2016How to use the IFS function in Excel 2016
    the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
  • How to use MID functions to get strings in ExcelHow to use MID functions to get strings in Excel
    mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
  • How to separate text strings by commas or spaces in ExcelHow to separate text strings by commas or spaces in Excel
    separating text strings based on commas or spaces is a trick that helps you split the characters in a parameter cell into different clusters based on commas or spaces that parameter cell contains. tipsmake will guide you how to separate text strings by commas or spaces in excel.
  • Text and string processing functions in ExcelText and string processing functions in Excel
    in excel, there are many groups of functions to help you process data quickly, one of which is a group of functions for processing text and strings. the following article summarizes the text processing functions and strings in excel.
  • IFERROR function in Excel, formulas, and usageIFERROR function in Excel, formulas, and usage
    the iferror function in excel corrects a formula error, returning a different value that replaces the error message in the normal way.
  • How to use the XLOOKUP function in Excel?How to use the XLOOKUP function in Excel?
    excel's new xlookup function replaces vlookup, which provides a powerful replacement for one of excel's most popular functions. this article will guide you how to use this xlookup function.
  • How to delete dots in a series of numbers on ExcelHow to delete dots in a series of numbers on Excel
    to quickly remove the dot in the numeric sequence in excel, we can use the search or use function in excel.

Office information