String Comparison Pattern Match to Stop at Each New Line

JonXL

Active Member
Joined
Feb 5, 2018
Messages
443
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I have written up a series of procedures that look in each of all the text files within a given directory to find if any of the files contain a particular pattern of text.

The process I have works just fine. Where I am having issues is with finding my string within the text file. What I want to find is "PUSH:" followed by zero or any number of characters then either "Q" or "C" and again followed by any number of characters. I want it to do this without searching across new lines (in the text file those are CRLF). The code I have now for the comparison doesn't work because on lines after "PUSH:" in the file there are words with "Q" and/or "C" and these are being picked up as a match (strTextFile represents the entire text of a particular text file):

VBA Code:
If strTextFile Like "*PUSH*[QC]*" Then

That would match, for example:

Code:
BLAH BLAH PUSH: UJ
CONTINUE LINE

... when I really only want it to match things like these:

Code:
BLAH BLAH PUSH: JCO
CONTINUE LINE

Code:
BLAH BLAH PUSH: Q
CONTINUE LINE

Code:
BLAH BLAH PUSH: IOCQ PULL: OP
CONTINUE LINE

And not these:

Code:
BLAH BLAH PUSH: JP
CONTINUE LINE

Code:
BLAH BLAH PUSH: UI
QUIT

Maybe I need a RegEx? Not really sure. I can loop through the text files one line at a time in which case the code I have above would work fine, but that would be slower so I am trying to do the search on the entire contents of the file in one go.

Any help is appreciated.

Thank you!
Jon
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
821
Office Version
  1. 365
Platform
  1. Windows
I would use RegEx but I would really suggest using an off-the-shelf tool to do this. You seem to be using Excel VBA to write a custom solution that is not related to Excel. There are tools that specialize in this. I use FileLocator Lite to do this kind of thing. It's free and it can do RegEx searches on either content or file names (or both). It is much better than Windows built-in searches and it's fast.

If you want to stick with Excel, use RegEx with the pattern "^.*PUSH.*[QC].*$"

Are you just trying to determine if the file has it, or capture the string and do something with it?
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
443
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I would use RegEx but I would really suggest using an off-the-shelf tool to do this. You seem to be using Excel VBA to write a custom solution that is not related to Excel. There are tools that specialize in this. I use FileLocator Lite to do this kind of thing. It's free and it can do RegEx searches on either content or file names (or both). It is much better than Windows built-in searches and it's fast.

Thank you for the suggestion. Downloading a special file for this isn't something I can do. And this isn't something I would do often, so I just went with something I already had. As to this not having anything to do with Excel, that's true and I even wondered about putting this in a different forum but since I wrote the script in Excel I put it here.

If you want to stick with Excel, use RegEx with the pattern "^.*PUSH.*[QC].*$"

Thank you. I will give that a try. Can you tell me how I would use that? My string for the whole text file is strTextFile.

Are you just trying to determine if the file has it, or capture the string and do something with it?

If I could have it return the text that matched the pattern, that would be extra helpful but at a minimum it needs to just be T/F whether it finds the match.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
821
Office Version
  1. 365
Platform
  1. Windows
Here is a general-purpose function. I have not tested this with the exact situation you are describing. I believe ^ should match the beginning of a line and $ the end, rather than the beginning and end of the entire string, but I could be mistaken. If so then we need to add characters to look for line breaks.

VBA Code:
If RegExpTest(strTextFile, "^.*PUSH.*[QC].*$") Then
' The rest of your logic here
End If

' Look for pattern FindWhat in string FindIn. Return TRUE if found, otherwise FALSE
Function RegExpTest(FindIn As String, _
                    FindWhat As String, _
                    Optional IgnoreCase As Boolean = False) As Boolean
   
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
   
    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RegExpTest = RE.Test(FindIn)
   
End Function
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
443
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The expression I ended up with was \bPUSH.*[QC].*\b and I used the 'matching and displaying a pattern' function from here:


This worked out well because, while I wasn't originally going to worry about the return text, I was able to rework that function to list each match and its file on a sheet which I then used to validate the data precisely from my other system.

Thank you for the help. I think I am now a little closer to understanding RegEx!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,590
Messages
5,625,674
Members
416,125
Latest member
NeedExcelHelp2021

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
Top