Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using this formula

=IF((LEFT($B26,2)="<p"),0,IF($B26="",0,IF($F26<>"",0,(FIND("""../",$B26)))))

To parse data similar to the following.

<nobr>&nbsp;&nbsp;&nbsp;&nbsp;contractor information</nobr><br> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="../City_Electrical_Inspectors.htm"><b> City Electrical Inspectors</b></a><br>
<nobr>&nbsp;&nbsp;&nbsp;&nbsp;<a href="../City_Electrical_Inspectors.htm"><b>inspection</b></a></nobr><br>

My problem comes in cases such as the first line, in which the line is not a new paragraph nor a link, and my FIND returns an error of #VALUE!

Id like to create an IF test to scan the line for the existence of the pattern in my FIND statement before processing that statement. I figured that looking for an error condition may be the way to go. However, the only way I can envision this is as a self-referential formula, similart to the following pseudocode.

IF(ISERROR($L26)=TRUE,$L26=0,L$26=the-result-of-the-formula-above)

Can this be done with a formula or do I need to use a new helper column?

Thanks.

share|improve this question
up vote 2 down vote accepted

If you're using Excel 2007 or newer, you can wrap the FIND() function in an IFERROR() function.

=IF((LEFT($B26,2)="<p"),0,IF($B26="",0,IF($F26<>"",0,IFERROR(FIND("""../",$B26),0))))

For older versions of Excel that do not support IFERROR(), you can use the following longer solution.

=IF((LEFT($B26,2)="<p"),0,IF($B26="",0,IF($F26<>"",0,IF(ISERROR(FIND("""../",$B26)),0,FIND("""../",$B26))))
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .