The Excel Substitute function replaces occurrences of a search text string, within an original text string, with the supplied replacement text.
The syntax of the function is :
Where the function arguments are:
|text||-||The original text string containing the text to be replaced.|
|old_text||-||The text to be found and replaced by new_text.|
|new_text||-||The new text that is to be used to replace the old_text.|
The Excel Substitute function is similar to the Excel Replace Function, but the Substitute function replaces one or more instances of a given text string, while the Replace function replaces text in a specified position of a supplied string.
Note also, that the Excel Substitute function is case sensitive. Therefore, if the old_text argument is the text string "A", this will NOT replace instances of the lower case text string "a".
The spreadsheets below show examples of use of the Excel Substitute Function.
Further information and examples of the Excel Substitute function can be found on the Microsoft Office website.
The Excel Substitute function is designed for use with text strings and returns a text string. Therefore, if you attempt to use the substitute function with a date, time or a number, it will give you unexpected results.
If you are not planning to use the date, time or number in further calculations, you could solve this problem by converting these values into text, using the Excel Text To Columns tool: