The SUBSTITUTE function replaces occurrences of a search text string, within an original text string, with the supplied replacement text.

Syntax

=SUBSTITUTE(text,old_text,new_text,[instance_num])

Arguments

Argument Description
text The original text string containing the text to be replaced
old_text The text you want to replace
new_text The text you want to replace old_text with
[instance_num] Optional. Specifies which occurrence of old_text you want to replace with new_text

     If you specify instance_num, only that instance of old_text is replaced
  If omitted, all instances of old_text are replaced with the new_text

Usage note: Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Note: The 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”.

Examples

  A B C D
1 Text Formula Result Notes
2   =SUBSTITUTE(“abcabc”,”a”,”b”) bbcbbc  
3   =SUBSTITUTE(“abcabc”,”a”,”b”,2) abcbbc  
4 Today is Saturday =SUBSTITUTE(A4,”Saturday”,”Sunday”) Today is Sunday  
5 Cat In The Hat =SUBSTITUTE(A5,”h”,”B”,2) Cat In The Hat Substitute is case sensitive
6   =SUBSTITUTE(A5,”H”,”B”,1) Cat In The Bat  

Common Function Error(s)

Problem What went wrong
Using SUBSTITUTE with a date, time or numbers returns unexpected results SUBSTITUTE is designed for use with text strings and returns a text string.

  If you are not planning to use the date, time or number in further calculations, use the Text To Columns tool to convert date, time, and number values into text