Retrieve keywords in between different format of quotation marks-CodePudding

Hi this is my first time to post here.

I try to retrieve a keywords in between the double quotation mark but I found that some data including two ''single quotations marks and also including different format of double quotations.I tried to use formula below, but it only able to retrieve the keyword if the data has proper double quotations marks.

=ArrayFormula(REGEXEXTRACT(B2:B,"\""(.*?)\"""))

To better understand, please refer to the image I have attached below. I wonder how can I have better formula to capture keywords. or how I should I replace all this incorrect quotation marks with the correct one in order for my formula to work ?

enter image description here

CodePudding user response：

try:

=INDEX(REGEXEXTRACT(B2:B, "(""|“|'')(.*)(""|”|'')"),,2)