Parsing a non-nested JSON string in Google Sheets using formulas only

I have a cell with the following JSON data:

``````{"rev2_0": "2,058", "rev2_1": "2,099", "rev2_2": "2,141", "rev2_3": "2,184", "revchg3_0": "3%", "revchg3_1": "6%"}
``````

In another cell, I want to get the value of `rev2_1` but I don't want to run a script. I just want to use the built-in formulas to get it.

• I think that this question should not be moved from SO as google sheets formulas are on topic there too. By the other hand this is not really a good question as it doesn't describe the search/research efforts made so far. Anyway, I added an answer b/c the previous answers were too convoluted for something that could be done with a short formula.
– Rubén
Mar 15, 2019 at 3:22

Assuming that the value of `rev2_1` always will have 5 characters use

``````=mid(A1,find("rev2_1",A1)+10,5)
``````

Explanation

• `find` finds the position of the first character of `rev2_1`
• `mid` gets a text (substring) of certain length (5) from a starting position, in this case the result of find plus 10 characters.

There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works:

``````=1*trim(substitute(substitute(substitute(index(SPLIT(A1,":"),3),"rev2_2",""),CHAR(34),""),",",""))
``````

short formulas:

``````=REGEXEXTRACT(A1;"_1""\: ""(.{5})")

=REGEXEXTRACT(A1;"_1""\: (.{7})")

=REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})")

=REGEXEXTRACT(A1;"rev2_1""\: (.{7})")

=REGEXEXTRACT(A1;"rev2_1""\: ""(.+)\"", ""rev2_2")

=REGEXEXTRACT(A1;"rev2_1""\: (.+)\, ""rev2_2")

=REGEXEXTRACT(A1;"_1""\: ""(.+)\"", ""rev2_2")

=MID(A1;32;5)
``````

this will search for what is in A5 cell:

``````=REGEXEXTRACT(FILTER(
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×")); ISNUMBER(SEARCH("*"&A5&"*";
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×"))))); ": (.+)")
``````

to remove doublequotes:

``````=SUBSTITUTE(REGEXEXTRACT(FILTER(
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×")); ISNUMBER(SEARCH("*"&A5&"*";
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×"))))); ": (.+)"); """"; "")
``````

to convert it on number:

``````=VALUE(SUBSTITUTE(SUBSTITUTE(REGEXEXTRACT(FILTER(
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×")); ISNUMBER(SEARCH("*"&A5&"*";
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×"))))); ": (.+)"); """"; ""); ","; "."))
``````

or:

``````=REGEXEXTRACT(SUBSTITUTE(REGEXEXTRACT(FILTER(
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×")); ISNUMBER(SEARCH("*"&A5&"*";
TRANSPOSE(SPLIT(SUBSTITUTE(A1; ", "; "×"); "×"))))); ": (.+)"); ","; ""); "\d+")
``````

• @jason answer updated. if none of this helped please provide some explanation what exactly are you after Mar 15, 2019 at 4:45