-1

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.

1
  • 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

3 Answers 3

1
+200

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.
1

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

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)

0


this will search for what is in A5 cell:

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

0


to remove doublequotes:

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

0


to convert it on number:

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

0


or:

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.