CTRL+E在实际工作中可以很方便地帮我们解决单元的内容拆分问题,大大减少的重复和枯燥的CTRL+C和V的工作。但是实际中有些内容得不到想要得结果必须通过自定义函数实现所要得功能。如下图所示
用CTRL+E得到得结果是这样,和实际所想获得得结果不一致。
针对这种问题可以通过自定义函数进行解决:
Public Sub autoExtract(rng As Range)
Dim i As Integer, j As Integer, k As Integer
For i = 1 To Len(rng)
If Mid(rng, i, 1) = " " Then
counter = counter + 1
If counter = 1 Then
' rng.Offset(0, 1) = Mid(rng, 1, i - 1)
' MsgBox Mid(rng, 1, i - 1)
rng.Offset(0, 1) = Mid(rng, 1, i - 1)
For j = 1 To 15
If Mid(rng, i + j, 1) = "(" Then
count_j = count_j + 1
' rng.Offset(0, 2) = Mid(rng, i + 1, j - 1)
' MsgBox Mid(rng, i + 1, j - 1)
If count_j = 1 Then
rng.Offset(0, 2) = Mid(rng, i + 1, j - 1)
For k = 1 To 100
If Mid(rng, i + j + k, 1) = Chr(35) Then
rng.Offset(0, 3) = Mid(rng, i + j + 1, k - 2)
Exit For
End If
Next
End If
End If
Next
End If
End If
Next
End Sub
让后写一个循环执行得程序就可以批量完成内容得拆分