I am currently doing the Ctrl-H, just trying to automate.

What I want to do is IF DR exists, replace it with Door, otherwise do
nothing.
Trying to nest FIND in either the IF or the REPLACE functions returns
the error.

Thanks



Anne Troy wrote:
First, is there some reason you can't Ctrl+H, and replace
"(space)DR.(space)" with "DOOR"?
Alternatively, what do you want to do if DR. does not exist in the string?
If nothing, then try this:

=(IF(ISERROR(FIND("DR.",A1,1)),"",FIND("DR.",A1,1) )

****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"HalB" wrote in message
...
How does one "overcome" the #Value! error when used in a function?

Example:

A1= "4 DR. SEDAN"

I want to FIND "DR."

The formula I use is =FIND("DR.",A1,1) and this works fine however when A1
does not contain "DR." I get the error.

I am actually trying to replace "DR. " with "Door" but the error causes
the formula to fail.

Thanks