Parse JSON string with Excel to search field values (price)-CodePudding

I need to extract price value from this cell after the old_value and the new_value from the following JSON string in cell A1:

{"createdAt": "1671062337000", "lastModifiedAt": "1671062337000", 
"item_id": "108532709", "old_value": {"variants": {"946038674": 
{"choice_groups": {"108532709": {"choices": {"946038818": 
{"price": "186.55"}}}}}}}, "closedAt": "1671062337000", 
"new_value": {"variants": {"946038674": {"choice_groups": {"108532709": 
{"choices": {"946038818": {"price": "190.55"}}}}}}}, 
"type_of_change": "EmbeddedChoicesPriceChange"}

Here is the same JSON string as before but just in a single line so you can copy and paste:

{"createdAt": "1671062337000", "lastModifiedAt": "1671062337000", "item_id": "108532709", "old_value": {"variants": {"946038674": {"choice_groups": {"108532709": {"choices": {"946038818": {"price": "186.55"}}}}}}}, "closedAt": "1671062337000", "new_value": {"variants": {"946038674": {"choice_groups": {"108532709": {"choices": {"946038818": {"price": "190.55"}}}}}}}, "type_of_change": "EmbeddedChoicesPriceChange"}

I was trying to use:

MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2)) 1, 
FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)) - 
FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))-1)

CodePudding user response：

=LET(t,TEXTSPLIT(A1,,HSTACK(CHAR(1)&"""",""""),1),
     o,XMATCH("old_value",t),
     n,XMATCH("new_value",t),
--INDEX(t,VSTACK(o,n) 16),)

TEXTSPLIT is used to split the text on either CHAR(1) or ". This returns a spill range of the splitted values.

o searches for the string old_value in this spill array and returns it's position.

n searches for the string new_value in the spill array and returns it's position.

Both prices are found 16 "rows" below in the spill array.

INDEX / VSTACK 16 returns the old and new price spilled and -- converts the string to numeric value.

CodePudding user response：

You can combine TEXTBEFORE with TEXTAFTER if such functions are available in your excel version and assuming price field appears two times as the question indicates. Put the following formula in B4:

=TEXTBEFORE(TEXTAFTER(A1,"{""price"": """, {1;2}),"""")

here is the output:

If you want to return the result by column, then replace {1;2} with {1,2}.

For your convenience you can define a user LAMBDA function TEXTBETWEEN, and reuse it:

=LET(TEXTBETWEEN, LAMBDA(text,before,after,occ,
  TEXTBEFORE(TEXTAFTER(text,after,occ), before)),
  TEXTBETWEEN(A1, """", "{""price"": """, {1;2})
)

Based on JSON structure (using