I think I have everything, but dont know the code for execution.
You see the cells in the picture. I want to delete everything before "Title":" and I have the regular expression for that:
.+(\Title":")
I also want to delete everything after ","URL and I have the regular expression for that too:
\","URL.*
How can I implement this in VBA Script to delete this for every cell in this Excel sheet?
I am in full agreement with GSerg's comment:
You have JSON in the cells. So parse it as JSON.
Regular Expressions isn't really a reliable tool for parsing JSON. Does that mean it can't be done? Well, not exactly. But you are risking coming up with unexpected results.
Your use-case looks pretty simple though. So if you were still wanting to proceed with the Regex method anyway, you could probably work with the following function:
Function getTitleFromJSON(s As String) As String
With CreateObject("VBScript.Regexp")
.Pattern = "{""Title"":""(.*)"",""URL"":"""
getTitleFromJSON = .Execute(s)(0).SubMatches(0)
End With
End Function
You would just use it in a similar fashion to
MsgBox getTitleFromJSON(Sheet1.Range("A5").Value)
.Execute(s)(0) is what returns the entire match of the pattern. But you only want the data in the capturing group (.*), which is where SubMatches(0) comes in.
Once again, Regex isn't the tool you should be using to parse JSON. Unfortunately, VBA lacks native support for parsing JSON, so I can understand the reasoning behind the desire for wanting to shortcut your way with Regex. But just understand that undesirable results may come out of using the easy way out.
