#Value! error
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 |
#Value! error
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 |
#Value! error
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 |
#Value! error
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 |
#Value! error
Voila!
Thanks! Ragdyer wrote: Try this: =IF(ISERR(FIND("DR.",A1,1)),A1,SUBSTITUTE(A1,"DR." ,"DOOR")) |
#Value! error
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")) |
#Value! error
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. |
#Value! error
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. |
#Value! error
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. |
#Value! error
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. |
#Value! error
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 |
#Value! error
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 |
#Value! error
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 |
All times are GMT +1. The time now is 07:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com