0

I have to Extract information from a json String for that I am trying to use Excel to extract the tag and the value of the tag within quotations into separate cells and eventually make a table, which will be easier for editing.

For Example: After I paste the data in an excel Cell:

"record_data":"[{\"fixed_leg\":[{\"calc_period_bus_center\":[\"Tgt\"],\"calc_period_bus_day_conv\":\"MOD \",\"calc_period\":\"12M\",\"calc_period\":\"8\",\"dcf\":\"30/360\",\"effective_date\":\"2021-04-08\",\"effective_date_ \":[\"Tgt\"],\"effective_date_bus_day_conv\":\"FOLLOWING\",\"final_principal_exchange\":\"FALSE\",\"fixed_rate\":\"0.0311\",\"initial_principal_exchange\":\"FALSE\",\"ntl_amount\":\"96\",\"ntl_ccy\":\"EUR\",\"pay_dates_relative_to\":\"CalculationPeriodEndDate\",\"pay_offset\":\"2D\",\"pay_offset_bus_center\":[\"Tgt\"],\"pay_offset_bus_day_conv\":\"MOD\",\"pay_period_bus_center\":[\"Tgt\"],\"pay_period_bus_day_conv\":\"MOD\",\"pay_period_freq\":\"12M\",\"payer_party\":\"US\",\"receiver_party\":\"GFIL\",\"resettable_ntl\":\"FALSE\",\"termination_date\":\"2026-04-08\",\"termination_date r\":[\"Tgt\"],\"termination_date_bus_day_conv\":\"MOD\"}],]"
}

I want to extract the Tag name and value to paste in other excel cells but due to limited knowledge about excel, I am struggling.

I have used =MID(A2,FIND("calc_period_bus_center:",A2,1),A2)

but that does only return an error #VALUE

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31

1 Answers1

1

2 points: First, the third argument of MID is a number (how many character to return). If you change this from A2 to LEN(A2), it will pull everything until the end of the string.

Secondly, you are searching for the string "calc_period_bus_center:". However, this does not exist in the string you are searching:

"record_data":"[{\"fixed_leg\":[{\"calc_period_bus_center\":[\"Tgt\"],\"calc_period_bus_day_conv\":\"MOD \",\"calc_period\":\"12M\",\"calc_period\":\"8\",\"dcf\":\"30/360\",\"effective_date\":\"2021-04-08\",\"effective_date_ \":[\"Tgt\"],\"effective_date_bus_day_conv\":\"FOLLOWING\",\"final_principal_exchange\":\"FALSE\",\"fixed_rate\":\"0.0311\",\"initial_principal_exchange\":\"FALSE\",\"ntl_amount\":\"96\",\"ntl_ccy\":\"EUR\",\"pay_dates_relative_to\":\"CalculationPeriodEndDate\",\"pay_offset\":\"2D\",\"pay_offset_bus_center\":[\"Tgt\"],\"pay_offset_bus_day_conv\":\"MOD\",\"pay_period_bus_center\":[\"Tgt\"],\"pay_period_bus_day_conv\":\"MOD\",\"pay_period_freq\":\"12M\",\"payer_party\":\"US\",\"receiver_party\":\"GFIL\",\"resettable_ntl\":\"FALSE\",\"termination_date\":\"2026-04-08\",\"termination_date r\":[\"Tgt\"],\"termination_date_bus_day_conv\":\"MOD\"}],]" }

Remove the Colon (:): =MID(A2,FIND("calc_period_bus_center",A2,1),Len(A2)) (Or add the \" before the colon instead)

Now, presuming you want the bit in square brackets after that, you can use a double-FIND to find the first appearance of "]" after "calc_period_bus_center", like so:

FIND("]", A2, FIND("calc_period_bus_center",A2,1))

You can then use this to trim down further with LEFT:

=MID(LEFT(A2, FIND("]", A2, FIND("calc_period_bus_center",A2,1))), FIND("calc_period_bus_center",A2,1), Len(A2))

This will give you "calc_period_bus_center\":[\"Tgt\"]", so SUBSTITUTE the first bit:

=SUBSTITUTE(MID(LEFT(A2, FIND("]", A2, FIND("calc_period_bus_center",A2,1))), FIND("calc_period_bus_center",A2,1), LEN(A2)),"calc_period_bus_center\"":","")

[\"Tgt\"]

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • Thank you it did work but I am not sure it was clear or i couldn't explain properly, i want to get both calc_period_bus_center and the value "Tgt" in separate cells for example: CELL 1 CELL 2 calc_period_bus_center Tgt calc_period_bus_day_conv MOD –  Sep 12 '19 at 16:08
  • calc_period_bus_center Tgt calc_period_bus_day_conv MOD calc_period 12M calc_period_roll 8 dcf 30/360 –  Sep 12 '19 at 16:19
  • @xack That sounds like something that you should probably be trying to do with VBA instead... (e.g. the `SPLIT` function can use `","` to break up rows, and `":"` to split up the columns) – Chronocidal Sep 13 '19 at 13:36