VBA - Count specific string in text file

[Spurious]

Hello all,

I am looking for a way to count all the occurences of a specific string.
The problem is, the string might be seperated by a new line or tab and I want the macro to count it as well as a regular whitespace.

Is there a way?

 

[Firefly2012]

Where is this text file? Is it sat in a directory somewhere or do you have it open in Excel?

Easiest way would be to run a replace on the text file contents and simply replace all instances of Tabs or LineFeeds (possibly Carriage Returns also) with a space before processing the count of this specific string

 

[Spurious]

Yeah, I was thinking about this as well.

I forgot to mention one thing:
Sometimes, the text is structured the following way:
T h i s t h e t e x t

Is there a way that might circumvent this problem?

 

[Firefly2012]

What problem? You need to explain what data you have and what string you need to count.

Did you perhaps mean that your specific string will be

Thisthetext

but in the file it might appear as

T h i s t h e t e x t

but you still want this to count as a positive match?

 

[Spurious]

Exactly, I have to analyze the file and might have occurences of this, where the file has spaces in between the letters.

 

[Firefly2012]

I have created the following function that seems to work without replacing any of the line feeds, carriage returns or tabs. The Sub shows a text file being (completely) read into a string variable, then the function used to count the instances of the passed in string.

Please note that because of the way the function works (regular expression syntax) you have to be careful of the match expression you pass in (it will work without problems if you are passing in simple digits and numerals). If this won't always be the case, the modification to adapt should be straightforward - I'm just being lazy.

Give it a try and let me know how you get on:

Sub test()
Dim strFileText As String

strFileText = CreateObject("scripting.filesystemobject"). _
    OpenTextFile("C:\Users\Firefly\Documents\MrExcel\Test\Test_Count_String.txt", ForReading).ReadAll

MsgBox Count_String(strFileText, "things", False, True)


End Sub


Function Count_String(ByVal strSearch As String, _
    ByVal strMatch As String, _
    Optional blnCaseSensitive As Boolean = False, _
    Optional blnAddWhiteSpace As Boolean = False) As Long
    
    Dim i As Long
    
    If blnAddWhiteSpace And Len(strMatch) > 1 Then
        For i = Len(strMatch) - 1 To 1 Step -1
            strMatch = Left$(strMatch, i) & "\s*" & Mid$(strMatch, i + 1)
        Next i
    End If
    
    With CreateObject("vbscript.regexp")
        .ignorecase = Not blnCaseSensitive
        .Global = True
        .Pattern = strMatch
        Count_String = .Execute(strSearch).Count
    End With
    
End Function

The function takes 4 arguments:
First is the text file contents (held in a single string variable)
Second is the string you want to count the occurrences of in the text file
Third is a boolean indicating whether the match should be case sensitive (True) or not (False)
Fourth is a boolean indicating whether whitespace (spaces, tabs, new lines, carriage returns) between characters of the match string should be ignored (True - yes, ignore)