no of occurances of sring in file

[srihari04]

I have a text file. I need a macro which will take input string from user and prints no of occurance of that string in a line.

Exanple: hari bnsnnndn hari nbns, hari xnndd dd ;
hari bnsnnndn hari nbns, hari xnndd dd ;
hari bnsnnndn hari nbns, xnndd dd ;


input string :hari
no of occurance: 3
3
2

[Comintern]

Splitting the string and finding the UBound of the resulting array is probably the easiest to code:

Private Function NumStrings(sSource As String, sMatch As String) As Long
 
    Dim sTemp() As String
    
    sTemp = Split(sSource, sMatch)
    NumStrings = UBound(sTemp)
 
End Function

[Shuja Ali]

@Comintern: You probably misunderstood OP. He wants to get the number of occurences of a particular string in a line.

Here is a function that will return the number of occurences of a particular string in a line.

'Function : checks the number of occurences of sToSearch in sLine
'returns the number of occurences
Public Function checkOccurences(ByVal sLine As String, ByVal sToSearch As String) As Long
    
    Dim searchPoistion As Long
    searchPoistion = 1
    
    Dim numOfOccurences As Long
    numOfOccurences = 0
    'get the position of the sToSearch in sLine
    searchPoistion = InStr(searchPoistion, sLine, sToSearch)
    'if position <> 0 then first occurence found
    Do While searchPoistion > 0
        'increment +1
        numOfOccurences = numOfOccurences + 1
        'add the position of sToSearch with length of sToSearch
        'so that it can find the next occurence
        searchPoistion = searchPoistion + Len(sToSearch)
        searchPoistion = InStr(searchPoistion, sLine, sToSearch)
    Loop        'loop till we don't have any more occurences
    'return the number of occurences.
    checkOccurences = numOfOccurences
End Function

You can call this function like this

MsgBox checkOccurences("hari bnsnnndn hari nbns, hari xnndd dd ;", "hari")

[Comintern]

@Comintern: You probably misunderstood OP. He wants to get the number of occurences of a particular string in a line.

Try the code I posted--it does exactly the same thing. If you use split on the line using the substring as a delimiter:

Before split:
"hari bnsnnndn hari nbns, hari xnndd dd ;"

After split with "hari" delimiter:
element 0 = ""
element 1 = " bnsnnndn "
element 2 = " nbns, "
element 3 = " xnndd dd ;"

The UBound will always equal the number of occurrences.

[Shuja Ali]

Try the code I posted--it does exactly the same thing. If you use split on the line using the substring as a delimiter:

Before split:
"hari bnsnnndn hari nbns, hari xnndd dd ;"

After split with "hari" delimiter:
element 0 = ""
element 1 = " bnsnnndn "
element 2 = " nbns, "
element 3 = " xnndd dd ;"

The UBound will always equal the number of occurrences.

I didn't see that. I just saw split in there.

One more reason why one shouldn't jump to conclusions.

[bushmobile]

You don't even need the variable in yours, Comintern:

Private Function NumStrings(sSource As String, sMatch As String) As Long
    NumStrings = UBound(Split(sSource, sMatch))
End Function

there's also another way of doing it on one line:

Private Function NumStrings(ByVal ssource As String, ByVal sMatch As String) As Long
    NumStrings = (Len(ssource) - Len(Replace(ssource, sMatch, vbNullString))) / Len(sMatch)
End Function

All of the codes suggested here will not count words, just case you that was actually what you were after, for example:

"hari bnsnharinndn hari nbns, hari xnndd dd ;" will give 4 instances of the string 'hari', but there are only three instances of the word 'hari'

[Comintern]

You don't even need the variable in yours, Comintern

Yeah, I know--I just do it from force of habit. I seem to remember reading somewhere that UBound leaks memory when it is passed the result of the function instead of an explicit array. Something about how the array gets garbage collected--I want to say that a temp array gets built for the call to UBound, but never gets de-allocated when it should normally lose scope. Ahhh yes, I found the link.

[bushmobile]

I didn't know that stupid bugs

If you're interested, I did a speed test for the split (non-memory leaking version) and the replace methods.

Short string was just 39 characters, with the search string present 3 times. Long string was 50 short strings concatenated together. 100000 repeats of each function:

Len & Replace - Short String
------------------
 0.319812154896782 
 0.318800853181061 
 0.321015659811512 

Split - Short String
------------------
 0.471079704264089 
 0.470355869251539 
 0.469071907183734 

Len & Replace - Long String
------------------
 7.04909339036107 
 6.93816476675108 
 6.81726029425528 

Split - Long String
------------------
 11.4743161491195 
 11.500835720741 
 11.6098705282375

[Comintern]

I didn't know that stupid bugs

If you're interested, I did a speed test for the split and the replace methods.

Short string was just 39 characters, with the search string present 3 times. Long string was 50 short strings concatenated together. 100000 repeats of each function:
[SNIP]

Not surprised at all. Your method doesn't have to build an array.

[bushmobile]

I suppose, but i've never found Replace to be particularly speedy. I was expecting the split to be faster. Oh well

[da_silvy]

I got the impression from the first post that he wanted it printed out for each line of the (multiline) input

e.g.

line 1 has 3 occurences
line 2 has 3 occurences etc

in which case you would have to split by the vbcrlf character, then loop through and split by the word you're looking for

dim lines() as string, i as integer
input = somestring
lines = split(somestring, vbcrlf)
for i = 0 to ubound(lines)
debug.print("Line " & i + 1 & " occurences: " & ubound(split(lines(i), "hari")) - 1)
next

Please forgive any syntax / coding errors, it's been a while :s

[yrwyddfa]

Sorry chaps. The number of instances of a string with a string (so get the file lines individually inside a string, first) has been well hammered here I think Penegate, and myself got as much mileage out of this as is humanly possible

[srihari04]

Thanks for ur mail. Exactly I wanted it only i.e the macro should print out for each line of the (multiline) output.

Actually I have a text file with some 100 lines , with each line is delimited by ';'. I need a macro that will print out for each line , no of occurances of a particular string .


Answer should look like
example: line 1 has 3 occurences
line 2 has 3 occurences etc
.
.THanks & regards

I got the impression from the first post that he wanted it printed out for each line of the (multiline) input

e.g.

line 1 has 3 occurences
line 2 has 3 occurences etc

in which case you would have to split by the vbcrlf character, then loop through and split by the word you're looking for

dim lines() as string, i as integer
input = somestring
lines = split(somestring, vbcrlf)
for i = 0 to ubound(lines)
debug.print("Line " & i + 1 & " occurences: " & ubound(split(lines(i), "hari")) - 1)
next

Please forgive any syntax / coding errors, it's been a while :s

[da_silvy]

then replace vbcrlf with ";" in the code above

i'd also be inclined to do something like
lines = split(replace(somestring, vbcrlf, ""), ";")

so you get rid of all the new line characters then you split based on the ";"

[Al42]

Ahhh yes, I found the link.

Typical Microshaft. "We know we sold you a broken product. This is how it's broken. That's too bad. Fix it? You're funny."