Excel - json data - return certain elements to adjacent cells formula/macro

Question

I have a lot of json data in rows and need to extract certain pieces of data from it in Excel, is there a way to do this? I have provided a sample of a typical cell, to keep it simple, how would I extract the "Lat" and "lng" values from this and place them in the adjacent cells. So let's say the json is in cell D2, I need the 'lat' in E3 and 'Lng' in F3:

projectId:'5571511970726f3903000000',
lat:13.737738,
lng:100.566147,
destinations:[{"id":"57bc75550ce0fe28af001609","name":"BrownEyesRestaurant","category_name":"restaurant","category_class_name":"restaurants","lat":13.737875,"lng":100.566806,"travel_time":"lessthanaminutebyfoot","distance_human":"39m","distance_in_meters":39},{"id":"57bc75550ce0fe28af00160a","name":"HanaHana","category_name":"restaurant","category_class_name":"restaurants","lat":13.738215,"lng":100.566701,"travel_time":"1minutebyfoot","distance_human":"48m","distance_in_meters":48},{"id":"57bc75550ce0fe28af001602","name":"7-Eleven","category_name":"convenience_store","category_class_name":"shopping","lat":13.73763,"lng":100.566934,"travel_time":"1minutebyfoot","distance_human":"55m","distance_in_meters":55},{"id":"57bc75550ce0fe28af001636","name":"GoldenShrine","category_name":"pokestop","category_class_name":"pokemon_places","lat":13.737763,"lng":100.567128,"travel_time":"1minutebyfoot","distance_human":"74m","distance_in_meters":74},{"id":"57bc75550ce0fe28af00160d","name":"FatFishSeafoodBistro","category_name":"restaurant","category_class_name":"restaurants","lat":13.737128,"lng":100.567127,"travel_time":"2minutesbyfoot","distance_human":"130m","distance_in_meters":126},{"id":"57bc75550ce0fe28af001635","name":"NARZStoneBalls","category_name":"pokestop","category_class_name":"pokemon_places","lat":13.738174,"lng":100.56538,"travel_time":"2minutesbyfoot","distance_human":"140m","distance_in_meters":142},

Example of desired output on screengrab:

Possible duplicate of [Parsing JSON in Excel VBA](https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba) — John Coleman, Aug 20 '17 at 12:50

Dy.Lee · Answer 1 · 2017-08-20T13:08:03.367

1

If you don't intent to extract json, try this code.

Sub test()
    Dim s As String
    Dim vSplit

    s = Range("d2")
    vSplit = Split(s, ",")
    Range("e2") = Val(Replace(vSplit(1), "lat:", ""))
    Range("f2") = Val(Replace(vSplit(2), "lng:", ""))

End Sub

Edit

Sub test()
    Dim s As String
    Dim vSplit
    Dim vDB, vR()
    Dim i As Long, n As Long
    vDB = Range("d2", Range("d" & Rows.Count).End(xlUp))
    n = UBound(vDB, 1)
    ReDim vR(1 To n, 1 To 2)
    For i = 1 To n
        s = vDB(i, 1)
        vSplit = Split(s, ",")
        vR(i, 1) = Val(Replace(vSplit(1), "lat:", ""))
        vR(i, 2) = Val(Replace(vSplit(2), "lng:", ""))
    Next i
    Range("e2").Resize(n, 2) = vR
End Sub

edited Aug 20 '17 at 13:08

answered Aug 20 '17 at 13:00

Dy.Lee

7,527
1
12
14

That's great, sorry, I should have said: how do I perform this all the way down my Excel sheet, I have about 2400 rows you see. Thanks. – Rich Stevens Aug 20 '17 at 13:02
I get an "out of range error" and the debug yellow highlights this row in the code: "vR(i, 1) = Val(Replace(vSplit(1), "lat:", ""))" – Rich Stevens Aug 20 '17 at 13:12
@RichStevens, There should be same type data in Colum D – Dy.Lee Aug 20 '17 at 13:16
It is the same from cell D2 to D2517, of course, different lats and lngs but the exact same structure – Rich Stevens Aug 20 '17 at 13:18
where debug stop line , check the i number and check your cell data. – Dy.Lee Aug 20 '17 at 13:26
cell row is i+1. – Dy.Lee Aug 20 '17 at 13:27
Doesn't process any data at all. When I click 'debug', I am taken to the code, on the code this line is highlighted in yellow: " vR(i, 1) = Val(Replace(vSplit(1), "lat:", ""))" – Rich Stevens Aug 20 '17 at 14:55

Excel - json data - return certain elements to adjacent cells formula/macro

1 Answers1