The problem I'm having is this. The data I pull adds information in a very inconvenient way. When I'm trying to isolate and extract a certain text within the string it becomes super complicated. I want to be able to extract "Ships in 1-2 business days" some of the strings are listed as "Ships 1-2 weeks" which also needs to be extracted if applicable.
The string in the cell is:
[{"orderItemId":609507407,"lineItemKey":"1509870501993","sku":"SKU-111111","name":"Company
Product - NA / Ships in 1-2 business
days","imageUrl":"https://FAKEADDRESS.com/","weight":{"value":352.0,"units":"ounces","WeightUnits":1},"quantity":1,"unitPrice":1599.0,"taxAmount":null,"shippingAmount":null,"warehouseLocation":null,"options":[],"productId":30236912,"fulfillmentSku":null,"adjustment":false,"upc":"","createDate":"2018-11-03T20:20:42.247","modifyDate":"2018-11-03T20:20:42.247"}]
Using =RIGHT(C5,LEN(C5)-FIND("Ships",C5,1)+1) I'm able to find what I need, however everything after still remains. See below example.
Ships in 1-2 business
days","imageUrl":"https://FAKEADDRESS.com/","weight":{"value":352.0,"units":"ounces","WeightUnits":1},"quantity":1,"unitPrice":1599.0,"taxAmount":null,"shippingAmount":null,"warehouseLocation":null,"options":[],"productId":30236912,"fulfillmentSku":null,"adjustment":false,"upc":"","createDate":"2018-11-03T20:20:42.247","modifyDate":"2018-11-03T20:20:42.247"}]
Essentially, I just want the formula to pull out the text "Ships in 1-2 business days" some of the strings will contain "Ships in 1-2 weeks" if it is not a 1-2 day shipping method, which I would like to extract as well.
See Question&Answers more detail:
os