|
Sub test()
Dim ar, i&, j&, br(), md As Date, title As Variant
With Sheets("数据")
ar = .Range("a2").CurrentRegion
ReDim br(1 To UBound(ar) - 1)
For i = 1 To UBound(br)
br(i) = Split(ar(i + 1, 1), ",")
Next
End With
ReDim ar(1 To UBound(br), 1 To 7)
For i = 1 To UBound(br)
md = Split(Split(br(i)(0), ":")(1), ";")(0)
ar(i, 1) = Format(CDate(md), "yyyy/mm/dd")
ar(i, 2) = Format(CDate(md), "hh:mm:ss")
ar(i, 3) = Split(Split(br(i)(0), ":")(1), ";")(1)
For j = 1 To Len(Split(br(i)(0), ":")(2))
If Not IsNumeric(Mid(Split(br(i)(0), ":")(2), j, 1)) And Mid(Split(br(i)(0), ":")(2), j, 1) <> "'" Then
ar(i, 4) = ar(i, 4) & Mid(Split(br(i)(0), ":")(2), j, 1)
End If
Next
ar(i, 5) = br(i)(5)
ar(i, 6) = Val(br(i)(UBound(br(i)) - 2))
ar(i, 7) = Val(br(i)(UBound(br(i)) - 1))
Next
title = [{"日期","时间","经销商","姓名","类型","台数","比例"}]
With Sheets("结果")
.Range("a1").CurrentRegion.ClearContents
.Range("a1").Resize(1, UBound(title)) = title
.Range("a2").Resize(UBound(ar), UBound(ar, 2)) = ar
End With
End Sub
请参考附件
单元格提取.zip
(23.96 KB, 下载次数: 8)
|
|