number of occurence of a particular substring in a string

[venkat1926]

A few days back in helping a member of newsgroup I have to find number of occurrence of a particular sub string in a string which is entered in a cell. I tried Google search as well as Mr. excel forum search I was not able to get a satisfactory reference.
So I created a function to solve this problem. I do not know whether
1) this is sufficiently elegant and general function which my peers in the newsgroup approve
2)I hope I am not reinventing the wheel.
3) It will be a useful reference.

If the administrator thinks that this is a not worthwhile post he/she may discard this post. If he/she decides to modify he/she is at liberty to do it.

I am giving the fuction and a example of procedure for using the function.

Private Function char_nr(r As Range, y As String) As Integer
'this counts number of occasions when a sub string occurs in a string
'r is the range where the string is located
'y is the string whose repeated occurence is couned.
Dim j As Integer, m As Integer, x As String
On Error GoTo outsideloop
x = r.Value
j = 0
m = 1
Do

m = WorksheetFunction.Search(y, x, m)
j = j + 1
m = m + 1
Loop
outsideloop:
char_nr = j
End Function

Sub test()
'suppose cell A1 contains this string "MC3626, MC3631 MC3681, MC3646"
'and we want to find out how many Ms are in A1.
Dim rng As Range, z As String
Set rng = Range("a1")
z = InputBox("type character or substring to be counted, in this case M")
MsgBox char_nr(rng, z)
End Sub

 

[oldegrieze]

Unfortunately i heve Office 2016, wich does not support TEXTJOIN
The CONCAT variant is not very handy, becaus my column has a couple of thousand values........

 

[Peter_SSs]

Then perhaps a 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.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function sCount(rngToCheck As Range, strLookFor As String, Optional bIgnoreCase As Boolean) As Long
  Dim s As String
  Dim pos As Long
  
  s = Join(Application.Transpose(rngToCheck), "|")
  Do
    pos = InStr(pos + 1, s, strLookFor, -bIgnoreCase)
    If pos > 0 Then sCount = sCount + 1
  Loop Until pos = 0
End Function

The function assumes that you will use a single, vertical column of values as the first argument of the function.
I have included the option to do a case-insensitive count by adding the third argument as TRUE as shown in cell C2.

Excel Workbook
	A	B	C	D	E
1	abcqqq		12		qq
2	abcQqQdqeqq		15
3
4	abc123
5	qqqaqqq
6	Qqqqqqq
7
Sheet4

 

[oldegrieze]

Thanks, that will do the job!

 

[Peter_SSs]

Thanks, that will do the job!

You're welcome.