I. Intro
The SUBSTITUTE function is used to replace text in a string with new text.
Note: If you need to replace specific text within a string, use SUBSTITUTE. If you need to replace text in a specific position within a string, use the REPLACE function.
II. About the function
- Formula: = SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
- Parameters:
- Text to search: The text within a string you want to replace, or the reference of the cell containing the text (the characters you will replace).
- Search for: The text to be replaced.
- Replace with: The new text that will be the replacement.
- Occurrence number (optional): Specify the number of times the old text is to be replaced with the new text. If this is not specified, all occurrences of the old text will be replaced with the new text.
- Example:
- =SUBSTITUTE("abcdefg","cde","xyz",1)
III. Steps
Use the SUBSTITUTE function
- Select a cell and click Formulas on the toolbar, then select Text > SUBSTITUTE. You can also directly enter =SUBSTITUTE in a cell.
- Enter the parameters in the cell. For example: =SUBSTITUTE("abcdefg","cde","xyz",1).
- Press Enter to display the result, which is abxyzfg in this example.
250px|700px|reset
Delete the SUBSTITUTE function
Select the cell with the SUBSTITUTE function, and press Delete.