|
![](https://clubstatic.excelhome.net/image/common/ico_lz.png)
楼主 |
发表于 2017-11-2 11:55
|
显示全部楼层
数据拆分问题,希望大神们多多帮助
'http://club.excelhome.net/thread-1376659-1-1.html
Sub Adele()
Dim arr, brr(), d As Object, crr(), Ma, Mat
arr = Sheet1.Range("a1").CurrentRegion
ReDim brr(1 To UBound(arr))
Set d = CreateObject("Scripting.dictionary")
Set Reg = CreateObject("Vbscript.RegExp")
With Reg
.Global = True
.Pattern = "(?!适合)([\u4e00-\u9fa5]{2,})([0-9]?)|[A-Za-z]+"
End With
For x = 2 To UBound(arr)
kk = kk + 1
Set Mat = Reg.Execute(Mid(arr(x, 2), 6))
For Each Ma In Mat
m = m + 1: k = 0
For y = 1 To Ma.submatches.Count
k = k + 1
If Ma.submatches(y - 1) = "" Then s = s & "," & Ma Else s = s & "," & Ma.submatches(y - 1)
Next
Next Ma
brr(kk) = s: s = ""
Next
ReDim crr(1 To UBound(brr), 1 To 10)
For a = 1 To UBound(brr)
If Len(brr(a)) Then
ss = Split(brr(a), ",")
If UBound(ss) = 10 Then
crr(a, 1) = ss(1): crr(a, 2) = ss(5): crr(a, 3) = ss(9): crr(a, 4) = ss(2) / 2
crr(a, 5) = ss(1): crr(a, 6) = ss(7): crr(a, 7) = ss(9): crr(a, 8) = ss(2) / 2
crr(a, 9) = ss(3): crr(a, 10) = ss(4)
Else
crr(a, 1) = ss(1): crr(a, 2) = ss(5): crr(a, 3) = ss(7): crr(a, 4) = ss(2)
crr(a, 9) = ss(3): crr(a, 10) = ss(4)
End If
End If
Next
Sheet3.Range("a23").Resize(UBound(crr), UBound(crr, 2)) = crr
End Sub |
|