Search for instances of substring within array

oscarj

New Member
Joined
May 4, 2018
Messages
12
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")

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123apple89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]red_tomato[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]green_apple[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try...

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

or

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

...where C1 contains apple.

Hope this helps!
 
Upvote 0
I would use a UDF to do this, it is so easy to do in VBA:
Code:
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
 
Upvote 0
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
1123apple89
27orange
33pineapple
4green_apple
Sample
 
Upvote 0
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!
 
Upvote 0
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)

Code:
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
ABCDE
1123apple892Apple2
27orange
33pineapple
4green_apple
Sheet1
 
Upvote 0

Forum statistics

Threads
1,223,711
Messages
6,174,028
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top