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

Question

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.

Answer 1

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.

Answer 2

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),""),",",""))

Answer 3

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+")