Trying to extract specific values from a string of text in a cell - variable length

kkoeb

New Member
Joined
Aug 23, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi & welcome to MrExcel.
Maybe
Fluff.xlsm
ABC
1
2[{"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"}}}]2237City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
Master
Cell Formulas
RangeFormula
B2:C2B2=TOROW(INDEX(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(SUBSTITUTE(A2,CHAR(34),""),"CustomerRef:"),"}"),": ",","),,2))
Dynamic array formulas.
 
Upvote 0
Solution
Hi & welcome to MrExcel.
Maybe
Fluff.xlsm
ABC
1
2[{"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"}}}]2237City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
Master
Cell Formulas
RangeFormula
B2:C2B2=TOROW(INDEX(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(SUBSTITUTE(A2,CHAR(34),""),"CustomerRef:"),"}"),": ",","),,2))
Dynamic array formulas.
Hi, and thank you. One question - does the CHAR(34) refer to the specific location in the string in which to start? I ask because the starting point can be at a different number of characters in each time - and so can the length of the customer name.
 
Upvote 0
pile on... CHAR34 is a quote. It's splitting up by quotes.

MrExcelPlayground20.xlsx
B
20[{"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"}}}]
212237
22City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
Sheet6
Cell Formulas
RangeFormula
B21:B22B21=LET(a,TEXTSPLIT(B20,,CHAR(34)),b,MATCH("CustomerRef",a,0),c,DROP(a,b),d,MATCH("value",c,0),e,INDEX(c,d+2),f,MATCH("name",c,0),g,INDEX(c,f+2),VSTACK(e,g))
Dynamic array formulas.
 
Upvote 0
Hi & welcome to MrExcel.
Maybe
Fluff.xlsm
ABC
1
2[{"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"}}}]2237City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
Master
Cell Formulas
RangeFormula
B2:C2B2=TOROW(INDEX(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(SUBSTITUTE(A2,CHAR(34),""),"CustomerRef:"),"}"),": ",","),,2))
Dynamic array formulas.
That works, thanks!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Please Try this


Book5
BCDEFGHIJKLMNOPQRS
3[{"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"}}}]
4
52237City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
Sheet4
Cell Formulas
RangeFormula
B5B5=LET(CRLoc,SEARCH("{""CustomerRef",B3),ValLoc,SEARCH("{""value",B3,CRLoc),IDLoc,SEARCH(": ",B3,ValLoc)+3,IDEndLoc,SEARCH(""",",B3,IDLoc),MID(B3,IDLoc,IDEndLoc-IDLoc))
C5C5=LET(CRLoc,SEARCH("{""CustomerRef",B3),NamLoc,SEARCH("name",B3,CRLoc),NamEnd,SEARCH("}",B3,NamLoc)-2,MID(B3,NamLoc+8,NamEnd-NamLoc-7))
 
Upvote 0
Please Try this


Book5
BCDEFGHIJKLMNOPQRS
3[{"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"}}}]
4
52237City of Town Downtown Redevelopment Authority:Hotel Town_WA_22-967
Sheet4
Cell Formulas
RangeFormula
B5B5=LET(CRLoc,SEARCH("{""CustomerRef",B3),ValLoc,SEARCH("{""value",B3,CRLoc),IDLoc,SEARCH(": ",B3,ValLoc)+3,IDEndLoc,SEARCH(""",",B3,IDLoc),MID(B3,IDLoc,IDEndLoc-IDLoc))
C5C5=LET(CRLoc,SEARCH("{""CustomerRef",B3),NamLoc,SEARCH("name",B3,CRLoc),NamEnd,SEARCH("}",B3,NamLoc)-2,MID(B3,NamLoc+8,NamEnd-NamLoc-7))
This works as well! Thanks!
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top