Regextract between quotes include commas

user_3545199 0
Viewed 43

I have question about regextract text between quotes in Google Sheets like this :

MY SAMPLE SHEETS

I am able to extract text between quotes, but when it has commas inside quotes, the formula didn't work.

Example : in Column A I had ["Apple","Orange","Banana"] its work fine but when it contains commas like this > ["Apple","Milks, Breads & Vitamins"] it suddenly won't work

this is my formula :

=arrayformula( iferror( regexextract( A2, rept( ".*?""(.+?)""", len(regexreplace(A2, "[^,]", "")) + 1 ) ) ) )

Any help would be appreciated. Thanks

spreadsheet regex
2 Answers

I don't know why doesn't work your RegExp. It looks quite well. Probably there is some obscured rule for commas in this context.

Here is working variant:

=arrayformula( iferror( regexextract( B2:B, rept( ".*?""(.+?)""", len(regexreplace(B2:B, "[^""]", ""))/2 ) ) ) )

It counts quotes instead of commas.

enter image description here

You didn't provide expected result. So, I don't know what exactly you need.

user_142654690

another way

=arrayformula(iferror(split(substitute(REGEXREPLACE(B2:B,"(\[|\])",""),"""",""),",")))
user_150193800
Related