|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
用正则写了一个。供楼主测试。
- Sub Testrep()
- Dim Reg, Arr, Brr, mh, k, s$, i%, x%, y%
- Arr = Sheet1.UsedRange
- ReDim Brr(1 To UBound(Arr), 1 To 6)
- [a1].CurrentRegion.Offset(1).ClearContents
- Set Reg = CreateObject("vbscript.regexp")
- Reg.Pattern = "(^[一-龥]+)|(1|\d{2}-)(\d{10,11})|(0\d{3}-|0\d{2}-)(\d{8}|\d{7})"
- Reg.Global = True
- For i = 2 To UBound(Arr)
- If Trim(Arr(i, 10)) <> "" Then
- x = x + 1: y = 1
- Set mh = Reg.Execute(Arr(i, 10))
- For Each k In mh
- y = IIf(y = 1, 2, IIf(InStr(k, "-") > 3, 3, 4))
- Brr(x, y) = k
- Next
- s = Reg.Replace(Arr(i, 10), "")
- Brr(x, 5) = Mid(s, mh.Count + 1, Len(s))
- Brr(x, 6) = Arr(i, 1) & "件" & Arr(i, 3) & "-" & Arr(i, 2)
- Else
- Arr(i, 1) = Arr(i - 1, 1)
- Brr(x, 6) = Brr(x, 6) & Chr(10) & Arr(i, 1) & "件" & Arr(i, 3) & "-" & Arr(i, 2)
- End If
- Next
- [a2].Resize(UBound(Brr), 6) = Brr
- Set Reg = Nothing
- Set mh = Nothing
- End Sub
复制代码 |
|