VBA提取excel中的一些特定数据
实现成如下:
如上俩图,实现左边的数据中提取一部分至右边,一些特定的数据,— — — — — — — — 是每条数据之间的分割线。当然,颜色和线条是没有的,为便于理解我标上去的。点击一个按钮怎么实现呢? 我是完全的新手,几乎刚接触 Excel?VBA
[解决办法]
要编程才能实现这个功能
[解决办法]
你这个数据要有规律,能有标志识别才行.然后编程时,才可以用程序判断.
[解决办法]
'以下代码放入Excel 的VBA模块中 舅:模块1
Public Function GetIP(ByVal Text As String) As String
On Error Resume Next
GetIP = "N/A"
Dim Arr() As String
Dim Arr1() As String
Arr = Split(Text, " ")
For i = 0 To UBound(Arr)
Arr1 = Split(Arr(i), ".")
If UBound(Arr1) = 3 Then
If IsNumeric(Arr1(0)) And IsNumeric(Arr1(1)) And IsNumeric(Arr1(2)) And IsNumeric(Arr1(3)) Then
If CLng(Arr1(0)) < 256 And CLng(Arr1(1)) < 256 And CLng(Arr1(2)) < 256 And CLng(Arr1(3)) < 256 Then
GetIP = Arr(i) & " " & arr(i+1)
Exit Function
End If
End If
End If
Next
End Function
Public Function GetAddress(ByVal Text As String) As String
On Error Resume Next
Dim Arr() As String
Dim Arr1() As String
Arr = Split(Text, " ")
For i = 0 To UBound(Arr)
Arr1 = Split(Arr(i), ".")
If UBound(Arr1) = 3 Then
If IsNumeric(Arr1(0)) And IsNumeric(Arr1(1)) And IsNumeric(Arr1(2)) And IsNumeric(Arr1(3)) Then
If CLng(Arr1(0)) < 256 And CLng(Arr1(1)) < 256 And CLng(Arr1(2)) < 256 And CLng(Arr1(3)) < 256 Then
For j = 0 To i - 1
GetAddress = GetAddress & Arr(j) & " "
Next
Exit Function
End If
End If
End If
Next
GetAddress = "N/A"
End Function
Public Function Get1St(ByVal Text As String) As String
On Error Resume Next
Get1St = "N/A"
Dim Arr() As String
Dim Arr1() As String
Arr = Split(Text, " ")
For i = 0 To UBound(Arr)
If UCase(Arr(i)) = "1ST" And UCase(Arr(i + 1)) = "HALF" Then
Get1St = Arr(i + 2)
Exit Function
End If
Next
End Function
Public Function Get2Nd(ByVal Text As String) As String
On Error Resume Next
Get2Nd = "N/A"
Dim Arr() As String
Dim Arr1() As String
Arr = Split(Text, " ")
For i = 0 To UBound(Arr)
If UCase(Arr(i)) = "2ND" And UCase(Arr(i + 1)) = "HALF" Then
Get2Nd = Arr(i + 2)
Exit Function
End If
Next
End Function