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:
- 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) .
Pressing Enter results as:
- Replace all strings found:
In the cell to get new values enter the formula: = SUBSTITUTE (C6, D6, E6) .
Pressing Enter results as:
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) .
Pressing Enter results returns:
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.
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!
You should read it
- How to use the function to delete spaces in Excel
- How to use the TEXTJOIN function in Excel 2016
- How to use the SWITCH function in Excel 2016
- Difference between REPLACE and SUBSTITUTE in Microsoft Excel
- How to use the IFS function in Excel 2016
- How to use MID functions to get strings in Excel
- CONCATENATE function (string concatenation function) in Excel
- How to use the XLOOKUP function in Excel?
May be interested
- CHITEST function - The function returns the independence test in Excelthe following article details how to use chitest, the function returns independence test.
- ISPMT function - The function that calculates interest for a specified period in Excelispmt function - the function that calculates interest for a specified period in excel
- NORMINV function - The function returns the inverse of the cumulative distribution in Excelthe following article details how to use the norminv function - the function returns the inverse of the cumulative distribution in excel.
- QUARTILE function - The function returns the quartile of a dataset in Excelthe quartile is an indispensable value in the field of surveying and sales, it helps you evaluate certain criteria in a set. the following article details how to use quartile function, the function returns the quartile of a dataset.
- Instructions to save Excel files as PDFs in Office 2010, 2013, 2016previously when you need to convert excel files to pdf, you need to install 1 conversion software, but from office 2010 and above, microsoft has built this feature into excel. the following article details how to save excel files as pdf.
- Hide and display columns and rows in Excelin the process of working with excel, you want to hide a certain row or column because of job requirements. the following article details how to display or hide columns and rows in excel.