I have data populating a cell in my excel spreadsheet. There is a lot of extraneous data and I only need a specific portion of the data in my result. The original data in each cell can be of varying length and the resulting data that I want to extract can be of varying length as well. What is the best way to extract the specific data that I need.
Example: This detail is all on one cell:
[{"Id": "1", "LineNum": 1, "Description": "LB-203-CG OS Table Large @ Lobby", "Amount": 2520.0, "DetailType": "ItemBasedExpenseLineDetail", "ItemBasedExpenseLineDetail": {"CustomerRef": {"value": "2237", "name": "City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967"}, "BillableStatus": "NotBillable", "ItemRef": {"value": "146", "name": "Procurement:Furniture:Furniture"}, "ClassRef": {"value": "300000000000856438", "name": "Washington:Seattle Library"}, "UnitPrice": 2520, "Qty": 1, "TaxCodeRef": {"value": "NON"}}}, {"Id": "2", "LineNum": 2, "Description": "LB-203.1-CG Table Medium @ Lobby", "Amount": 1620.0, "DetailType": "ItemBasedExpenseLineDetail", "ItemBasedExpenseLineDetail": {"CustomerRef": {"value": "2237", "name": "City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967"}, "BillableStatus": "NotBillable", "ItemRef": {"value": "146", "name": "Procurement:Furniture:Furniture"}, "ClassRef": {"value": "300000000000856438", "name": "Washington:Seattle Library"}, "UnitPrice": 1620, "Qty": 1, "TaxCodeRef": {"value": "NON"}}}, {"Id": "3", "LineNum": 3, "Amount": 700.0, "DetailType": "ItemBasedExpenseLineDetail", "ItemBasedExpenseLineDetail": {"CustomerRef": {"value": "2237", "name": "City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967"}, "BillableStatus": "NotBillable", "ItemRef": {"value": "97", "name": "Procurement:Misc Procurement:Estimated Vendor Freight (Procurement)"}, "ClassRef": {"value": "300000000000856438", "name": "Washington:Seattle Library"}, "UnitPrice": 1080, "Qty": 0.6481481, "TaxCodeRef": {"value": "NON"}}}]
What I need to extract is CustomerRef value = 2237 and if possible CustomerRef name = City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
And again, the data in each cell can be of varying length, but same basic structure. Is there a good way to extract these details?
Thanks!
Example: This detail is all on one cell:
[{"Id": "1", "LineNum": 1, "Description": "LB-203-CG OS Table Large @ Lobby", "Amount": 2520.0, "DetailType": "ItemBasedExpenseLineDetail", "ItemBasedExpenseLineDetail": {"CustomerRef": {"value": "2237", "name": "City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967"}, "BillableStatus": "NotBillable", "ItemRef": {"value": "146", "name": "Procurement:Furniture:Furniture"}, "ClassRef": {"value": "300000000000856438", "name": "Washington:Seattle Library"}, "UnitPrice": 2520, "Qty": 1, "TaxCodeRef": {"value": "NON"}}}, {"Id": "2", "LineNum": 2, "Description": "LB-203.1-CG Table Medium @ Lobby", "Amount": 1620.0, "DetailType": "ItemBasedExpenseLineDetail", "ItemBasedExpenseLineDetail": {"CustomerRef": {"value": "2237", "name": "City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967"}, "BillableStatus": "NotBillable", "ItemRef": {"value": "146", "name": "Procurement:Furniture:Furniture"}, "ClassRef": {"value": "300000000000856438", "name": "Washington:Seattle Library"}, "UnitPrice": 1620, "Qty": 1, "TaxCodeRef": {"value": "NON"}}}, {"Id": "3", "LineNum": 3, "Amount": 700.0, "DetailType": "ItemBasedExpenseLineDetail", "ItemBasedExpenseLineDetail": {"CustomerRef": {"value": "2237", "name": "City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967"}, "BillableStatus": "NotBillable", "ItemRef": {"value": "97", "name": "Procurement:Misc Procurement:Estimated Vendor Freight (Procurement)"}, "ClassRef": {"value": "300000000000856438", "name": "Washington:Seattle Library"}, "UnitPrice": 1080, "Qty": 0.6481481, "TaxCodeRef": {"value": "NON"}}}]
What I need to extract is CustomerRef value = 2237 and if possible CustomerRef name = City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
And again, the data in each cell can be of varying length, but same basic structure. Is there a good way to extract these details?
Thanks!