需求:用正则表达式提取D列的***,写到E列中
步骤:
1.打开excel中的vb编辑器,点击“文件”--“选项”--“自定义功能区”,勾选“开发工具”
2.点击确定后,按Ctrl+F11打开vb窗口,点击查看代码,打开代码窗口
3.在代码窗口中输入代码
Sub testRegexp() Dim a, b, c Dim regexp As Object Dim d As String Set regexp = CreateObject("vbscript.regexp") With regexp .Global = True .IgnoreCase = True .Pattern = ".* .{2} \d{8} (\d{18}|\d{17}[xX])" End With For Each a In Range("D2:D4") Set b = regexp.Execute(a.Value) Rem b.Count大于0就是匹配成功 If b.Count > 0 Then For Each c In b Rem SubMatches.Count大于0就是捕获成功 If c.SubMatches.Count > 0 Then Rem vbCrLf是单元格内换行符 d = d + c.SubMatches(0) + vbCrLf End If Next c End If Worksheets("Sheet1").Cells(a.Row, a.Column + 1).Value = d d = "" Next a End Sub
4.按F5运行代码
5.运行效果
参考文档:
VBScript RegExp Object:正则对象说明(英文)