How to replace words in excel?
You can use two different functions – substitute and replace. Difference between substitute and replace are shown from the following table.
SUBSTITUTE function in excel
The substitute function in excel is to replace a text within a cell by a new text
|text||The full text|
|old_text||The text to replace (Note: this is case sensitive)|
|new_text||The text to replace with (New text)|
|instance [optional]||The instance of old_text to replace with new_text. Optional; if not supplied, all instances of old_text are replaced with new_text.|
There is a statement “This restaurant is the WORST WORST I have ever seen!” in cell B1, and now we want to change the word from “WORST” to “BEST” using the substitute function in B2
|Text||A1 (“This restaurant is the WORST I have ever seen!”)|
|Old Text||“WORST” (Note: this is case sensitive)|
|Result||This restaurant is the BEST I have ever seen!|
Use the SUBSTITUTE function when you want to replace text based on its content, not position. SUBSTITUTE finds and replaces old_text with new_text in a text string. Instance limits SUBSTITUTE replacement to one particular instance of old_text. If the instance is not supplied, all instances of old_text are replaced with new_text.
The instance is optional. if not supplied, all instances of old_text are replaced with new_text.
Use SUBSTITUTE to replace text based on content. Use REPLACE when to replace text based on its location.
SUBSTITUTE is case-sensitive and does not support wildcards.
REPLACE function in excel
The replace function in excel is to swaps part of the text string with another set of text
replace(text,strt_num, num_ of chars, new text)
|old_text||Source of the text|
|start_num||The starting location in the text to search.|
|num_chars||The number of characters to replace.|
|new_text||The text to replace old_text with.The full text|
There is a statement “This restaurant is the WORST WORST I have ever seen!” in cell B1, and now we want to change the word from “WORST WORST” to “WORLD’S WORST” using the substitute function in B3
|Old_Text||B1 (“This restaurant is the WORST WORST I have ever seen!”)|
|Result||This restaurant is the WORLD’S WORST I have ever seen!|
How it work
This time we have a longer path then the substitute function, we firstly used the “find” function to locate the first “WORST” in the sentence we are going to work on and followed by a “LEN” function to LEN function to determine the number of characters we are going to replace.
refer to the following links for instruction on how to use the Find and replace function
Use the REPLACE function when you want to replace text based on its location in a string.
Use FIND or SEARCH to find the location of text to replace it it’s not known in advance.
Use SUBSTITUTE to replace text based on content.
Substitute Vs Replace
So what is the difference between the Substitute and the Replace function? In a simple word, the substitute function actually searches for the words that to be replaced and replace it by a new word, while the replace function is more simple as it simply replaces the number of characters start from the given position by a new text.
Also bear in mind that although we are using words as demonstration, the actual operation of the substitute function is actually on character level. This means it can also be used to replace part of a word.
Try it yourself!
Or download from here