Replaces existing text with new text in a string.
Sample usage
SUBSTITUTE("search for it","search for","Google")
SUBSTITUTE(A2,"new york","New York")
SUBSTITUTE("January 2, 2012",2,3,1)
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 within text_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 replace search_for. -
occurrence_number
– [ OPTIONAL ] – The instance of search_for within text_to_search to be replaced with replace_with. By default, all occurrences of search_for are replaced; however, if occurrence_number is specified, only the indicated instance of search_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 to text according to a specified format.
REPLACE
: Replaces part of a text string with a different text string.