CODEWITHSUNDEEP
javascriptexcelvba
Rich Stevens
Rich Stevens

Reputation: 609

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

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:

enter image description here

Upvotes: 0

Views: 67

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 1

Related Questions