-2

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.

migrated from stackoverflow.com Mar 14 at 21:15

This question came from our site for professional and enthusiast programmers.

  • 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 at 3:22
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),""),",",""))
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

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

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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