software

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 quot;The cow jumped over the moonquot; is in A1 and you want to know if cow is
present in A1, try this:

=IF(ISNUMBER(SEARCH(quot;cowquot;,A1)),quot;Yesquot;,quot;Noquot;)

quot;man818quot; wrote:

gt; I am trying to find all instances of a word in a set of words in a cell.
gt;
gt; e.g. The cow jumped over the moon
gt;
gt; I want to find all instances of cow.
gt;
gt;

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) gt; 0 Then
lngPos = 1
Do
lngPos = InStr(lngPos, strText, strFind, lngCompare)
If lngPos gt; 0 Then
lngCount = lngCount 1
lngPos = lngPos Len(strFind)
End If
Loop While lngPos gt; 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 = quot;the cow jumped over the moonquot; and substring = quot;cowquot;

You may try adding quot;cowquot; substrings within the string to check.

Hope this helps!

--
Thanks and kind regardsquot;man818quot; wrote:

gt; I am trying to find all instances of a word in a set of words in a cell.
gt;
gt; e.g. The cow jumped over the moon
gt;
gt; I want to find all instances of cow.
gt;
gt;

Additional info:

-String can use the cell address of the original data
-Substring should be enclosed in double quotes
--
Thanks and kind regardsquot;RaymundCGquot; wrote:

gt; Hi!
gt;
gt; If I got right it, you wanted to count the number of occurences of a
gt; substring within a string?
gt;
gt; In that case, a custom function can be made using VBA which goes like this...
gt;
gt; Public Function CountIn(strText As String, strFind As String, _
gt; Optional lngCompare As VbCompareMethod = vbBinaryCompare) As Long
gt;
gt; Dim lngCount As Long
gt; Dim lngPos As Long
gt;
gt; If Len(strFind) gt; 0 Then
gt; lngPos = 1
gt; Do
gt; lngPos = InStr(lngPos, strText, strFind, lngCompare)
gt; If lngPos gt; 0 Then
gt; lngCount = lngCount 1
gt; lngPos = lngPos Len(strFind)
gt; End If
gt; Loop While lngPos gt; 0
gt; Else
gt; lngCount = 0
gt; End If
gt; CountIn = lngCount
gt; End Function
gt;
gt; You'll have to place this in a code module in the VBA window (you may access
gt; this by pressing Alt F11).
gt;
gt; Once inputted the syntax of the custom function would now be entered as...
gt;
gt; =CountIn(string,substring)
gt;
gt; where string = quot;the cow jumped over the moonquot; and substring = quot;cowquot;
gt;
gt; You may try adding quot;cowquot; substrings within the string to check.
gt;
gt; Hope this helps!
gt;
gt; --
gt; Thanks and kind regards
gt;
gt;
gt; quot;man818quot; wrote:
gt;
gt; gt; I am trying to find all instances of a word in a set of words in a cell.
gt; gt;
gt; gt; e.g. The cow jumped over the moon
gt; gt;
gt; gt; I want to find all instances of cow.
gt; gt;
gt; gt;

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 regardsquot;RaymundCGquot; wrote:

gt; Hi!
gt;
gt; If I got right it, you wanted to count the number of occurences of a
gt; substring within a string?
gt;
gt; In that case, a custom function can be made using VBA which goes like this...
gt;
gt; Public Function CountIn(strText As String, strFind As String, _
gt; Optional lngCompare As VbCompareMethod = vbBinaryCompare) As Long
gt;
gt; Dim lngCount As Long
gt; Dim lngPos As Long
gt;
gt; If Len(strFind) gt; 0 Then
gt; lngPos = 1
gt; Do
gt; lngPos = InStr(lngPos, strText, strFind, lngCompare)
gt; If lngPos gt; 0 Then
gt; lngCount = lngCount 1
gt; lngPos = lngPos Len(strFind)
gt; End If
gt; Loop While lngPos gt; 0
gt; Else
gt; lngCount = 0
gt; End If
gt; CountIn = lngCount
gt; End Function
gt;
gt; You'll have to place this in a code module in the VBA window (you may access
gt; this by pressing Alt F11).
gt;
gt; Once inputted the syntax of the custom function would now be entered as...
gt;
gt; =CountIn(string,substring)
gt;
gt; where string = quot;the cow jumped over the moonquot; and substring = quot;cowquot;
gt;
gt; You may try adding quot;cowquot; substrings within the string to check.
gt;
gt; Hope this helps!
gt;
gt; --
gt; Thanks and kind regards
gt;
gt;
gt; quot;man818quot; wrote:
gt;
gt; gt; I am trying to find all instances of a word in a set of words in a cell.
gt; gt;
gt; gt; e.g. The cow jumped over the moon
gt; gt;
gt; gt; I want to find all instances of cow.
gt; gt;
gt; gt;