Find and replace characters with a formula in an excel report

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 the formula ?

IF

ISERROR

SEARCH

XXX

SUBSTITUTE

Formula for 1 word to replace:

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

Formula for 2 or more words to replace:

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

NOTE: replace “repeatformula” by the same formula then the last one by the cell reference, for instance:

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

Interesting Management