REGEX Query in Google Sheets

Question

Having a bit of a headache with a scraping scenario I'm trying in Google Sheets.

In a nutshell, we want to use Google Sheets with ImportXML to create scraped feed from clients' websites pulling product details.

Here is a link to the smaller version of the doc. https://docs.google.com/a/sprt.co.za/spreadsheets/d/1dSbglYniWa_cijb6yDty576j33CTk9Cf8J38a3VXHSU/edit?usp=sharing

Currently this specific client only has the Item Price, etc details in a text area in the code. So when I use =ImportXml($C$2, "//textarea") it gives me the entire text area across two cells. From these cells, actually only the second one I need to pull out details but I am pretty stuck on the Regex on a piece if data this big.

" { ""id"": ""061013AACI9"", ""productId"": ""061013AACI9"", ""name"": ""VANS MEN'S 
PERFORATED LEATHER ERA"", ""price"": ""R 799.00"", ""oldPrice"": """", ""brand"": 
""Vans"", ""brandURL"": ""/plp/vans/_/N-1z140je"", ""defaultImages"": [ ], 
""images"": [ { ""thumb"": 
""http://tfgsrv.wigroup.co/06/Thumbnail/31460739.jpg"", ""large"": 
""http://tfgsrv.wigroup.co/06/Detail/31460739.jpg"" } , { ""thumb"": 
""http://tfgsrv.wigroup.co/06/ThumbnailAlternative/31460739_01.jpg"", 
""large"": ""http://tfgsrv.wigroup.co/06/DetailAlternative/31460739_01.jpg"" } 
, { ""thumb"": 
""http://tfgsrv.wigroup.co/06/ThumbnailAlternative/31460739_02.jpg"", 
""large"": ""http://tfgsrv.wigroup.co/06/DetailAlternative/31460739_02.jpg"" } 
, { ""thumb"": 
""http://tfgsrv.wigroup.co/06/ThumbnailAlternative/31460739_03.jpg"", 
""large"": ""http://tfgsrv.wigroup.co/06/DetailAlternative/31460739_03.jpg"" } 
], ""transientProfile"": ""true"", ""wishListId"": ""anonymous"", ""colors"": [ { 
""id"": ""31460739"", ""name"": ""White"", ""path"": 
""http://tfgsrv.wigroup.co/06/ColourSwatch/31460739_SW.jpg"", ""activeColor"" : 
true, ""available"" : true } ], ""sizes"": [ { ""id"": ""31460740_06"", ""name"": 
""6"", ""available"": false } , { ""id"": ""31460741_06"", ""name"": ""7"", 
""available"": true } , { ""id"": ""31460742_06"", ""name"": ""8"", ""available"": true 
} , { ""id"": ""31460743_06"", ""name"": ""9"", ""available"": false } , { ""id"": 
""31460744_06"", ""name"": ""10"", ""available"": true } , { ""id"": ""31460745_06"", 
""name"": ""11"", ""available"": false } ], ""productType"" : ""ColourSize"" } "

I need to pull out the R 799.00 value from that mess. So if anyone is willing to help out. Because frankly my talent and skill has run it's course in trying to navigate that with RegEx.

Your doc is not public, no one can view that docs.google.com/a/sprt.co.za/spreadsheets/d/… — Tim007
– Tim007, Commented Mar 11, 2016 at 7:58

Tim007 · Accepted Answer · 2016-03-11 07:57:21Z

1

Try this:

""price"":\s""([^"]+)""

Demo

Output:

MATCH 1
1.  [124-132]   `R 799.00`

answered Mar 11, 2016 at 7:57

Tim007

2,5571 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Charl Over a year ago

RegEx working nicely, thanks. ALso works for pulling other values from the Text Area so double bonus. Now trying to get the RegEx to work in Sheets. =REGEXEXTRACT(E3,'""price"":\s""([^"]+)""') Isn't giving me any joy.

Tim007 Over a year ago

try this =REGEXEXTRACT(E3,"price.*?:\s.(.*?)..,")

Tim007 Over a year ago

=REGEXEXTRACT(E3,"price\W*:\W*(.*?)\W?,") RE2 regular expression syntax reference re2.googlecode.com/hg/doc/syntax.html

Charl Over a year ago

Fantastic. Thanks a lot!

Collectives™ on Stack Overflow

REGEX Query in Google Sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related