|
本帖最后由 hrpotter 于 2012-9-5 15:30 编辑
你的数据还是比较乱的,不规范,如果按“,”分,不止五列的,有的是五列,有的超过五列,暂时按前二个,和后二个逗号的分开,中间的都归在一起,不然就得手工一个个分了- Sub test()
- Dim arr, brr, crr
- Dim i As Long, j As Long
- arr = Range("b2:b" & Range("b65536").End(xlUp).Row)
- ReDim brr(1 To UBound(arr), 1 To 5)
- For i = 1 To UBound(arr)
- crr = Split(arr(i, 1), ",")
- brr(i, 1) = crr(0)
- brr(i, 2) = crr(1)
- brr(i, 4) = crr(UBound(crr) - 1)
- brr(i, 5) = crr(UBound(crr))
- For j = 2 To UBound(crr) - 2
- brr(i, 3) = brr(i, 3) & "," & crr(j)
- Next
- brr(i, 3) = Mid(brr(i, 3), 2)
- Next
- Range("c2").Resize(UBound(brr), 5) = brr
- Range("b:g").Columns.AutoFit
- End Sub
复制代码 |
|