在 VBA (excel) 中返回正则表达式匹配 [英] Returning a regex match in VBA (excel)

问题描述

我正在尝试为 excel 2010 编写一个函数，该函数将采用非结构化文本单元格，查找称为 sdi 值的内容，如果找到，则返回该数字.sdi 值将显示为 sdi ####.我想要的是返回 sdi 和它后面的特定数字，所以如果单元格包含一些文本 sdi 1234 一些更多的文本"，该函数将返回 sdi 1234.

I am trying to write a function for excel 2010 that will take a cell of unstructured text, look for something called an sdi value and, if found, return that number. The sdi value will appear as sdi ####. What I want is to return sdi and the sepecific numbers that follow it, so if the cell contains "some text sdi 1234 some more text" the function will return sdi 1234.

这是我的功能:

Function SdiTest(LookIn As String) As String
  Dim temp As String
  Dim STA As Object
  temp = ""

  Set SDI = CreateObject("VBScript.RegExp")
  SDI.IgnoreCase = True
  SDI.Pattern = "sdi [1-9]*"
  SDI.Global = True

  If SDI.Test(LookIn) Then
    temp = SDI.Execute(LookIn)
  End If

  SdiTest = temp
End Function

如果没有 sdi 编号，它永远不会进入 if 语句并尽职尽责地返回空字符串.如果有 sdi 号码，我会得到 #VALUE！

If there is no sdi number it never enters the if statement and dutifully returns the empty string. If there is an sdi number I get #VALUE!

我错过了什么?

是的，启用了 VBScript.此外，我发现在 VBA 中使用正则表达式令人沮丧，并且很难在网上找到有用的信息.欢迎提供指向优质在线资源的链接.

Yes, VBScript is enabled. Additionally, I am finding it frustrating to use regex in VBA, and hard to find useful info online. Links to good online resources would be appreciated.

谢谢

推荐答案

您需要访问匹配项才能获得 SDI 编号.这是一个可以执行此操作的函数(假设每个单元只有 1 个 SDI 编号).

You need to access the matches in order to get at the SDI number. Here is a function that will do it (assuming there is only 1 SDI number per cell).

对于正则表达式，我使用了sdi 后跟一个空格和一个或多个数字".您有sdi 后跟一个空格和零个或多个数字".您可以简单地将我的模式中的 + 更改为 * 以恢复到您拥有的状态.

For the regex, I used "sdi followed by a space and one or more numbers". You had "sdi followed by a space and zero or more numbers". You can simply change the + to * in my pattern to go back to what you had.

Function ExtractSDI(ByVal text As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = "(sdi d+)"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(text)

If allMatches.count <> 0 Then
    result = allMatches.Item(0).submatches.Item(0)
End If

ExtractSDI = result

End Function

如果一个单元格可能有多个要提取的 SDI 编号，这里是我的 RegexExtract 函数.您可以传入第三个参数来分隔每个匹配项(如逗号分隔它们)，并在实际函数调用中手动输入模式:

If a cell may have more than one SDI number you want to extract, here is my RegexExtract function. You can pass in a third paramter to seperate each match (like comma-seperate them), and you manually enter the pattern in the actual function call:

Ex) =RegexExtract(A1, "(sdi d+)", ", ")

这里是:

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional seperator As String = "") As String

Dim i As Long, j As Long
Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & seperator & allMatches.Item(i).submatches.Item(j)
    Next
Next

If Len(result) <> 0 Then
    result = Right(result, Len(result) - Len(seperator))
End If

RegexExtract = result

End Function

*请注意，我从我的 RegexExtract 中删除了RE.IgnoreCase = True"，但您可以将其重新添加，或者如果您愿意，甚至可以将其添加为可选的第 4 个参数.

*Please note that I have taken "RE.IgnoreCase = True" out of my RegexExtract, but you could add it back in, or even add it as an optional 4th parameter if you like.

这篇关于在 VBA (excel) 中返回正则表达式匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！