How to parse a single value out of a JSON or CSV string in Microsoft Excel? Or Google Sheets?

Question

I will have hundreds of cells which will use a webservice call to obtain stock data. However, the data is only returned in JSON or CSV format. Using one example, how can I obtain only the "uHigh" value from each dynamic JSON or CSV string, given the example below and knowing that the length of the number value could be different each time?

[{"date":"2020-01-22","uClose":1485.95,"uOpen":1491,"uHigh":1503.21,"uLow":1484.93,"uVolume":1610846,"close":1485.95,"open":1491,"high":1503.21,"low":1484.93,"volume":1610846,"change":1.55,"changePercent":0.1044,"label":"Jan 22","changeOverTime":0.001044}]

or I can parse CSV such as

"date,uClose,uOpen,uHigh,uLow,uVolume,close,open,high,low,volume,change,changePercent,label,changeOverTime,symbol
2020-01-22,1485.95,1491,1503.21,1484.93,1610846,1485.95,1491,1503.21,1484.93,1610846,1.55,0.1044,Jan 22,0.001044,GOOG"

Excel offers a FILTERXML method but not one for JSON or CSV to my knowledge, so what can I put in my "=something-here" cell value to parse out just the uHigh value?

I will use Google Sheets if that helps.

Answer 1

For Google Sheets

If you want to extract from JSON in Google Sheets, you can use REGEXEXTRACT like this:

• Put JSON in A1
• Put the key you want to lookup in B1
• Put this formula in C1: =REGEXEXTRACT(A1, """" & B1 & """ *: *(.*?),")

You can test it out by changing the lookup key in B1.

If you want to extract from CSV, you can use this:

• Put CSV in A1
• Put the key you want to lookup in B1
• Put this formula in C1: =VLOOKUP(B1, TRANSPOSE(ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(A1, char(10))), ","))), 2, FALSE)

You can test it out by changing the lookup key in B1.

Disclaimer: Both of these will only work if the JSON/CSV are valid/standard formats. A few things to watch out for:

• The JSON one will fail if the value is quoted and has a comma. For example: {"key" : "value, value"}.
• The CSV one will fail if the field separator, comma, is also used in a key/value.

For Excel

If you want to extract from JSON in Excel, you can use a mix of SEARCH, FIND, and MID like this:

• Put JSON in A1
• Put the key you want to lookup in B1
• Put this formula in C1: =TRIM(MID(A1,FIND(":", A1, FIND(""""&B1&"""", A1))+1,SEARCH(",",A1,FIND(":", A1, FIND(""""&B1&"""", A1)))-FIND(":", A1, FIND(""""&B1&"""", A1))-1))

Answer 2

DISCLAIMER: you'd need Excel Labs/AFE/some other formula IDE to manipulate the "low-level" functions, and the solutions provided use spill arrays.

Solution:

1. Add the Excel Labs add-in,
2. Import this gist using the add-in, then
3. Add the following function to a cell:

=XLOOKUP(
  key,
  module.getJSONKeys(JSONVal),
  module.getJSONValues(JSONVal)
)

key: your key, no quotes required JSONVal: your JSON value, no quotes required

Example:

//Add in cells
A1: {"key": "this is a value", "key2": "another value"}
A2: key
A3: =XLOOKUP(
  key,
  module.getJSONKeys(JSONVal),
  module.getJSONValues(JSONVal)
) // returns: this is a value

Notes:

• won't handle JSON arrays, only objects
• does not (yet) extract nested objects
• can handle quotes and commas in the JSON string
• there are a bunch more use/edge-cases that I haven't implemented/thought of

Answer 3

Extract value after wording "uHigh"

Put JSON format type data in A2

In B2, enter formula :

=LOOKUP(9^9,0+MID(A2,SEARCH("uHigh",A2)+7,ROW($1:$99)))

Answer 4

I've learned one can use Split on a CSV string and then pull the needed item out of the resulting string array by number. This has been functionalized by another person as seen here:

find the nth item in a comma separated list

Of course, it would still be nice to do it in one simpler call.