So a common request is switching text, so instead of “The day is Monday” we would switch “Monday” => “Tuesday” so it would ready “The day is Tuesday”. There are two functions that come up, SUBSTITUTE and REPLACE. We’ll talk now only about SUBSTITUTE, because that’s the more relevant function for this task.
So let’s start with a few lines of text:
What I want to do is replace the word “Excel” with “Microsoft Excel”. So let’s check out the SUBSTITUTE function:
SUBSTITUTE(text, old_text, new_text, [instance_num])
Text – the original string we’ll work with (“Tomer is great at Excel”)
Old text – the word we want to replace (“Excel”)
New text – the word we want to be replaced with (“Microsoft Excel”)
So let’s write our function in B2 as follows:
=SUBSTITUTE(A2, “Excel”, “Microsoft Excel”)
As we see it worked, it now says “Tomer is great at Microsoft Excel”, yay!
Let’s duplicate the function to the second row as well, so it will now say:
“AutomateMicrosoft Excel is the largest Microsoft Excel site in the world”. That’s not right. We can see the function replaced ALL occurrences of the word “Excel” to the new word. In the first instance “Excel” was part of “AutomateExcel”, because it’s a name. That shouldn’t of been replaced.
To solve this we have the 4th function parameter: instance_num which allows us to define which instance to replace, instead of all of them.
Let’s change our function to the follows:
=SUBSTITUTE(A3, “Excel”, “Microsoft Excel”, 2)
There we go! Now it’s perfect.