|
发表于 2014-10-12 00:07
|
显示全部楼层
本楼为最佳答案
正则提取数字和字母- Sub ExtractData()
- Dim arr, arrO, i, j, k, l
- arr = Range("A1").CurrentRegion
- ReDim arrO(1 To UBound(arr), 1 To 20)
- Dim objRegExp As Object, obj As Object
- Set objRegExp = CreateObject("VBScript.regExp")
- For i = 1 To UBound(arr)
- With objRegExp
- k = 0
- .Global = True
- .Pattern = "\d+"
- If .test(arr(i, 1)) Then
- Set obj = .Execute(arr(i, 1))
- k = obj.Count
- For j = 0 To obj.Count - 1
- arrO(i, j + 1) = obj(j)
- Next j
- End If
- .Pattern = "\D+"
- If .test(arr(i, 1)) Then
- Set obj = .Execute(arr(i, 1))
- For j = 0 To obj.Count - 1
- arrO(i, k + j + 1) = obj(j)
- Next j
- End If
- End With
- Next i
- Set objRegExp = Nothing
- Range("B1").Resize(UBound(arrO), UBound(arrO, 2)) = arrO
- End Sub
复制代码 |
|