Searching and replacing within a string excel

You can use the REPLACE function in conjunction with the SEARCH function to create a new
string that replaces part of the original text string with another string. In effect, you use the
SEARCH function to find the starting location used by the REPLACE function.
For example, assume cell A1 contains the text Annual Profit Figures. The following formula
searches for the word Profit and replaces those six characters with the word Loss:

=REPLACE(A1,SEARCH(“Profit”,A1),6,”Loss”)

This next formula uses the SUBSTITUTE function to accomplish the same effect in a more effi-
cient manner:

=SUBSTITUTE(A1,”Profit”,”Loss”)