Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.