-1

I have a Geojson in a google sheet cell. I'd like to extract coordinates from that. I tried using Regexextract function but I'm really having trouble in making the regex work.

This is a sample structure: 

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              62.22656249999999,
              61.438767493682825
            ],
            [
              54.140625,
              44.33956524809713
            ],
            [
              79.453125,
              48.45835188280866
            ],
            [
              62.22656249999999,
              61.438767493682825
            ]
          ]
        ]
      }
    }
  ]
}

what I want to obtain is 

[[[62.22656249999999,61.438767493682825],[54.140625,44.33956524809713],[79.453125,48.45835188280866],[62.22656249999999,61.438767493682825]]]

Can you help me with that?

Thanks

Gusepo
  • 837
  • 2
  • 13
  • 26

1 Answers1

2
=REGEXREPLACE(A5,"[^\[\]\.\d+,]",)

Anything except []., and digits gives a close approximation.

EDIT:

=REGEXEXTRACT(A5,"(?s)(\[\s+\[\s+\[.*\]\s+\]\s+\])")
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks, it gives an approximation but some of my GEOJsons have other numbers and square brackets. Is there a way I can find everything inside within "[[[" and "]]]" ? – Gusepo Feb 15 '18 at 09:00
  • is there a way to search only what's inside 3 consecutive square brackets, now I also get the digits inside single square brackets. – Gusepo Feb 15 '18 at 12:58