Search for instances of substring within array

[oscarj]

Hello,

I am trying to search a large list for the number of occurrences of a word located anywhere within a string, but can't figure out how to do so with countif and find.

The list would look something like this but longer: (Searching for the number of occurrences of "apple")

	A	B
1	123apple89
2	7orange
3	red_tomato
4	green_apple

<tbody>
</tbody>

and return 2, since apple was listed twice in this case. Any help on this topic would be greatly appreciated!

 

[Domenic]

Try...

=COUNTIF(A1:A4,"*apple*")

or

=COUNTIF(A1:A4,"*"&C1&"*")

...where C1 contains apple.

Hope this helps!

 

[offthelip]

I would use a UDF to do this, it is so easy to do in VBA:

Function cntfnd(Inp As Range, txt As String)
cnt = 0
Inarr = Inp.Value
For i = 1 To UBound(Inarr, 1)
  If InStr(Inarr(i, 1), txt) > 0 Then
   cnt = cnt + 1
  End If
Next i
cntfnd = cnt
End Function

 

[oscarj]

Didn't know that was an option for searching within strings! Thanks!

 

[Peter_SSs]

Hmm, just checking, with either method suggested, are you comfortable that in searching for "apple" in this sample data the result is 3, rather than 2?

Book1
	A
1	123apple89
2	7orange
3	3pineapple
4	green_apple
Sample

 

[oscarj]

Good question. In my specific situation it will be a word that wouldn't appear like that within another word, as apple was just an example. But that would be a major concern otherwise. Thank you for the consideration!

 

[Peter_SSs]

Good question. In my specific situation it will be a word that wouldn't appear like that within another word, as apple was just an example. But that would be a major concern otherwise. Thank you for the consideration!

Well, just in case it becomes an issue, you could use this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below. (Two meythods shown)
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function CountWords(r As Range, sWord As String) As Long
  Static RX As Object
  Dim a As Variant, itm As Variant
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.IgnoreCase = True
  End If
  RX.Pattern = "(\b|[^a-z])(" & sWord & ")(\b|[^a-z])"
  a = r.Value
  For Each itm In a
    If RX.test(itm) Then CountWords = CountWords + 1
  Next itm
End Function

Excel Workbook
	A	B	C	D	E
1	123apple89	2		Apple	2
2	7orange
3	3pineapple
4	green_apple
Sheet1