The most easy way will be to use the replace function but it may happen that I need this function in formula.

 

When I use the formula ?

In general, when I want to substitute a word into a sentence but I can use it to replace a character or else.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

IF

IFERROR

FIND

SUBSTITUTE

REPLACE

Formula for 1 word to replace:

=SUBSTITUTE(A2:A10,"search word","replace by")

For instance: =SUBSTITUTE(A2:10,"resolved",E2)

Formula for 2 or more words to replace:

=SUBSTITUTE(repeatformula,"search word","replace by")

NOTE: replace “repeatformula” by the same formula, for instance:

=SUBSTITUTE(SUBSTITUTE(A2:10,"search word 2","replace by 2"),"search word 1","replace by 1")

If I want to add a condition, this formula will not work because it makes reference to a range. To use a criteria, I will make reference to a cell, for instance:

=IF(A2="","",SUBSTITUTE(A2,"search word","replace by"))

Alternatively, I can use the REPLACE function but it will not work for multiple words in a single column:

IFERROR(REPLACE(A2,FIND("search word",A2),number,"replace by"),A2)

NOTE:

• Replace “number” by the number of characters, for instance: search word = 11
• For 2 or more words, you will need to repeat the formula in other columns, for instance:
  • Column C: =IFERROR(REPLACE(A2,FIND("search word 1",A2),number,"replace by 1"),A2)
  • Column D: =IFERROR(REPLACE(C2,FIND("search word 2",C2),number,"replace by 2"),C2)