从单个字段中提取多个数据

I'm currently trying to find a solution to deal with a single excel field which contain a couple of information. I will show you an example of what could contain those fields :

我目前正在尝试找到一个解决方案来处理包含一些信息的单个excel字段。我将向您展示可能包含这些字段的示例:

Field A1 : Coral Helm 7.154€ 21-22.12.13 

Field A2 : Wall of Spear 5€8-15.10.11

Field A3 : Clockwork Avian TBD 12-15.12.12

Field A4 : FellwarStone4-14-8.12.13

Field A5 : YotianSoldierTBD15-8-13

So to explain a little bit, basically this is the price of a card, with a range of date like 21-22.12.13 mean from 21.12.13 to 22.12.13. As you can see sometimes there are no space between word so i can't simply use the LEFT(), MID(), RIGHT() function.

所以要解释一下,基本上这是一张卡的价格,日期范围如21-22.12.13意味着从21.12.13到22.12.13。正如你所看到的,有时单词之间没有空格所以我不能简单地使用LEFT(),MID(),RIGHT()函数。

I've search on the net to check if it was possible by using only excel formula but it seems it's way better to use VBA to deal with that kind of problems. If anybody have an idea about how to deal with that it could be nice.

我在网上搜索是否只使用excel公式来检查它是否可行,但似乎使用VBA来处理这类问题会更好。如果有人知道如何处理它可能会很好。

1 个解决方案

#1

Try using Regular Expressions via the RegExp object, which you can include into your VBA project by going into the VBA IDE, clicking Tools ---> References... and checking the box next to "Microsoft VBScript Regular Expressions 5.5"

尝试通过RegExp对象使用正则表达式,您可以通过进入VBA IDE,单击工具--->引用...并选中“Microsoft VBScript正则表达式5.5”旁边的框,将其包含到VBA项目中

The following pattern can match and split each of the items you listed except for "FellwarStone4-14-8.12.13":

以下模式可以匹配并拆分您列出的每个项目,“FellwarStone4-14-8.12.13”除外:

"^([\w\s]+?(?=\s*TBD|\s*[0-9.]+€))\s*(TBD|[0-9.]+€)(.+)$"

Here is an example of how to use the RegExp object:

以下是如何使用RegExp对象的示例:

Public Sub SplitCell(cellvalue As String)
    Dim oRegExp As New RegExp
    Dim oMatches As MatchCollection
    Dim oMatch As Match
    Dim submatches() As String
    With oRegExp
        .Pattern = "^([\w\s]+?(?=\s*TBD|\s*[0-9.]+€))\s*(TBD|[0-9.]+€)\s*(.+)$"
        .Global = True
        .IgnoreCase = False
        .Multiline = False
        Set oMatches = .Execute(cellvalue)
    End With
    If oMatches.Count <> 0 Then
        MsgBox "Description: """ & oMatches.Item(0).submatches.Item(0) & """"
        MsgBox "Price: """ & oMatches.Item(0).submatches.Item(1) & """"
        MsgBox "Date: """ & oMatches.Item(0).submatches.Item(2) & """"
    Else
        MsgBox "Could not split cell value! It did not match the pattern."
    End If
End Sub

#1