How to update/concatenate specific value in excel cell?

Question

I have stored below request in A0 cell and now i want to update the 'tag' value with the data(123) which is present in A1 cell. Not sure how to concatenate or how to make use of other formula. Can someone please help here ?

Request in A0 Cell:
    {
      "merchant_ref": "xxx-xxxx",
      "tag": "789",     //Value(789) is not constant. May differ next request/cell.
      "transaction_type": "xx",
      "method": "xxxx",
      "amount": "xxxx",
      "currency_code": "xxx"
    }


Data in A1 Cell:
123


What i am expecting in A2 Cell:
    {
      "merchant_ref": "xxx-xxxx",
      "tag": "123", 
      "transaction_type": "xx",
      "method": "xxxx",
      "amount": "xxxx",
      "currency_code": "xxx"
    }

Answer 1

AFAIK, there's no native parser/builder for JSON. The following formula constructs the desired string by concatenating 1) original string from beginning up to the position right before the (old) value, 2) the new value, 3) original string after the value till the end. The """,", CHAR(10), " " after 'A1' is just the way I handled the newline and indentation characters, not necessary to be optimal.

=CONCAT(LEFT(A0,FIND("tag"": ",A0)+LEN("tag"": ")),A1, """,", CHAR(10), "      ", MID(A0, FIND("""tran",A0),1000))