|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- <div>Option Explicit
- Sub 提取条码信息()
- Application.ScreenUpdating = False
- Dim c As Range, i%, str$, s$, j%
- i = 2
- With ActiveSheet
- str = .Cells(i, 1).Value
- s = ".(\d{4})(\*|×|-)(\d+)[\d\s-]*(有盖)?"
- Do While Len(str) > 0
- str = .Cells(i, 1).Value
- For j = 0 To UBound(PickStr(str, s)) - 1
- .Cells(i, 2) = "'" & PickStr(str, s, 0)(j)
- .Cells(i, 3) = IIf(PickStr(str, s, 1)(j) = "-", 1, PickStr(str, s, 2)(j))
- .Cells(i, 4) = PickStr(str, s, 3)(j)
- .Cells(i + 1, 1).EntireRow.Insert shift:=xlDown
- i = i + 1
- Next j
- i = i + 1
- Loop
- '删除空行
- .Range("B2:B" & i + 2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
- End With
- Application.ScreenUpdating = True
- End Sub
- Function PickStr(str$, ptr$, Optional i% = -1)
- 'i, 显示匹配到的值里的分组数组项
- Dim reg As Object, ms, m, sub_str$
- Set reg = CreateObject("VBScript.RegExp")
- With reg
- .Pattern = ptr '正则表达式
- .Global = True '匹配出所有符合条件的字符
- .IgnoreCase = False '不忽略大小写
- .MultiLine = True '多行模式
- Set ms = .Execute(str) '执行匹配
- End With
- If ms.Count Then
- For Each m In ms
- If i = -1 Then
- sub_str = sub_str & m & "|"
- Else
- sub_str = sub_str & m.SubMatches(i) & "|"
- End If
- Next
- Else '未匹配到值时显示空值
- sub_str = sub_str & "|"
- End If
- PickStr = Split(sub_str, "|")
- Set ms = Nothing
- Set reg = Nothing
- End Function</div>
复制代码
|
|