如何在Excel中使用正则表达式,并利用Excel强大的网格式设置进行数据操作?
函数返回字符串中匹配的模式或替换的值。 子遍历一列数据并提取与相邻单元格的匹配项。 需要什么设置? Excel中正则表达式的特殊字符是什么?
我知道Regex在很多情况下并不理想(使用或不使用正则表达式?),因为excel可以使用Left, Mid, Right, Instr类型的命令进行类似的操作。
如何在Excel中使用正则表达式,并利用Excel强大的网格式设置进行数据操作?
函数返回字符串中匹配的模式或替换的值。 子遍历一列数据并提取与相邻单元格的匹配项。 需要什么设置? Excel中正则表达式的特殊字符是什么?
我知道Regex在很多情况下并不理想(使用或不使用正则表达式?),因为excel可以使用Left, Mid, Right, Instr类型的命令进行类似的操作。
当前回答
我需要使用它作为一个单元格函数(如SUM或VLOOKUP),并发现它很容易:
Make sure you are in a Macro Enabled Excel File (save as xlsm). Open developer tools Alt + F11 Add Microsoft VBScript Regular Expressions 5.5 as in other answers Create the following function either in workbook or in its own module: Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant Dim regex As New VBScript_RegExp_55.RegExp Dim strInput As String strInput = myRange.Value With regex .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With REGPLACE = regex.Replace(strInput, outputPattern) End Function Then you can use in cell with =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" to "A243")
其他回答
我不想启用一个参考库,因为我需要我的脚本是可移植的。Dim foo作为新的VBScript_RegExp_55。RegExp行导致了User Defined Type Not Defined错误,但我找到了一个适合我的解决方案。
更新RE评论w/ @chrisneilsen:
I was under the impression that enabling a reference library was tied to the local computers settings, but it is in fact, tied directly to the workbook. So, you can enable a reference library, share a macro enabled workbook and the end user wouldn't have to enable the library as well. Caveat: The advantage to Late Binding is that the developer does not have to worry about the wrong version of an object library being installed on the user's computer. This likely would not be an issue w/ the VBScript_RegExp_55.RegExp library, but I'm not sold that the "performance" benifit is worth it for me at this time, as we are talking imperceptible milliseconds in my code. I felt this deserved an update to help others understand. If you enable the reference library, you can use "early bind", but if you don't, as far as I can tell, the code will work fine, but you need to "late bind" and loose on some performance/debugging features.
来源:https://peltiertech.com/Excel/EarlyLateBinding.html
您要做的是在单元格A1中放置一个示例字符串,然后测试您的strPattern。一旦工作,调整然后rng按需要。
Public Sub RegExSearch()
'https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
'https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/
'https://www.vitoshacademy.com/vba-regex-in-excel/
Dim regexp As Object
'Dim regex As New VBScript_RegExp_55.regexp 'Caused "User Defined Type Not Defined" Error
Dim rng As Range, rcell As Range
Dim strInput As String, strPattern As String
Set regexp = CreateObject("vbscript.regexp")
Set rng = ActiveSheet.Range("A1:A1")
strPattern = "([a-z]{2})([0-9]{8})"
'Search for 2 Letters then 8 Digits Eg: XY12345678 = Matched
With regexp
.Global = False
.MultiLine = False
.ignoreCase = True
.Pattern = strPattern
End With
For Each rcell In rng.Cells
If strPattern <> "" Then
strInput = rcell.Value
If regexp.test(strInput) Then
MsgBox rcell & " Matched in Cell " & rcell.Address
Else
MsgBox "No Matches!"
End If
End If
Next
End Sub
以下是我的尝试:
Function RegParse(ByVal pattern As String, ByVal html As String)
Dim regex As RegExp
Set regex = New RegExp
With regex
.IgnoreCase = True 'ignoring cases while regex engine performs the search.
.pattern = pattern 'declaring regex pattern.
.Global = False 'restricting regex to find only first match.
If .Test(html) Then 'Testing if the pattern matches or not
mStr = .Execute(html)(0) '.Execute(html)(0) will provide the String which matches with Regex
RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
Else
RegParse = "#N/A"
End If
End With
End Function
这不是一个直接的答案,但可能会为你提供一个更有效的选择。这就是谷歌表有几个内置的正则表达式函数,这些可以非常方便,并帮助绕过Excel中的一些技术程序。显然,在个人电脑上使用Excel有一些优势,但对于大多数用户来说,谷歌表格将提供相同的体验,并可能在文档的可移植性和共享方面提供一些好处。
他们提供
REGEXEXTRACT:根据正则表达式提取匹配的子字符串。
REGEXREPLACE:使用正则表达式将文本字符串的一部分替换为不同的文本字符串。
替换:用字符串中的新文本替换现有文本。
REPLACE:用不同的文本字符串替换文本字符串的一部分。
你可以像这样直接把这些输入到单元格中,然后生成你想要的任何东西
=REGEXMATCH(A2, "[0-9]+")
它们也可以很好地与其他函数组合,如IF语句,如下所示:
=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")
希望这为那些对Excel的VBS组件感到畏惧的用户提供了一个简单的解决方案。
下面是一个regex_subst()函数。例子:
=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo
下面是简化的代码(至少对我来说更简单)。我不知道如何使用上面的例子来构建一个合适的输出模式:
Function regex_subst( _
strInput As String _
, matchPattern As String _
, Optional ByVal replacePattern As String = "" _
) As Variant
Dim inputRegexObj As New VBScript_RegExp_55.RegExp
With inputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = matchPattern
End With
regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function
我需要使用它作为一个单元格函数(如SUM或VLOOKUP),并发现它很容易:
Make sure you are in a Macro Enabled Excel File (save as xlsm). Open developer tools Alt + F11 Add Microsoft VBScript Regular Expressions 5.5 as in other answers Create the following function either in workbook or in its own module: Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant Dim regex As New VBScript_RegExp_55.RegExp Dim strInput As String strInput = myRange.Value With regex .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With REGPLACE = regex.Replace(strInput, outputPattern) End Function Then you can use in cell with =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" to "A243")