number of occurence of a particular substring in a string

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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.

Code:
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
Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
not sure about the complexity of the original requirement, but basis your sample data, why not this?
Excel Workbook
KLM
22MC3626, MC3631 MC3681, MC3646M4
Sheet1
Excel 2003
Cell Formulas
RangeFormula
M22=(LEN(K22)-LEN(SUBSTITUTE(K22,L22,"")))/LEN(L22)
 
Last edited:
Upvote 0
It is possible using Excel's built-in functions.

For a single character:

=LEN(A1)-LEN(SUBSTITUTE(A1,"M",""))

For multiple characters:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"MC","")))/LEN("MC")
 
Upvote 0
i am thankful to the comments of Andrew Poulsom and
sanrv1f. perhaps I went about in a circuitous way . I now understand the use of Len and substitute functions for this problem. thanks once again.

But I had to use the return value of the function in a loop in another macro over a range of cells. .That is why I created the function. Perhaps I could have used these excel functions.
 
Last edited:
Upvote 0
venkat

If you are working in code, then perhaps you could adapt something from this?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CountSubstrings()<br>    <SPAN style="color:#00007F">Dim</SPAN> SplitStr<br>    <SPAN style="color:#00007F">Dim</SPAN> StartStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, SubStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CaseSensitive <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, CaseInsensitive <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    StartStr = "MC3626, MC3631 Mc3681, MC3646"<br>    SubStr = "Mc"<br>    <br>    <SPAN style="color:#007F00">'Use this for case sensitive count ...</SPAN><br>    CaseSensitive = <SPAN style="color:#00007F">UBound</SPAN>(Split(StartStr, SubStr, , 0))<br>    MsgBox "Case sensitive count = " & CaseSensitive<br>    <br>    <SPAN style="color:#007F00">'... or use this for case insensitive count</SPAN><br>    CaseInsensitive = <SPAN style="color:#00007F">UBound</SPAN>(Split(StartStr, SubStr, , 1))<br>    MsgBox "Case insensitive count = " & CaseInsensitive<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Upvote 0
Peter
Thank you very much. Today I have learnt a new vb function "split". I went through the help for this function and some google searches. I now somewhat understand the versatility of this function. I may have to so some more work on thsi There is even a specific reference to "
Counting specific characters in a string"

in
http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/

which I missed in my previous google search.

There is a saying in my native tongue which if translated means

"what one knows is the size of the fist and what one does not know is that of the world"
Thanks once again for iniating me into the "split" function.
 
Upvote 0
It is possible using Excel's built-in functions.
For a single character:
=LEN(A1)-LEN(SUBSTITUTE(A1,"M",""))
For multiple characters:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"MC","")))/LEN("MC")

This last formula with the substitute does not give the right answer if one would like to count the number of a substring in another string when that string contains more then 1 subsequent combinations of the searchstring.
Try this formula with the substring "qq" and the string to be investigated contains "qqq". The answer given is 1 but the right answer should be 2: the cobination of the first 2 q's plus the combination of the second and last q.
Do you have a suggestion how to solve this?
 
Upvote 0
..count the number of a substring in another string when that string contains more then 1 subsequent combinations of the searchstring.
Try this formula with the substring "qq" and the string to be investigated contains "qqq". The answer given is 1 but the right answer should be 2: the cobination of the first 2 q's plus the combination of the second and last q.
Do you have a suggestion how to solve this?
Welcome to the MrExcel board!

See if one of these, copied down, does what you want.
The column B formula is not case-sensitive, the column C formula is.

If this is not what you want, please give more examples, expected results and explanations.

Excel Workbook
ABC
1abcqqq22
2abcQqQdqeqq31
300
4abc12300
5qqqaqqq44
6Qqqqqqq65
Sheet2




Instead of using "qq" in the formula you could place the text you are searching for in a cell and reference that cell in the formula instead.

Excel Workbook
ABCDE
1abcqqq22qq
2abcQqQdqeqq31
300
4abc12300
5qqqaqqq44
6Qqqqqqq65
Sheet3
 
Upvote 0
Hi Peter,
This last one (C1 with the reference to the cell E1) is the correct formula for me.
With the help of https://www.get-digital-help.com/2018/04/17/count-a-given-pattern-in-a-cell-value/ (wich i found after asking you) i do understand the formula.
I actualy want to count the occurrencies of E1 in the whole column A:A
In your example, that should give the answer 12........... Yes i know, i could sum the column C, but is it posible to do this in 1 formula?
Thanks for your time
 
Upvote 0
Just a note on the formula in the link you provided in case you happen to be actually using that somewhere. Whilst it gives the correct result, it is not very robust. Having set up a sheet exactly as shown in that link and you then decide you want a new row at the top of the sheet, as soon as you do that, the formula returns an incorrect result. As you add more rows, the result of the formula change change further.
If, instead of adding a new row at the top, you delete one, the formula returns an error.

For the sum of a column of text, if you have Excel 2016, try something like this
=SUMPRODUCT(--EXACT(MID(TEXTJOIN("|",TRUE,A1:A10),ROW(INDEX(A:A,1):INDEX(A:A,LEN(TEXTJOIN("|",TRUE,A1:A10)))),LEN($E$1)),$E$1))

I would strongly suggest you don't use a whole column reference like A:A where I have used A1:A10, just make the range big enough to cover your data.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

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