我该如何在Excel中使用正则表达式,并利用Excel强大的网格状设置进行数据操作?
- 单元格函数可返回字符串中匹配的模式或替换后的值。
- Sub循环遍历数据列并提取匹配到相邻单元格中的内容。
- 需要哪些设置?
- Excel中的正则表达式特殊字符有哪些?
我知道正则表达式并不适合于许多情况(是否使用正则表达式?),因为在类似操作中,Excel可以使用Left
、Mid
、Right
、Instr
等命令。
我该如何在Excel中使用正则表达式,并利用Excel强大的网格状设置进行数据操作?
我知道正则表达式并不适合于许多情况(是否使用正则表达式?),因为在类似操作中,Excel可以使用Left
、Mid
、Right
、Instr
等命令。
正则表达式 用于模式匹配。
在Excel中使用,请按照以下步骤:
第一步: 添加VBA对“Microsoft VBScript Regular Expressions 5.5”的引用
第二步: 定义你的模式
基本定义:
-
范围。
a-z
匹配小写字母a到z0-5
匹配0到5之间的任何数字[]
只匹配括号内的一个对象。
[a]
匹配字母a[abc]
匹配单个字母 a、b 或 c[a-z]
匹配任意一个小写字母。()
用于将不同的匹配分组以便返回。请参见以下示例。
{}
为之前定义的模式的重复副本设置乘数。
[a]{2}
匹配两个连续的小写字母a: aa
[a]{1,3}
匹配至少一个并最多三个小写字母 a
, aa
, aaa
+
匹配至少一个或多个之前定义的模式。
a+
将匹配连续的a's: a
, aa
, aaa
等等。?
匹配零个或一个之前定义的模式。
[a-z]?
匹配空字符串或任何单个小写字母。*
匹配零个或多个之前定义的模式。
[a-z]*
匹配空字符串或小写字母字符串。.
匹配除换行符 \n
以外的任何字符
a.
匹配以a开头并以除 \n
以外的任何字符结尾的两个字符字符串|
或运算符
a|b
表示可以匹配 a
或 b
。red|white|orange
精确匹配其中一种颜色。^
非运算符
[^0-9]
字符不能包含数字[^aA]
字符不能是小写字母 a
或大写字母 A
\
转义后面的特殊字符(覆盖上述行为)
\.
, \\
, \(
, \?
, \$
, \^
锚定模式:
^
匹配必须出现在字符串开头
^a
第一个字符必须是小写字母 a
^[0-9]
第一个字符必须是数字。$
匹配必须出现在字符串结尾
a$
最后一个字符必须是小写字母 a
优先级表:
Order Name Representation
1 Parentheses ( )
2 Multipliers ? + * {m,n} {m, n}?
3 Sequence & Anchors abc ^ $
4 Alternation |
预定义字符缩写:
abr same as meaning
\d [0-9] Any single digit
\D [^0-9] Any single character that's not a digit
\w [a-zA-Z0-9_] Any word character
\W [^a-zA-Z0-9_] Any non-word character
\s [ \r\t\n\f] Any space character
\S [^ \r\t\n\f] Any non-space character
\n [\n] New line
示例1:作为宏运行
下面的示例宏检查单元格A1
中的值,看看前1或2个字符是否是数字。如果是,则将其删除并显示字符串的其余部分。如果不是,则会出现一个框,告诉您未找到匹配项。单元格A1
值为12abc
将返回abc
,值为1abc
将返回abc
,值为abc123
将返回“未匹配”,因为数字不在字符串的开头。Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1")
If strPattern <> "" Then
strInput = Myrange.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
End Sub
示例2: 作为单元格函数运行
此示例与示例1相同,但设置为作为单元格函数运行。要使用,请将代码更改为以下内容:
Function simpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
strPattern = "^[0-9]{1,3}"
If strPattern <> "" Then
strInput = Myrange.Value
strReplace = ""
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
simpleCellRegex = regEx.Replace(strInput, strReplace)
Else
simpleCellRegex = "Not matched"
End If
End If
End Function
将字符串("12abc")放入单元格A1
中。在单元格B1
中输入公式=simpleCellRegex(A1)
,结果将为"abc"。
示例3:循环范围
这个例子和示例1相同,但是循环处理一系列单元格。
Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1:A5")
For Each cell In Myrange
If strPattern <> "" Then
strInput = cell.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
MsgBox (regEx.Replace(strInput, strReplace))
Else
MsgBox ("Not matched")
End If
End If
Next
End Sub
例子4: 分解不同的模式
此例子循环遍历一个区域 (A1
, A2
和 A3
),查找以三个数字开头,后跟一个字母和4个数字的字符串。输出将模式匹配拆分到相邻单元格中,使用()
。 $1
代表第一个在第一组()
中匹配的模式。
Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("A1:A3")
For Each C In Myrange
strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
If strPattern <> "" Then
strInput = C.Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
C.Offset(0, 1) = regEx.Replace(strInput, "$1")
C.Offset(0, 2) = regEx.Replace(strInput, "$2")
C.Offset(0, 3) = regEx.Replace(strInput, "$3")
Else
C.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
结果:
额外示例模式
String Regex Pattern Explanation
a1aaa [a-zA-Z][0-9][a-zA-Z]{3} Single alpha, single digit, three alpha characters
a1aaa [a-zA-Z]?[0-9][a-zA-Z]{3} May or may not have preceding alpha character
a1aaa [a-zA-Z][0-9][a-zA-Z]{0,3} Single alpha, single digit, 0 to 3 alpha characters
a1aaa [a-zA-Z][0-9][a-zA-Z]* Single alpha, single digit, followed by any number of alpha characters
</i8> \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit
regEx = Nothing
。否则将会出现内存不足异常。 - KirilSet regEx = CreateObject("VBScript.RegExp")
- ZygDThisWorkbook
中。尝试将代码移动到单独的模块
中。 - Automate This为了直接在Excel公式中使用正则表达式,可以使用以下UDF(用户定义函数)。它几乎直接将正则表达式功能作为Excel函数暴露出来。
它需要2-3个参数。
$0
,$1
,$2
等等。 $0
是整个匹配项,$1
及以上对应于正则表达式中各自的匹配组。默认为$0
。提取电子邮件地址:
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")
结果为:some@email.com
提取多个子字符串:
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
结果为:E-Mail: some@email.com, Name: Peter Gordon
将单个单元格中的组合字符串拆分成多个单元格中的各个部分:
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)
结果为:Peter Gordon
some@email.com
...
按以下步骤使用此UDF(粗略基于此 Microsoft 页面,那里有一些很好的额外信息!):
ALT+F11
打开 Microsoft Visual Basic for Applications 编辑器。点击插入模块。如果给模块命名,请确保模块名称与下面的UDF不同(例如将模块命名为 Regex
,函数命名为 regex
会导致#NAME!错误)。
在中间的大文本窗口中插入以下内容:
Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
Dim replaceNumber As Integer
With inputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = matchPattern
End With
With outputRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = "\$(\d+)"
End With
With outReplaceRegexObj
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set inputMatches = inputRegexObj.Execute(strInput)
If inputMatches.Count = 0 Then
regex = False
Else
Set replaceMatches = outputRegexObj.Execute(outputPattern)
For Each replaceMatch In replaceMatches
replaceNumber = replaceMatch.SubMatches(0)
outReplaceRegexObj.Pattern = "\$" & replaceNumber
If replaceNumber = 0 Then
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
Else
If replaceNumber > inputMatches(0).SubMatches.Count Then
'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
regex = CVErr(xlErrValue)
Exit Function
Else
outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
End If
End If
Next
regex = outputPattern
End If
End Function
保存并关闭 Microsoft Visual Basic for Applications 编辑器窗口。
Function foo() As Variant \n foo="Hello World" \n End Function
UDF,看看是否有效。如果可以,请逐步升级到上面的完整内容;如果不行,则可能是某些基本设置出了问题(宏已禁用?)。 - Patrick Böker添加以下代码:
Function RegxFunc(strInput As String, regexPattern As String) As String
Dim regEx As New RegExp
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.pattern = regexPattern
End With
If regEx.Test(strInput) Then
Set matches = regEx.Execute(strInput)
RegxFunc = matches(0).Value
Else
RegxFunc = "not matched"
End If
End Function
正则表达式模式放置在其中一个单元格中,并对其使用绝对引用。
该函数将与创建它的工作簿相关联。
如果需要在不同的工作簿中使用它,请将函数存储在Personal.XLSB中。
RegxFunc(B5,$C$2)
中,您必须使用分号 ";" 而不是逗号 ","。 - devbf这是我的尝试:
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
=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 组件令人生畏的用户能够轻松应对。
“尽管VBScript正则表达式...版本5.5实现了以前版本缺失的许多基本正则表达式功能...JavaScript和VBScript实现了Perl风格的正则表达式。但是,它们缺少许多Perl和其他现代正则表达式语言中的高级功能:”
\A
,或者使用^
符号匹配字符串第一个字符之前的位置\Z
,或者使用$
符号匹配字符串最后一个字符之后的位置(?<=a)b
(虽然支持正向前行断言)(?<!a)b
(虽然支持负向前行断言)\{uFFFF}
/i
(不区分大小写)或/g
(全局)。通过RegExp
对象属性设置这些 > 如果可用,则将RegExp.Global = True
和RegExp.IgnoreCase = True
。'
注释来添加它们。我在使用VBA中的正则表达式时已经遇到了不止一次的问题。通常是在使用LookBehind
时,但有时我甚至会忘记修改器。我自己没有经历过上述所有这些问题,但我想尝试更详细地提到一些更深入的信息。欢迎评论/纠正/添加。非常感谢regular-expressions.info提供如此丰富的信息。
P.S. 你提到了常规的VBA方法和函数,我可以确认它们(至少对我自己而言)在RegEx失败时也很有帮助。
我需要将这个作为单元格函数(比如 SUM
或者 VLOOKUP
)使用,发现很容易实现以下步骤:
在工作簿或其自己的模块中创建以下函数:
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
那么你可以在单元格中使用=REGPLACE(B1, "(\w) (\d+)", "$1$2")
(例如:"A 243" 变成 "A243")
这里有一个 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
Public Sub RegExSearch()
'https://dev59.com/u2Eh5IYBdhLWcg3wVCEb
'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
VBScript_RegExp_55
库几乎无处不在,因此很少出现在特定目标机器上找不到的风险。而且,从 Early Bound 切换到 Late Bound 也无法解决可移植性问题(代码仍然会出错,只是在运行时而不是编译时)。 - chris neilsenVBProject
对象具有References
属性。库引用作为宿主文档中VBA项目的一部分保存。 - Mathieu Guindon
Like
运算符,它提供了一种类似于正则表达式的轻量级功能。即使包装在子程序或函数过程中,它通常比正则表达式快得多。 - Egalth