#Value! error

[HalB]

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

 

[Anne Troy]

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

 

[Ragdyer]

Try this:

=IF(ISERR(FIND("DR.",A1,1)),A1,SUBSTITUTE(A1,"DR.","DOOR"))

 

[HalB]

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

 

[HalB]

Voila!

Thanks!

 

[Ragdyer]

One word of note:

Your original formula, and the one I suggested, which was built around it,
is *case sensitive*!

If the "DR." may be "dr." in some of your data, you would need a more robust
formula.

You might use this instead of my original suggestion, since it would work
for all situations:

=IF(ISERR(SEARCH("DR.",A1)),A1,SUBSTITUTE(UPPER(A1),"DR.","DOOR"))

Appreciate the feed-back.

 

[JE McGimpsey]

No need to use FIND() at all - just use

=SUBSTITUTE(A1,"DR.","Door")

If "DR." doesn't exist, no error will be raised.

 

[Anne Troy]

Nice.
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Ragdyer]

Yellow complexion is from all the egg on my face.<vbg>

Although still wouldn't hurt to use Upper().

 

[Anne Troy]

Yeah. Right. As if you haven't thrown some egg yourself! You're just one of
the many I admire here, RD; one of the many who teach me new things all the
time. There are just some functions I can't get "on my list". SUBSTITUTE is
one of them.

****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Guest]

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

 

[Guest]

You don't really need
=IF(ISERR(FIND("DR.",A1,1)),A1........

all you need is

SUBSTITUTE(A1,"DR.","DOOR")
It it can not find "DR." it will return A1 anyway

 

[Anne Troy]

Hi, Ron!
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************