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

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

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"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

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

Voila!

Thanks!


Ragdyer wrote:
Try this:

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

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"HalB" wrote in message
...
Voila!

Thanks!


Ragdyer wrote:
Try this:

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

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

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

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

In article , HalB
wrote:

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

Nice. :)
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"JE McGimpsey" wrote in message
...
No need to use FIND() at all - just use

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

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

In article , HalB
wrote:

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

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JE McGimpsey" wrote in message
...
No need to use FIND() at all - just use

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

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

In article , HalB
wrote:

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

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
****************************
"Ragdyer" wrote in message
...
Yellow complexion is from all the egg on my face.<vbg

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JE McGimpsey" wrote in message
...
No need to use FIND() at all - just use

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

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

In article , HalB
wrote:

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

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

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


"Ragdyer" wrote:

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"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

Hi, Ron! :)
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"Ron Coderre" wrote in message
...
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