Basic Functions: SUBSTITUTE
The SUBTITUTE function replaces a sequence of characters with a different sequence of characters in a text string.
The arguments to the functions are :
SUBSTITUTE(text, old_text, new_text [,instance_num])
where
- text The text for which you want to substitute characters.
- search_text The text you want to replace.
- new_text The text you want to replace search_text with.
- occurence Optional: The occurrence number you want to replace.
This function should be used when you want to replace all the instances of some text regardless of their location in the text. If “occurence” is left blank, every occurrence of “search_text” is changed to “new_text”. The SUBSTITUTE function is very similar to the REPLACE function.
See also this tip for a clever application of the SUBSTITUTE function.