Regex pattern in VBA to extract the words from the string in escaping quotes

userlWsR 0
Viewed 40

I need to extract the substring in escaping quotes from the string. For example in the string (I open my eyes and said "hello to the "world") I need to get the words "hello to the "world". I'm not familiar with regex and didn't find the proper pattern. The following topic contains the regex patterns to resolve the issue, but they are not for VBA. If someone is in touch with VBA, please help with the right regex VBA pattern for the escaping quotes.

quotes string vba
2 Answers

I guess the easy way of writing that as a Regular expression would be:

""".+"""

Reference to how to use Regex in VBA:
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Tested using this example code

Sub RegexMatchingAndDisplayingAPattern()
Dim stringOne As String
Dim regexOne As Object
Dim theMatches As Object
Dim Match As Object
Set regexOne = New RegExp

regexOne.Pattern = """.+"""
stringOne = "(I open _my eyes_ and said  ""hello to the ""world"")"

Set theMatches = regexOne.Execute(stringOne)

For Each Match In theMatches
  Debug.Print Match.Value
Next

End Sub
Christofer Weber0

You don't need regex to do this, try:

Sub Test()
    Dim s As String
    s = "(I open my eyes and said ""hello to the ""world"")"
    Debug.Print s
    s = Mid(Left(s, InStrRev(s, """")), InStr(s, """"))
    Debug.Print s
End Sub

This prints

(I open my eyes and said "hello to the "world")
"hello to the "world"
GWD0
Related