Hi,

I am looking to run a count (or similar) function to find cells that contain two words that are not immediately following.

E.g.
Cell B2: "this cell contains text"
Cell B3: "this cell contains no text"
Cell B4: "this table contains no text"

Count (how many times "no" and "cell" appear in the same cell). =1

Can anybody advise such a function?

Hi,

Am Wed, 11 Jun 2014 13:28:31 +0100 schrieb masking:

Cell B2: "this cell contains text"
Cell B3: "this cell contains no text"
Cell B4: "this table contains no text"

try following UDF:

Function myWords(myRng As Range, ParamArray searchW()) As Long
Dim rngC As Range
Dim i As Long, counter As Long

For Each rngC In myRng
counter = 0
For i = LBound(searchW) To UBound(searchW)
If InStr(rngC, searchW(i)) Then
counter = counter + 1
End If
Next
If counter = UBound(searchW) + 1 Then
myWords = myWords + 1
End If
Next
End Function

and call the function into the sheet with:
=myWords(B1:B100,"cell","no")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional