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.

Additional info:

-String can use the cell address of the original data
-Substring should be enclosed in double quotes
--
Thanks and kind regards


"RaymundCG" wrote:

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.

Additional info 2

This custom function is case sensitive; use the ff syntax as applicable:

=CountIn(string,substring,0) for case sensitive substring entries or
=CountIn(string,substring,1) if not case sensitive

--
Thanks and kind regards


"RaymundCG" wrote:

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.