vba 在 Excel 单元格中调用正则表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5666856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call regex in an Excel cell
提问by millebii
I want to extract a string from a cell using a regex match. I can't find the excel function that does it and I'm no VBA expert either. I use Excel 2007.
我想使用正则表达式匹配从单元格中提取字符串。我找不到执行此操作的 excel 函数，而且我也不是 VBA 专家。我使用 Excel 2007。
回答by Andrew Cowenhoven
In the Excel VBA code editor select Tools/References. In the picker check the latest version of Microsoft VBScript Regular Expresions.
在 Excel VBA 代码编辑器中，选择工具/参考。在选择器中检查最新版本的 Microsoft VBScript 正则表达式。
Here is a simple user defined function to get you started. This will return "foo" if you point it at a cell with "foobar" or "fooooobar" in it.
这是一个简单的用户定义函数，可以帮助您入门。如果您将其指向包含“foobar”或“fooooobar”的单元格，这将返回“foo”。
Function RegExtract(contents As String)
Dim regx As RegExp
Set regx = New RegExp
With regx
.Pattern = "f[o]+"
With .Execute(contents)
If .Count Then RegExtract = .Item(0).Value
End With
End With
End Function
回答by aevanko
Here's a function that will let you pass in a cell then any regex pattern. Use () around the part of the pattern you want the function to return.
这是一个函数，可让您传入一个单元格，然后传入任何正则表达式模式。在您希望函数返回的模式部分周围使用 ()。
Function RegexExtract(ByVal text As String, _
ByVal extract_what As String) 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)
RegexExtract = allMatches.Item(0).submatches.Item(0)
End Function
回答by John Smith
@AndrewCowenhoven Has a good answer. It's simple to add the Regex Library. Just to expand this answer a little, I will add what I learned to get items within matches.
@AndrewCowenhoven 有一个很好的答案。添加正则表达式库很简单。只是为了稍微扩展这个答案，我将添加我学到的在匹配中获取项目的知识。
For example, I have a string like this in one cell.
例如，我在一个单元格中有一个这样的字符串。
doubleclick.net/activityi;u=5f2256de37ab4992927b3a0a0a34f983;u13=;u14=27.34;u16=AUD;u9=;u10=;u11=;u12=;u5=;u6=;u7=;u8=;u1=Car;u2=SEA;u3=SEA;u4=20130923%20%7C%2020130926;ord=5360319407191.128?
doubleclick.net/activityi;u=5f2256de37ab4992927b3a0a0a34f983;u13=;u14=27.34;u16=AUD;u9=;u10=;u11=;u12=;u5=;u6=;u7=;u8=u;u2 =SEA;u3=SEA;u4=20130923%20%7C%2020130926;ord=5360319407191.128?
I only need a few sections to verify that the doubleclick tag sent to google is correct. Using The regex shown above I have something like this.
我只需要几个部分来验证发送给 google 的 doubleclick 标记是否正确。使用上面显示的正则表达式我有这样的事情。
Function CheckU3(contents As String)
Dim regx As RegExp
Set regx = New RegExp
Dim matches, s
With regx
.Pattern = "u3=(.*?);"
.Global = False
If regx.Test(contents) Then
Set matches = regx.Execute(contents)
For Each Var In matches.Item(0).SubMatches
s = Var
Next
End If
CheckU3 = s
End With
End Function
I know this could be simplified a lot, but the point is that I'm pulling the submatches using parans to get specifically what I need.
我知道这可以简化很多，但关键是我正在使用 parans 拉取子匹配来获得我需要的特定内容。
回答by NBHyman
There's a library available in Excel's VBA system known as Microsoft VBScript Regular Expressions. Once you include the library from the internal VBA 'IDE', you can create a simple macro that accepts whatever value you'd like to parse and return the result back to the caller (which can be a function call from a cell).
Excel 的 VBA 系统中有一个名为 Microsoft VBScript 正则表达式的库。从内部 VBA 'IDE' 中包含库后，您可以创建一个简单的宏，该宏接受您想要解析的任何值并将结果返回给调用者（可以是来自单元格的函数调用）。
