Try something like this:

=SUBSTITUTE(UPPER(A1),"DR.","DOOR")

If A1= "4 DR. SEDAN"
The formula returns: 4 DOOR SEDAN

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"HalB" wrote:

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