Search code examples
regexvbastringquotes

Extract words in quotes from a string

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.

Solution

  • 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