CODEWITHSUNDEEP
jsonexcelexcel-formula
Andrew Mizuno
Andrew Mizuno

Reputation: 13

Extract Value from JSON string in EXCEL

I have hundreds of single line JSON strings and I am trying to extract the value related to ActiveServices.

However, not all the cells start the same, some strings start like this:

[{"id":"ActiveServices","type":"count","value":"1"},{"id":"HasActiveService","type":"boolean","value":"true"}]

and others are like this:

[{"id":"HasActiveService","type":"boolean","value":"true"},{"id":"ActiveServices","type":"count","value":"16"}]

and like this:

[{"id":"HasActiveIssue","type":"boolean","value":"true"},{"id":"HasActiveNewIssue","type":"boolean","value":"true"},{"id":"ActiveLowIssues","type":"count","value":"1"},{"id":"ActiveServices","type":"count","value":"1"},{"id":"HasActiveService","type":"boolean","value":"true"},{"id":"ActiveNewIssues","type":"count","value":"1"},{"id":"ActiveIssues","type":"count","value":"1"},{"id":"HasActiveLowIssue","type":"boolean","value":"true"}]

My current formula only works for the 1st and 3rd JSON string structures.

This is my current formula:

=TRIM(MID(P4,FIND("value", P4, FIND(""""&Q4&"""", P4))+8,SEARCH(",",P4,FIND("value", P4, FIND(""""&Q4&"""", P4)))-FIND("value", P4, FIND(""""&Q4&"""", P4))-10))

P4 has my JSON string, Q4 has the word I am finding "ActiveServices"

Any help appreciated. Thanks

Upvotes: 0

Views: 1439

Answers (1)

JvdV
JvdV

Reputation: 75870

There are shorter ways of doing this I'm sure, but I'm thinking it would be rather neat to first split a JSON in an actual array, then filter out the data needed:

=LET(a,TEXTSPLIT(P4,{""":""",""","""},{"[{""","""},{""","""}]"},1),FILTER(TAKE(a,,-1),INDEX(a,,2)=Q4))

The idea here is to first TEXTSPLIT() the JSON string into an array of values, so:

[{"id":"ActiveServices","type":"count","value":"1"},{"id":"HasActiveService","type":"boolean","value":"true"}]

Becomes:
id ActiveServices type count value 1
id HasActiveService type boolean value true

The array is saved in a variable called a which we then FILTER() to TAKE() the last element (column) which represents the values we are after and test the 2nd element (INDEX(a,,2)) which represents the ID we are after.

Upvotes: 3

Related Questions