使用左、中、右的函数来提取两个字符/单词之间的文本?

I have the following data in a worksheet:

我在工作表中有以下数据:

column A
431620121601 5201585 - 0006 Fresh Turkey Gravy 500g 27 -Cartons 27 162
431619121602 5204059 - 0006 Fresh Bread Sauce 300g 52 -Palettes 52 312

I want to get the number of cartons or palettes from the text and put this in column B

我想从文本中获取纸箱或调色板的数量，并将其放在B列

Column B
    27 Cartons
    52 Palettes

Here's what i have tried so far:

以下是我迄今为止所做的尝试:

=MID(A8,SEARCH(" ",A8)+12,SEARCH("-Cartons",A8)-SEARCH(" ",A8)-4)

but this gives me this:

但这给了我这个:

06 Fresh Turkey Gravy 500g 27 -Cartons

Please can someone show me a better way of doing this?

谁能给我演示一下更好的方法吗?

2 个解决方案

#1

I was able to achieve what you want but with helper columns.

我能够实现您想要的，但是使用帮助列。

Col I has all possible Types listed like Cartons, Palettes etc.

所有可能的类型，如纸箱，调色板等。

Then I use these formula to get the results

然后我用这些公式得到结果

To find Index of what Type is matching. this formula is to be entered as an array formula i.e. type the formula and press Ctrl Shift Enter

查找匹配类型的索引。该公式将作为一个数组公式输入，即键入公式并按Ctrl Shift Enter。

B2 =SUMPRODUCT(--IFERROR(IF(FIND($I$2:$I$4,A2)>0,TRUE,FALSE),FALSE)*ROW($I$2:$I$4))

Get what Type is matching

获取匹配的类型

C2 =INDEX($I$1:$I$7,B2)

Get the ending string

得到字符串结束

D2 =RIGHT(A2,LEN(A2)-FIND(C2,A2)+1)

Get the last number

得到最后一个号码

E2 =SUBSTITUTE(RIGHT(SUBSTITUTE(D2," ",REPT(" ",50)),50)," ","")

replace the last number and the Type with blank and add the Type at the end

将最后的数字和类型替换为空，并在末尾添加类型

F2 =TRIM(SUBSTITUTE(SUBSTITUTE(D2,E2,""),C2,"")) & " " & C2

Drag all the formulas down for each row of your data. Take note of the column I.

将数据的每一行的所有公式拖下来。注意列I。

#2

I know a UDF probably seems like overkill, but I am a huge fan of regular expressions, and I wish they were more mainstream within the VBA community the way they are in many other technologies.

我知道UDF可能有点过头了，但我是正则表达式的超级粉丝，我希望它们在VBA社区中像在其他技术中一样成为主流。

So, with that in mind, here is a Regex solution to your problem:

因此，记住这一点，这里有一个Regex解决您的问题的解决方案:

Function UNITS(InputText As String) As String

  Dim rx As New RegExp
  Dim match As MatchCollection

  rx.Pattern = "(\d+ )-([A-Za-z]+)"

  If rx.Test(InputText) Then
    Set match = rx.Execute(InputText)
    UNITS = match(0).SubMatches(0) & match(0).SubMatches(1)
  End If

End Function

The best thing about the Regex is the scalability. This can adapt in ways an inline formula would have trouble doing with irregular data.

Regex最好的地方是可扩展性。这可以使内联公式在处理不规则数据时遇到麻烦。

Also, in the example above, the rx might be better suited as a global.

同样，在上面的示例中，rx可能更适合作为全局变量。

To use Regular Expressions, you will have to add a reference to "Microsoft VBScript Regular Expressions 5.5" to your project.

要使用正则表达式，您必须向项目添加“Microsoft VBScript正则表达式5.5”的引用。

#1