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

Returns the text string after replacing instances of a substring.

textThe text string with the characters you want to replace.
old_textThe text you want to replace.
new_textThe text you want to replace "old_text" with.
instance_num(Optional) The occurrence number you want to replace.

Remarks

* This function is case sensitive.
* This function does not support wildcards (? or *).
* This function can be used when you want to replace all the instances of a particular substring.
* The "text" can be a cell reference.
* If you specify "instance_num", only that instance of "old_text" is replaced.
* If "instance_num" is left blank, every occurrence of "old_text" is changed to "new_text".
* You can use the FIND function to return the position of a substring within a larger text string. This is case sensitive.
* You can use the SEARCH function to return the position of a substring within a larger text string. This is not case sensitive.
* You can use the REPLACE function to return a text string with a number of characters replaced.
* You can use the UPPER function to converted all the characters to uppercase.
* You can use the PROPER function to convert the first letter of every word to a capital letter.
* For the Microsoft documentation refer to support.office.com

 A
1=SUBSTITUTE("July Data","July","April") = April Data
2=SUBSTITUTE("July Data","july","April") = July Data
3=SUBSTITUTE("2 March, 2020","2","1") = 1 March, 1010
4=SUBSTITUTE("2 March, 2020","2","1",1) = 1 March, 2020
5=SUBSTITUTE("2 March, 2020","2","1",2) = 2 March, 1020
6=SUBSTITUTE("2 March, 2020","2","1",3) = 2 March, 2010
7=SUBSTITUTE("Better Solutions","Better","Awesome") = Awesome Solutions
8=SUBSTITUTE("Better Solutions","tter","st") = Best Solutions
9=SUBSTITUTE("123-456-789","-","") = 123456789
10=SUBSTITUTE("123 456 789"," ","-") = 123-456-789
11=SUBSTITUTE(SUBSTITUTE("Abr 1 - Abr 2","Abr 1","one"),"Abr 2","two") = one - two
12=PROPER(SUBSTITUTE(UPPER("July Data"),UPPER("july"),"April")) = April Data

1 - What text string would you get if you substituted the word "July" for the word "April" in the text string "July Data".
2 - What text string would you get if you substituted the word "july" for the word "April" in the text string "July Data". There would be no substitution because this function is case sensitive and there is no substring "july".
3 - What text string would you get if you substituted the substring "2" for the substring "1" in the text string "2 March, 2020". All the occurences are replaced.
4 - What text string would you get if you substituted the first occurrence of the substring "2" for the substring "1" in the text string "2 March, 2020".
5 - What text string would you get if you substituted the second occurrence of the substring "2" for the substring "1" in the text string "2 March, 2020".
6 - What text string would you get if you substituted the third occurrence of the substring "2" for the substring "1" in the text string "2 March, 2020".
7 - What text string would you get if you substituted the word "Better" for the word "Awesome" in the text string "Better Solutions".
8 - What text string would you get if you substituted the characters "tter" for the characters "st" in the text string "Better Solutions".
9 - What text string would you get if you substituted the dash character "-" for an empty substring "" in the text string "123-456-789". This removes all the dashes.
10 - What text string would you get if you substituted the space character " " for a dash character "-" in the text string "123 456 789".
11 - What text string would you get if you combined two functions together to make two substitutions. The inside function substitutes the "Abr 1" substring and the outside function substitutes the "Abr 2" substring.
12 - This function is case sensitive but you can get around this limitation by combining it with other functions.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top