regex - Extract Text Within a Long Varying String of Text

Question

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.

Answer 1

Here is the formula you need. It's a variation of what you already have.

=LEFT(B12,FIND(CHAR(34),B12)-1)

Of course, B12 holds the formula you published. So, by replacing "B12" with your formula you get the result you want.

=LEFT(RIGHT(C5,LEN(C5)-FIND("Ships",C5,1)+1),FIND(CHAR(34),RIGHT(C5,LEN(C5)-FIND("Ships",C5,1)+1))-1)