提取长文本字符串中的文本 [英] Extract Text Within a Long Varying String of Text

问题描述

我遇到的问题是这个.我提取的数据以非常不便的方式添加了信息.当我试图隔离并提取字符串中的某些文本时，它变得非常复杂.我希望能够提取"1-2个工作日内的船运"，其中一些字符串被列为"1-2个星期内的船运"，如果适用，还需要将其提取.

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.

单元格中的字符串为:

[{"orderItemId":609507407，"lineItemKey":"1509870501993"，"sku":"SKU-111111"，名称":公司 产品-不适用/在1-2个业务中发货 days，" imageUrl:" https://FAKEADDRESS.com/，" weight:{"值:352.0，" units:"盎司，" WeightUnits:1}，"数量:1，" unitPrice:1599.0，" taxAmount:null，" shippingAmount:null，" warehouseLocation:null，"选项:[]，" productId:30236912，" fulfillmentSku:null，" adjustment:false，" upc:"，" createDate:" 2018-11-03T20:20:42.247，" modifyDate":"2018-11-03T20:20:42.247"}]

[{"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"}]

使用= RIGHT(C5，LEN(C5)-FIND("Ships"，C5,1)+1)我可以找到所需的内容，但是之后的一切仍然存在.参见下面的示例.

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.

1-2个业务中的发运 days，" imageUrl:" https://FAKEADDRESS.com/，" weight:{"值:352.0，" units:"盎司，" WeightUnits:1}，"数量:1，" unitPrice:1599.0，" taxAmount:null，" shippingAmount:null，" warehouseLocation:null，"选项:[]，" productId:30236912，" fulfillmentSku:null，" adjustment:false，" upc:"，" createDate:" 2018-11-03T20:20:42.247，" modifyDate":"2018-11-03T20:20:42.247"}]

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"}]

本质上，我只是希望该公式提取文本"1-2个工作日内发货"，如果不是1-2天的发货方式，则某些字符串将包含"1-2个星期内发货"，我也想提取出来.

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.

推荐答案

这是您需要的公式.这是您已经拥有的东西的一种变化.

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

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

当然，B12保存您发布的公式.因此，通过用公式替换"B12"，您可以获得所需的结果.

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)

这篇关于提取长文本字符串中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！