Excel formula: If cell contains substring "this" AND does not contain substring "that"
<p>I'm trying to write a function in which a column contains one substring and does not contain another substring.</p>

<p>In the example bellow I would like my function to return 1 if my row contains "some project" AND DOES NOT CONTAIN "overhead".</p>

<pre><code>row| example strings             | desired return value
0  |some project,other project   | 1
1  |some project                 | 1
2  |overhead                     | 0
3  |some project, overhead       | 0
4  |some project, other, boo     | 1
</code></pre>

<p>I was trying to formulate it first with exact strings such as:</p>

<pre><code>=IF(AND((E3="some project"),NOT(E3="overhead")),1,0)
</code></pre>

<p>But this only gives correct results for row 1 and 2 because it only does exact mach for the string instead of matching on the substring. </p>