Extract words in quotes from a string

Question

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

"hello to the "world"

This contains regex patterns to resolve the issue, but not for VBA.

Answer 1

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"

Answer 2

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