excel文件格式:
需求:用正则表达式提取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.运行效果
参考文档: