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!
>
>