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.

Do you want to know if it's present or how many times it's present in the
cell.

If "The cow jumped over the moon" is in A1 and you want to know if cow is
present in A1, try this:

=IF(ISNUMBER(SEARCH("cow",A1)),"Yes","No")

"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.

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.