Replacing or Substituting Text
 

I'm trying to substitute/replace text in one cell for another text in another
cell. I don't want the return value to be the original text or a "#VALUE!" in
the new cell if it does not find the text to replace. PLEASE HELP. I'm going
crazy.

Here is an example:

A1 value is Brown Donkey
A2 value is Purple Butterfly
A3 value is Brown Dog
A4 value is Pink Panther

If I use the substitute formula for Column B to replace Brown with BLUE,
this is what I get:

B1 value is BLUE Donkey
B2 vlaue is Purple Butterfly
B3 value is BLUE Dog
B4 value is Pink Panther

HERE IS THE DESIRED RESULT:

B1 value is Blue Donkey
B2 value is " "
B3 value is Blue Dog
B4 value is " "

"" (quotes) represent blank cells. PLEASE HELP!

Try:

=IF(ISERROR(SEARCH("Brown",A1)),"",SUBSTITUTE(A1," Brown","Blue"))

Note Seach is not case sensitive while Substitute is.

Hope this helps
Rowan

Naya wrote:
I'm trying to substitute/replace text in one cell for another text in another
cell. I don't want the return value to be the original text or a "#VALUE!" in
the new cell if it does not find the text to replace. PLEASE HELP. I'm going
crazy.

Here is an example:

A1 value is Brown Donkey
A2 value is Purple Butterfly
A3 value is Brown Dog
A4 value is Pink Panther

If I use the substitute formula for Column B to replace Brown with BLUE,
this is what I get:

B1 value is BLUE Donkey
B2 vlaue is Purple Butterfly
B3 value is BLUE Dog
B4 value is Pink Panther

HERE IS THE DESIRED RESULT:

B1 value is Blue Donkey
B2 value is " "
B3 value is Blue Dog
B4 value is " "

"" (quotes) represent blank cells. PLEASE HELP!