如何在Microsoft Excel中使用正则表达式(Regex),包括单元格内和循环。
如何在Excel中使用正则表达式,并利用Excel强大的网格式设置进行数据操作?
- 单元格内函数返回一个匹配的模式或字符串中的替换值。
- 循环浏览一列数据并提取相邻单元格的匹配数据的子函数。
- 什么样的设置是必要的?
- 什么是Excel的正则表达式的特殊字符?
我知道Regex在很多情况下并不理想(使用或不使用正则表达式?),因为excel可以使用左'、
中'、右'、
Instr`类型的命令进行类似操作。
548
7
正则表达式被用于模式匹配。
要在Excel中使用,请遵循以下步骤。
第1步。添加VBA引用到"Microsoft VBScript Regular Expressions 5.5"。
步骤2:定义你的模式
基本的定义:
-
范围。[]
正好匹配这些括号内的一个对象。[a]
匹配字母a[abc]
匹配单个字母,可以是a、b或c。[a-z]
匹配字母表中任何一个小写字母。()
为返回目的将不同的匹配分组。 见下面的例子。{}
在它之前定义的模式的重复副本的乘数。[a]{2}
匹配两个连续的小写字母a。aa
。[a]{1,3}
匹配至少一个和最多三个小写字母a
,aa
,aaa
。+
匹配至少一个,或更多,在它之前定义的模式。a+
将匹配连续的a'sa
,aa
,aaa
, 以此类推。?
匹配前面定义的模式中的零或一个。[a-z]?
匹配空字符串或任何单个小写字母。*
匹配前面定义的模式中的零个或多个。[a-z]*
匹配空字符串或小写字母的字符串。.
匹配除换行之外的任何字符n
。a.
匹配以a开头,以任何字符结尾的两个字符串,除了`n'以外。|
OR运算符a|b
表示可以匹配a
或b
。红色|白色|橙色
正好匹配其中一种颜色。^
非运算符[^0-9]
字符不能包含数字[^aA]
字符不能是小写字母a
或大写字母A
。``逃脱后面的特殊字符(覆盖上述行为)。
.
,\`,
(, ``?
,$`,
/^`)锚定模式:
^
匹配必须发生在字符串的开头^a
第一个字符必须是小写字母a
。^[0-9]
第一个字符必须是一个数字。$
匹配必须发生在字符串的末尾a$
最后一个字符必须是小写字母a
。优先级表:
预定义的字符缩写:
示例1: 以宏的形式运行
要在Excel公式中直接使用正则表达式,下面的UDF(用户定义函数)可以提供帮助。它或多或少地将正则表达式的功能直接暴露为一个Excel函数。
它是如何工作的
它需要2-3个参数。
1.一个要使用正则表达式的文本。 2.一个正则表达式。 3.一个格式字符串,指定结果的样子。它可以包含
$0
,$1
,$2
,以此类推。$0'是整个匹配,
$1'及以上对应于正则表达式中的各个匹配组。默认为"$0"。一些例子
提取一个电子邮件地址。
结果是。
some@email.com
。提取几个子字符串。
结果是。`E-Mail: some@email.com, Name:彼得-戈登
将单个单元格中的组合字符串拆成多个单元格中的组成部分。
结果是。
Peter Gordon
some@email.com
...如何使用
要使用这个UDF,请按以下步骤操作(大致基于这个微软页面。他们那里有一些很好的附加信息!)。
1.在Excel中一个启用了宏的文件('.xlsm')按
2.在列表中找到Microsoft VBScript Regular Expressions 5.5并勾选旁边的复选框。
3.点击OK。
3.点击插入模块。如果你给你的模块起了一个不同的名字,请确保该模块不与下面的UDF同名(例如,将模块命名为
ALT+F11
打开Microsoft Visual Basic for Applications编辑器。 2.2.为正则表达式库添加VBA参考(无耻地从[Portland Runners++ answer][2]复制)。 1.点击工具 ->引用(请原谅我的德语截图)Regex
和函数regex
会导致#NAME!*错误)。5.保存并关闭Microsoft Visual Basic for Applications编辑器窗口。
[2]:/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell and-loops#answer-22542835
对[patszim][1]'的[答案][2]进行扩展,供有急事的人参考。
Alt+F11</kbd>。 打开VBA/宏窗口。
在工具下添加对regex的引用,然后在引用下添加。 [![![Excel VBA表格添加引用][3]][3]
并选择Microsoft VBScript Regular Expression 5.5。 [![!/[Excel VBA添加regex引用][4]][4]
插入一个新的模块(代码需要驻留在模块中,否则无法工作)。 [![!/[Excel VBA插入代码模块][5]][5]。
在新插入的模块中: [![!/[Excel VBA插入模块代码][6]][6]
添加以下代码。
函数RegxFunc(strInput As String, regexPattern As String) As String。 Dim regEx As New RegExp 有regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern 结束于
如果 regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value. 其他 RegxFunc = "不匹配".RegxFunc = "不匹配" End If 结束功能
[1]: https://stackoverflow.com/users/1975049/patszim [2]: https://stackoverflow.com/a/28176749/1699071 [3]: [3]:https://i.stack.imgur.com/sKCdA.png [4]: https://i.stack.imgur.com/nmSgP.png [5]: https://i.stack.imgur.com/RaLQ0.png [6]: https://i.stack.imgur.com/DFJ7F.png [7]: https://i.stack.imgur.com/XnS6t.png
这是我的尝试。
我需要将其作为单元格函数使用(如
SUM
或VLOOKUP
),发现很容易。打开开发者工具Alt</kbd>。
在工作簿或自己的模块中创建以下函数。
函数REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant。 Dim regex as New VBScript_RegExp_55.Reg RegExp Dim strInput as String
strInput = myRange.Value
用regex .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern 结束于
REGPLACE = regex.Replace(strInput, outputPattern)
结束功能
然后可以在单元格中使用
=REGPLACE(B1, "(\w) (\d+)", "$1$2")
(例:) "A 243" 至"A243")这里有一个
regex_subst()
函数。 例子:这是简化后的代码(反正对我来说比较简单)。 我不知道如何使用上面的代码建立一个合适的输出模式,以便像我的例子一样工作。
我不想启用一个参考库,因为我需要我的脚本是可移植的。
Dim foo As New VBScript_RegExp_55.RegExp
行引起User Defined Type Not Defined
错误,但我找到了一个对我有用的解决方案。你要做的是在单元格
A1
中放入一个示例字符串,然后测试你的strPattern
。 一旦成功了,就可以根据需要调整rng
。