|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 活在理想的世界 于 2018-7-15 09:10 编辑
- Sub d()
- Dim arr(), R, R1, m, w
- Set w = WorksheetFunction
- Set R = CreateObject("Vbscript.Regexp")
- Set R1 = CreateObject("Vbscript.Regexp")
- R.Global = 1: R.Pattern = "[ ,,。]+"
- R1.Global = 1: R1.Pattern = "\.[一-龥]"
- arr = Sheet1.Range("a1").CurrentRegion.Resize(, 2)
- For i = 1 To UBound(arr)
- arr(i, 2) = R.Replace(arr(i, 1), "")
- Set m = R1.Execute(arr(i, 2))
- If m.Count > 0 Then
- For j = 1 To m.Count
- arr(i, 2) = w.Substitute(arr(i, 2), m.Item(j - 1), "." & 0 & Split(m.Item(j - 1), ".")(1))
- Next
- End If
- Next
- Sheet1.Range("a1").CurrentRegion.Resize(, 2) = arr
- End Sub
复制代码
|
|