3

I wanted to use the formula from this topic to extract "count" value in this JSON from a Google Sheet cell.

[{"year":2022,"month":2,"count":720},{"year":2022,"month":1,"count":720}, 
{"year":2021,"month":12,"count":480},{"year":2021,"month":11,"count":590}, 
{"year":2021,"month":10,"count":590},{"year":2021,"month":9,"count":590}, 
{"year":2021,"month":8,"count":320},{"year":2021,"month":7,"count":390}, 
{"year":2021,"month":6,"count":590},{"year":2021,"month":5,"count":590}, 
{"year":2021,"month":4,"count":720},{"year":2021,"month":3,"count":590}]

I've tried to build the correct regex but without success since I don't understand how the regex is build.

1 Answer 1

2
=TRANSPOSE(
  REGEXEXTRACT(
    A1,
    "\" & REGEXREPLACE(
            A1,
            CHAR(34)&"count" & CHAR(34) & ":(\d+)",
            CHAR(34)&"count" & CHAR(34) & ":($1)"
            )
  )
)

I took inspiration from here instead: https://stackoverflow.com/questions/43432409/multiple-regex-matches-in-google-sheets-formula. It's really clever and once you understand it, You'll end up wanting to use it all the time.

This will spit out a column (assuming you want it to be a column, if you don't, then unwrap it from the transpose function,) containing all values of count. A1 btw is the JSON string. I'm assuming that the JSON is all in a single cell.

I made a couple of sneaky changes here, first that "\" at line 4. I had to do since brackets are special characters in Regex. I also had to use CHAR(34) which is the equivalent of a double quotation mark. I couldn't find a way to escape it, so there's the quick, sloppy way of doing it. One last thing is that I placed the capture group only on the actual values. /"count":(\d+)/ is then replaced with "count":($1)". That then allows it to become a regex itself! (that technique I stole from the link I shared above.)

In the end, Regex is a monstrous thing to maintain, so don't beat yourself up if you can't figure it out.

1
  • 2
    Thanks a lot it works :)
    – Rafaleur
    Mar 24, 2022 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.