2022-12-19

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

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)


No comments:

Post a Comment