Excel中的模式匹配计数（regex& vba） [英] Pattern match count in excel (regex & vba)

问题描述

我有一个Office 2007 .XLSX 文件包含超过5000个记录，如下所示（单个单元格，多行文本）。问题：在一个相邻的单元格上，将单元格中的事件计数。查看A1的单元格数据，您可以看到3个事件：

I have an Office 2007 .XLSX file containing over 5000 records like the below (a single cell with multiple rows of text). The issue: on an adjacent cell, put count of incidents from the cell. Looking at the cell data for A1 you can see 3 incidents:

单元格A1：

1/15/2013 1:30:11 AM Userx
Had to reboot system
1/15/2013 1:32:11 AM Userx
System running finished rebooting and appears to be working
11/15/2013 12:30:11 AM Userx
System hung again

问题是日期值不一致。日期，月份和小时可以是单位或双位数，但总是在新行上注明。

The problem is that the date value isn't consistent. The day, month, and hour can be single or double digit, but they are always noted on a new line.

我的代码解决方案是将单元格拆分在换行符上，修剪最后一个'：'后面的所有5个字符，并根据我的正则表达式评估结果。之后，一些基本的文字和文本插入到相邻的单元格中。

My code solution was to take the cell, split it on line break, trim everything 5 characters after the last ':' and evaluate the the result against my regular expression. After that, some basic tally and text insert to adjacent cell.

以下是如何调用函数的示例。

The below is an example of how the function would be called.

'calling function from another source:

thecount = CountOfDateValues(Range("a1").Value) 'get count
Range("b1").Value = thecount 'put count to adjacent cell

是否有任何代码将使用字符串值并返回匹配的计数正则表达式？

Is there any code that will take the string value and return a count of matches to the regular expression?

推荐答案

您还可以使用\\\
在Pattern表达式中包含换行符。这样一来，您就不必在数组中分割文本：

You can also include newlines in the Pattern expression by using \n. this way, you don't have to split the text in an array:

Private Function String_CountRegExp_Debug()

    'Input of the test text
    Dim TestText As String
    TestText = "1/15/2013 1:30:11 AM Userx" & vbNewLine & _
            "Had to reboot system" & vbNewLine & _
            "1/15/2013 1:32:11 AM Userx" & vbNewLine & _
            "System running finished rebooting and appears to be working" & vbNewLine & _
            "11/15/2013 12:30:11 AM Userx" & vbNewLine & _
            "System hung again"

    'Input of the Pattern
    Dim RE_Pattern As String
    RE_Pattern = "(\d{1,2})\/(\d{1,2})\/(\d{4})\s(\d{1,2}):(\d{1,2}):(\d{1,2})\s([A,P]M).*\n"

    Debug.Print String_CountRegExp(TestText, RE_Pattern)

End Function

Public Function String_CountRegExp(Text As String, Pattern As String) As Long
'Count the number of Pattern matches in a string.

    'Set up regular expression object
    Dim RE As New RegExp
    RE.Pattern = Pattern
    RE.Global = True
    RE.IgnoreCase = True
    RE.MultiLine = True
    'Retrieve all matches
    Dim Matches As MatchCollection
    Set Matches = RE.Execute(Text)
    'Return the corrected count of matches
    String_CountRegExp = Matches.Count

End Function

这篇关于Excel中的模式匹配计数（regex& vba）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！