Hi!

If I got right it, you wanted to count the number of occurences of a
substring within a string?

In that case, a custom function can be made using VBA which goes like this...

Public Function CountIn(strText As String, strFind As String, _
Optional lngCompare As VbCompareMethod = vbBinaryCompare) As Long

Dim lngCount As Long
Dim lngPos As Long

If Len(strFind) 0 Then
lngPos = 1
Do
lngPos = InStr(lngPos, strText, strFind, lngCompare)
If lngPos 0 Then
lngCount = lngCount + 1
lngPos = lngPos + Len(strFind)
End If
Loop While lngPos 0
Else
lngCount = 0
End If
CountIn = lngCount
End Function

You'll have to place this in a code module in the VBA window (you may access
this by pressing Alt+F11).

Once inputted the syntax of the custom function would now be entered as...

=CountIn(string,substring)

where string = "the cow jumped over the moon" and substring = "cow"

You may try adding "cow" substrings within the string to check.

Hope this helps!

--
Thanks and kind regards


"man818" wrote:

I am trying to find all instances of a word in a set of words in a cell.

e.g. The cow jumped over the moon

I want to find all instances of cow.