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

K

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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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
Fluff said:
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.
Click to expand...
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
Fluff said:
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.
Click to expand...
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
Jeffrey Mahoney said:
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))
Click to expand...
This works as well! Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Excel vba Extract specific text from a cell containing multiple line
Replies
1
Views
7K
shknbk2
S

Forum statistics

Threads
1,215,071
Messages
6,122,964
Members
449,094
Latest member
Anshu121

Share this page

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