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.
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
