CODEWITHSUNDEEP
excelstringexcel-formula
user4661010
user4661010

Reputation:

Using MID and FIND to extract data from a String into an Excel Cell

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

Upvotes: 0

Views: 171

Answers (1)

Chronocidal
Chronocidal

Reputation: 7951

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\"]

Upvotes: 1

Related Questions