How to replace words in excel? Substitute or replace in excel?

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

Syntax

SUBSTITUTE(text,old_text_new_text,[Instance_num])

Argument

ArgumentDescription
textThe full text
old_textThe text to replace (Note: this is case sensitive)
new_textThe 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.
Argument for Substitute Function

Example

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

Statement=SUBSTITUTE(B1,”WORST”,”BEST”)
TextA1 (“This restaurant is the WORST I have ever seen!”)
Old Text“WORST” (Note: this is case sensitive)
New Text“Best”
ResultThis restaurant is the BEST I have ever seen!
Example for Substitute Function

Notes

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

Syntax

replace(text,strt_num, num_ of chars, new text)

Argument

ArgumentDescription
old_textSource of the text
start_numThe starting location in the text to search.
num_charsThe number of characters to replace.
new_textThe text to replace old_text with.The full text
Argument for Replace Function

Example

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

Statement=REPLACE(B1,FIND(“WORST”,B1),LEN(“WORST”),”BEST”)
Old_TextB1 (“This restaurant is the WORST WORST I have ever seen!”)
Start_numFIND(“WORST”,B1)
num_charsLEN(“WORST”)
new_text“BEST”
ResultThis restaurant is the WORLD’S WORST I have ever seen!
Example for Replace Function

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

Find: Top 5 most useful String functions in Excel (Find)

LEN: Top 5 most useful String functions in Excel – (LEN)

Notes

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.

How to replace words in excel

Try it yourself!

Or download from here

Leave a Comment