CODEWITHSUNDEEP
regexvbams-word
Andreas K
Andreas K

Reputation: 63

VBA regex matching over two lines

I created a VBA macro, which searches regular expressions in txt documents. You have to input a regular eypression in a cell in an excel spreadsheet and the macro will use Word to count the occurences of the regex in a particular txt document. So far everything works great.

Now I am trying to search for phrases like "going to". Which also works fine, as long as it is not spread out over two lines. Potentially, there is a line break between "going" and "to". So I tried patterns like:

going\nto
going[\n]to
going\rto
going\sto

None of them found "going to" spread out over two lines. I also entered it manually in the txt documents and couldn't find it either.

When I only search for \n. The line breaks between "going" and "to" are found correctly.

What am I doing wrong?

Upvotes: 0

Views: 947

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626748

The best way to match whitespace between two words is \s+.

It matches 1 or more whitespace symbols.

Here is a sample VBA piece of code printing 3 "going on"s that are separated with 1) just a CR, 2) CR+LF, 3) spaces.

Sub MatchStrWithWhitespaces()
Dim rx As RegExp
Dim m As MatchCollection
Dim s As String

s = "going" & vbCr + "to and " & "going" & vbCrLf + "to and " & "going    to and "
Set rx = New RegExp
rx.Global = True
rx.Pattern = "going\s+to"
Set m = rx.Execute(s)
If m.Count > 0 Then
  For Each n In m
    Debug.Print m(0)
  Next
End If

End Sub

Upvotes: 1

Related Questions