SUBSTITUTE
Definition
Replaces existing text with new text in a string.
Sample Usage
Syntax
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
text_to_search
- The text within which to search and replace.search_for
- The string to search for withintext_to_search
.search_for
will match parts of words as well as whole words; therefore a search for"vent"
will also replace text within"eventual"
.
replace_with
- The string that will replacesearch_for
.occurrence_number
- [ OPTIONAL ] - The instance ofsearch_for
withintext_to_search
to replace withreplace_with
. By default, all occurrences ofsearch_for
are replaced; however, ifoccurrence_number
is specified, only the indicated instance ofsearch_for
is replaced.
Notes
SUBSTITUTE
can be used to replace one or all instances of a string within text_to_search
. It cannot be used to replace multiple, but not all instances within a single call.This function returns text as the output. If a number is desired, try using the VALUE
function in conjunction with this function.
See Also
TEXT
: Converts a number into text according to a specified format.
REPLACE
: Replaces part of a text string with a different text string.
In order to use the AVERAGE SUBSTITUTE formula, start with your edited Excellentable