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 ?






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