Function if cell contains "text1" and not "text2"

[Temporary-Failure]

I have tried to make function which is like =takeif("text1";"text2";range)
text1 is what should be found in the cell and text2 is what should'nt be found.

Example:
A1 contains text "E8811, example, superb"
When I write to B1 function =takeif("E88";"uper";A1) result is blank. If "E88" is found but not "uper" then result is A1.

I have now:
Function takeif(txtyes As String, txtno As String, r As Range) As Boolean
takeif = InStr(r.Value, txtyes) > 0 And Not InStr(r.Value, txtno) > 0
End Function

It works ok but it returns FALSE or TRUE. It should return value of the A1 or blank.


EDIT. solved

<code>Function TakeIf(sMust As String, sMustNot As String, rRng As Range) As String Dim bRqd As Boolean bRqd = False If InStr(1, rRng.Value, sMust, vbTextCompare) > 0 Then bRqd = True If InStr(1, rRng.Value, sMustNot, vbTextCompare) > 0 Then bRqd = False If bRqd Then     TakeIf = rRng.Value Else     TakeIf = "" End If  End Function</code></pre>

 

[ZAX]

Try This :

Function takeif(txtyes As String, txtno As String, r As Range) As Boolean
Select Case InStr(r.Value, txtyes) > 0 And Not InStr(r.Value, txtno) > 0
Case True
takeif = r.Value
Case False
takeif = ""
End Select
End Function