I have to extract Hostname and IP address which are the strings after Hostnames:
and IP Address:
from a text cell as shown below, one cell may have multiple Hostnames and IP addresses, and I have many cells as such:
我必须提取主机名和IP地址,它们是Hostnames之后的字符串:和IP地址:从文本单元格如下所示,一个单元格可能有多个主机名和IP地址,我有很多单元格如下:
Here my sample data:
这是我的样本数据:
Please refer to CR_Implementation_Reversion_Plan UNIX and Informatica (9.1)
--------------------
IP Address: 10.89.140.123, 10.89.140.125, 10.89.140.127, 10.89.140.92
Hostname: a01gbiapp1a, a01gbiapp1b, a01gbiapp1z, w01ggdwtd1a
Informatica ID: proddeploy(Informatica)
Unix ID:cbitimpl, root(su- bipadm,pdm, bipxtr, bdwadm, cpmsadm, dqadm, rmgadm, cbgadm, bdwxtr, hkb2adm, biptwadm, bipinadm, bipcnadm, biphkadm, bipidadm, bipotadm)
UNIX and Informatica (7.1)
--------------------
IP Address: 10.89.140.52, 10.89.140.53, 10.89.140.37, 10.89.140.37
Hostname: a01ginf1a , a01ginf1b, a01ginf1z, a01ginf1z
Informatica ID: proddeploy(Informatica)
Unix ID: cbitimpl, root(su- bipadm, bipdm, bipxtr, bdwadm, cpmsadm, dqadm, rmgadm, cbgadm, bdwxtr, hkb2adm, biptwadm, bipinadm, bipcnadm, biphkadm, bipidadm, bipotadm)
Teradata
--------
IP Address: 10.89.140.97
Hostname: r01gtddb1a
ID: fdwadmin, bdwadmin
InformaticaClient
------------------
w01gdnv1a or w01ggdwtd1a or w01ggdwtd10a 10.89.140.92(Informatica Client),
ID: infadeploy
BO implementation:Draw Rack Keys:
-------------------------------------
IP Address: 10.89.140.105, 10.89.140.106, 10.89.140.48, 10.89.140.49
Hostname: W01GBOXIAPP1A, W01GBOXIAPP2A, W01GBOXIAPP3A, W01GBOXIAPP4A
BO ID: boimpl
SAS
---
IP Address: 10.89.136.122, 10.89.136.125, 10.89.136.126
Hostname: w01gsaseapp1a, a01gsaseapp1a, a01gsaseapp2a
SAS ID: sas_impl
Power Exchange
--------------
IP ADDRESS: 10.80.250.73
ID: DMSDDTL1, DMSDDTL2, DMSDDTL3
UI
---
IP: 10.89.140.112
Hostname: A01GWAWEB1A
ID: wasuser
WODM
----
IP: 10.89.140.109
Hostname: A01GWABRE1A
ID: wodmadm
DB2 Server
----------
IP: 10.89.140.113
DB : DBSWATS
Schema : watsusr
DBS_IBMSG_BTEAM:
Pls allow Ramakishore M/ Prashanth Badugu/ Srinivasa Theerdhala Part B and Satish Parmarthy / Dileep EP / Krishna Reddy / Raghavendra Goud Part A to withdraw the
"infaoper" and "proddeploy" ID for server a01gbiapp1a, a01gbiapp1b, a01gbiapp1z.
I have used Perl to extract strings from HTML file like this:
我使用Perl从HTML文件中提取字符串,如下所示:
if ($ReadFile =~ /^OS Version\/Service Pack\:/) {
#print "$ReadFile\n"
($OSVer)=(split /:/, $ReadFile)[1]; print "$OSVer\n";
&myServerInfo("$OSVer","4");
}
I'm wondering can I use the same method to extract Hostname and IP address for such text cell in VBA
, any help would be appreciated.
我想知道我可以使用相同的方法在VBA中提取此类文本单元的主机名和IP地址,任何帮助将不胜感激。
3 个解决方案
#1
3
Firstly, I want to tell about input data which my code can process perfectly. In your input data, rhythm of "IP Address" and "Hostname" must be equal. I means static serial for those pair as follow:
首先,我想告诉我的代码可以完美处理的输入数据。在输入数据中,“IP地址”和“主机名”的节奏必须相等。我的意思是这些对的静态序列如下:
................................
---------------------------
IP Address:
Hostname:
................................
---------------------------
IP ADDRESS:
Hostname:
................................
---------------------------
IP:
Hostname:
................................
---------------------------
IP Address:
................................
---------------------------
IP Address:
Hostname:................................ ------------------ --------- IP地址:主机名:................................ ---- ----------------------- IP地址:主机名:...................... .......... --------------------------- IP:主机名:......... ....................... --------------------------- IP地址: ................................ - - - - - - - - ------------ IP地址:主机名:
The input data must be that serial format. Only if in that format,my code will work well. Ok..? I means it can't for following serial input:
输入数据必须是串行格式。只有采用该格式,我的代码才能正常运行。好..?我的意思是它无法跟随串行输入:
................................
---------------------------
IP Address:
Hostname:
................................
---------------------------
IP ADDRESS:
Hostname:
................................
---------------------------
Hostname:
IP Address:................................ ------------------ --------- IP地址:主机名:................................ ---- ----------------------- IP地址:主机名:...................... .......... ---------------------------主机名:IP地址:
So, I made small modification to your input data as follow:
所以,我对输入数据做了一些小修改,如下所示:
Please refer to CR_Implementation_Reversion_Plan UNIX and Informatica (9.1)
---------------------------------------------------------------------------------
IP Address: 10.89.140.123, 10.89.140.125, 10.89.140.127, 10.89.140.92
Hostname: a01gbiapp1a, a01gbiapp1b, a01gbiapp1z, w01ggdwtd1a
Informatica ID: proddeploy(Informatica)
Unix ID:cbitimpl, root(su- bipadm,pdm, bipxtr, bdwadm, cpmsadm, dqadm, rmgadm, cbgadm,
bdwxtr, hkb2adm, biptwadm, bipinadm, bipcnadm, biphkadm, bipidadm, bipotadm)
UNIX and Informatica (7.1)
---------------------------------------------------------------------------------
IP Address: 10.89.140.52, 10.89.140.53, 10.89.140.37, 10.89.140.37
Hostname: a01ginf1a , a01ginf1b, a01ginf1z, a01ginf1z
Informatica ID: proddeploy(Informatica)
Unix ID: cbitimpl, root(su- bipadm, bipdm, bipxtr, bdwadm, cpmsadm, dqadm, rmgadm,
cbgadm, bdwxtr, hkb2adm, biptwadm, bipinadm, bipcnadm, biphkadm, bipidadm,
bipotadm)
Teradata
---------------------------------------------------------------------------------
IP Address: 10.89.140.97
Hostname: r01gtddb1a
ID: fdwadmin, bdwadmin
InformaticaClient
---------------------------------------------------------------------------------
IP Address: 10.89.140.92
Hostname: w01gdnv1a or w01ggdwtd1a or w01ggdwtd10a
ID: infadeploy(Informatica Client)
BO implementation:Draw Rack Keys:
---------------------------------------------------------------------------------
IP: 10.89.140.105, 10.89.140.106, 10.89.140.48, 10.89.140.49
Hostname: W01GBOXIAPP1A, W01GBOXIAPP2A, W01GBOXIAPP3A, W01GBOXIAPP4A
BO ID: boimpl
SAS
---------------------------------------------------------------------------------
IP ADDRESS: 10.89.136.122, 10.89.136.125, 10.89.136.126
Hostname: w01gsaseapp1a, a01gsaseapp1a, a01gsaseapp2a
SAS ID: sas_impl
Power Exchange
---------------------------------------------------------------------------------
IP Address: 10.80.250.73
ID: DMSDDTL1, DMSDDTL2, DMSDDTL3
UI
---------------------------------------------------------------------------------
IP Address: 10.89.140.112
Hostname: A01GWAWEB1A
ID: wasuser
WODM
---------------------------------------------------------------------------------
IP Address: 10.89.140.109
Hostname: A01GWABRE1A
ID: wodmadm
DB2 Server
---------------------------------------------------------------------------------
IP Address: 10.89.140.113
DB : DBSWATS
Schema : watsusr
DBS_IBMSG_BTEAM:
Pls allow Ramakishore M/ Prashanth Badugu/ Srinivasa Theerdhala Part B and
Satish Parmarthy / Dileep EP / Krishna Reddy / Raghavendra Goud Part A to withdraw
the "infaoper" and "proddeploy" ID for server a01gbiapp1a, a01gbiapp1b, a01gbiapp1z.
And I modify my code for run that input as follow:
我修改我的代码运行输入如下:
Public Sub splitHostnameAndIPAddress()
Dim addressStream As String
Dim lineList() As String
Dim line As Integer
Dim tempHosts, tempIps As String
Dim hostList(), ipList() As String
Dim hostIndex, ipIndex, tempIndex As Integer
Dim result As String
Dim ipFlag As Boolean
hostIndex = 1
ipIndex = 1
'Get address string from cell
addressStream = Sheets("Sheet5").Range("A1")
'Split by vbLf(line by line)
lineList = Split(addressStream, vbLf)
'Loop all line
For line = 0 To UBound(lineList)
'If "IP Address" string include in line, store ip address
If InStr(lineList(line), "IP Address") Or InStr(lineList(line), "IP ADDRESS") Or InStr(lineList(line), "IP") Then
'Check for getting right pair.
If ipFlag Then
hostIndex = hostIndex + 1
Else
ipFlag = True
End If
'Getting Ip(s)
tempIps = Trim(Split(lineList(line), ":")(1))
'If there is several ip in string which are separated by ","
If InStr(tempIps, ",") Then
'Loop ip list which is separated by "," and store
For tempIndex = 0 To UBound(Split(tempIps, ","))
ReDim Preserve ipList(ipIndex)
ipList(ipIndex) = Trim(Split(tempIps, ",")(tempIndex))
ipIndex = ipIndex + 1
Next tempIndex
'Else single ip is store
Else
ReDim Preserve ipList(ipIndex)
ipList(ipIndex) = tempIps
ipIndex = ipIndex + 1
End If
'If "Hostnames" string include in line, store host name
ElseIf InStr(lineList(line), "Hostname") Then
'Check for getting right pair.
If ipFlag Then
ipFlag = False
Else
ipIndex = ipIndex + 1
End If
'Getting host(s)
tempHosts = Trim(Split(lineList(line), ":")(1))
'If there is several host in string which are separated by ","
If InStr(tempHosts, ",") Then
'Loop host list which is separated by "," and store
For tempIndex = 0 To UBound(Split(tempHosts, ","))
ReDim Preserve hostList(hostIndex)
hostList(hostIndex) = Trim(Split(tempHosts, ",")(tempIndex))
hostIndex = hostIndex + 1
Next tempIndex
'Else single host is store
Else
ReDim Preserve hostList(hostIndex)
hostList(hostIndex) = tempHosts
hostIndex = hostIndex + 1
End If
End If
Next line
'Adjust two list size
If hostIndex > ipIndex Then
ReDim Preserve ipList(hostIndex - 1)
ElseIf ipIndex > hostIndex Then
ReDim Preserve hostList(ipIndex - 1)
End If
'Loop host list
For index = 1 To UBound(hostList)
'Add host & ip pair
result = result & ipList(index) & vbTab & hostList(index) & vbNewLine
Next index
'Show result
MsgBox result
End Sub
And then I tested my code and I got the following result.
然后我测试了我的代码,得到了以下结果。
I try what I can for that problem. I can't think for next anymore input data. So, try to adjust input data according to my suggestion. If you can, you can modify my code to meet your requirement. Thanks for asking an challenging question.
我尽我所能解决这个问题。我不能再考虑下一个输入数据了。因此,请根据我的建议尝试调整输入数据。如果可以,您可以修改我的代码以满足您的要求。感谢您提出具有挑战性的问题。
#2
0
In VBA this will put in the cell to the right e.g. if above is in A1, select A1 run this and then in B1,C1,D1 you would have "a01...","10.89...","swnet"
在VBA中,这将放入右侧单元格,例如如果上面是A1,选择A1运行这个,然后在B1,C1,D1中你会得到“a01 ...”,“10.89 ......”,“swnet”
Sub t()
With ActiveCell
sp = Split(.Value, Chr(10))
For i = 0 To UBound(sp)
sp2 = Split(sp(i), ":")
.Offset(0, 1 + i).Value = Trim(sp2(1))
Next
End With
End Sub
Could change activecell to a range and then loop the above code to replicate for more tahn one line at a time
可以将activecell更改为一个范围,然后循环上面的代码,以便一次复制更多的一行
#3
0
This gets most of them. It assumes the data are in cell A1 of the active worksheet. It can easily be modified to work on other cells. Let me know if you need that.
这得到了大部分。它假定数据位于活动工作表的单元格A1中。它可以很容易地修改为适用于其他单元格。如果您需要,请告诉我。
Public Sub ParseHostIP()
Dim i As Long
Dim s As String, sIP As String, sHost As String
Dim v As Variant, vIP As Variant, vHost As Variant
s = [a1]
s = Replace(Replace(Replace(s, "IP Address:", "~~"), "IP ADDRESS:", "~~"), "IP:", "~~")
s = Replace(Replace(s, "Hostname:", "~~"), " ", vbNullString)
v = Split(s, vbLf)
For i = 0 To UBound(v)
If Left$(v(i), 2) = "~~" Then
'Stop
If Left$(v(i + 1), 2) = "~~" Then
sIP = sIP & "," & Mid$(v(i), 3)
sHost = sHost & "," & Mid$(v(i + 1), 3)
i = i + 1
End If
End If
Next
vIP = Split(Mid$(sIP, 2), ",")
vHost = Split(Mid$(sHost, 2), ",")
'*******************************************
' Done.
' The rest is here only to display results.
'*******************************************
s = vbNullString
For i = 0 To UBound(vIP)
s = s & vIP(i) & vbTab & vHost(i) & vbLf
Next
MsgBox s
End Sub
Update #1
Based on new information provided in the comments here is a revised version:
根据评论中提供的新信息,这里是修订版:
Public Sub ParseHostIP()
Dim i As Long, k As Long
Dim s As String, sIP As String, sHost As String
Dim vIn As Variant, vOut As Variant, v As Variant
With Worksheets(1).Columns(2).Cells(4)
vIn = .Resize(.End(xlDown).Row).Value
End With
ReDim vOut(1 To UBound(vIn), 1 To 2)
For k = 1 To UBound(vIn)
s = vIn(k, 1)
s = Replace(Replace(Replace(s, "IP Address:", "~~"), "IP ADDRESS:", "~~"), "IP:", "~~")
s = Replace(Replace(s, "Hostname:", "~~"), " ", vbNullString)
v = Split(s, vbLf)
For i = 0 To UBound(v)
If Left$(v(i), 2) = "~~" Then
'Stop
If Left$(v(i + 1), 2) = "~~" Then
sIP = sIP & "," & Mid$(v(i), 3)
sHost = sHost & "," & Mid$(v(i + 1), 3)
i = i + 1
End If
End If
Next
vOut(k, 1) = Mid$(sIP, 2)
vOut(k, 2) = Mid$(sHost, 2)
Next
Worksheets(2).Range("a1").Resize(UBound(vOut), 2) = vOut
End Sub
#1
3
Firstly, I want to tell about input data which my code can process perfectly. In your input data, rhythm of "IP Address" and "Hostname" must be equal. I means static serial for those pair as follow:
首先,我想告诉我的代码可以完美处理的输入数据。在输入数据中,“IP地址”和“主机名”的节奏必须相等。我的意思是这些对的静态序列如下:
................................
---------------------------
IP Address:
Hostname:
................................
---------------------------
IP ADDRESS:
Hostname:
................................
---------------------------
IP:
Hostname:
................................
---------------------------
IP Address:
................................
---------------------------
IP Address:
Hostname:................................ ------------------ --------- IP地址:主机名:................................ ---- ----------------------- IP地址:主机名:...................... .......... --------------------------- IP:主机名:......... ....................... --------------------------- IP地址: ................................ - - - - - - - - ------------ IP地址:主机名:
The input data must be that serial format. Only if in that format,my code will work well. Ok..? I means it can't for following serial input:
输入数据必须是串行格式。只有采用该格式,我的代码才能正常运行。好..?我的意思是它无法跟随串行输入:
................................
---------------------------
IP Address:
Hostname:
................................
---------------------------
IP ADDRESS:
Hostname:
................................
---------------------------
Hostname:
IP Address:................................ ------------------ --------- IP地址:主机名:................................ ---- ----------------------- IP地址:主机名:...................... .......... ---------------------------主机名:IP地址:
So, I made small modification to your input data as follow:
所以,我对输入数据做了一些小修改,如下所示:
Please refer to CR_Implementation_Reversion_Plan UNIX and Informatica (9.1)
---------------------------------------------------------------------------------
IP Address: 10.89.140.123, 10.89.140.125, 10.89.140.127, 10.89.140.92
Hostname: a01gbiapp1a, a01gbiapp1b, a01gbiapp1z, w01ggdwtd1a
Informatica ID: proddeploy(Informatica)
Unix ID:cbitimpl, root(su- bipadm,pdm, bipxtr, bdwadm, cpmsadm, dqadm, rmgadm, cbgadm,
bdwxtr, hkb2adm, biptwadm, bipinadm, bipcnadm, biphkadm, bipidadm, bipotadm)
UNIX and Informatica (7.1)
---------------------------------------------------------------------------------
IP Address: 10.89.140.52, 10.89.140.53, 10.89.140.37, 10.89.140.37
Hostname: a01ginf1a , a01ginf1b, a01ginf1z, a01ginf1z
Informatica ID: proddeploy(Informatica)
Unix ID: cbitimpl, root(su- bipadm, bipdm, bipxtr, bdwadm, cpmsadm, dqadm, rmgadm,
cbgadm, bdwxtr, hkb2adm, biptwadm, bipinadm, bipcnadm, biphkadm, bipidadm,
bipotadm)
Teradata
---------------------------------------------------------------------------------
IP Address: 10.89.140.97
Hostname: r01gtddb1a
ID: fdwadmin, bdwadmin
InformaticaClient
---------------------------------------------------------------------------------
IP Address: 10.89.140.92
Hostname: w01gdnv1a or w01ggdwtd1a or w01ggdwtd10a
ID: infadeploy(Informatica Client)
BO implementation:Draw Rack Keys:
---------------------------------------------------------------------------------
IP: 10.89.140.105, 10.89.140.106, 10.89.140.48, 10.89.140.49
Hostname: W01GBOXIAPP1A, W01GBOXIAPP2A, W01GBOXIAPP3A, W01GBOXIAPP4A
BO ID: boimpl
SAS
---------------------------------------------------------------------------------
IP ADDRESS: 10.89.136.122, 10.89.136.125, 10.89.136.126
Hostname: w01gsaseapp1a, a01gsaseapp1a, a01gsaseapp2a
SAS ID: sas_impl
Power Exchange
---------------------------------------------------------------------------------
IP Address: 10.80.250.73
ID: DMSDDTL1, DMSDDTL2, DMSDDTL3
UI
---------------------------------------------------------------------------------
IP Address: 10.89.140.112
Hostname: A01GWAWEB1A
ID: wasuser
WODM
---------------------------------------------------------------------------------
IP Address: 10.89.140.109
Hostname: A01GWABRE1A
ID: wodmadm
DB2 Server
---------------------------------------------------------------------------------
IP Address: 10.89.140.113
DB : DBSWATS
Schema : watsusr
DBS_IBMSG_BTEAM:
Pls allow Ramakishore M/ Prashanth Badugu/ Srinivasa Theerdhala Part B and
Satish Parmarthy / Dileep EP / Krishna Reddy / Raghavendra Goud Part A to withdraw
the "infaoper" and "proddeploy" ID for server a01gbiapp1a, a01gbiapp1b, a01gbiapp1z.
And I modify my code for run that input as follow:
我修改我的代码运行输入如下:
Public Sub splitHostnameAndIPAddress()
Dim addressStream As String
Dim lineList() As String
Dim line As Integer
Dim tempHosts, tempIps As String
Dim hostList(), ipList() As String
Dim hostIndex, ipIndex, tempIndex As Integer
Dim result As String
Dim ipFlag As Boolean
hostIndex = 1
ipIndex = 1
'Get address string from cell
addressStream = Sheets("Sheet5").Range("A1")
'Split by vbLf(line by line)
lineList = Split(addressStream, vbLf)
'Loop all line
For line = 0 To UBound(lineList)
'If "IP Address" string include in line, store ip address
If InStr(lineList(line), "IP Address") Or InStr(lineList(line), "IP ADDRESS") Or InStr(lineList(line), "IP") Then
'Check for getting right pair.
If ipFlag Then
hostIndex = hostIndex + 1
Else
ipFlag = True
End If
'Getting Ip(s)
tempIps = Trim(Split(lineList(line), ":")(1))
'If there is several ip in string which are separated by ","
If InStr(tempIps, ",") Then
'Loop ip list which is separated by "," and store
For tempIndex = 0 To UBound(Split(tempIps, ","))
ReDim Preserve ipList(ipIndex)
ipList(ipIndex) = Trim(Split(tempIps, ",")(tempIndex))
ipIndex = ipIndex + 1
Next tempIndex
'Else single ip is store
Else
ReDim Preserve ipList(ipIndex)
ipList(ipIndex) = tempIps
ipIndex = ipIndex + 1
End If
'If "Hostnames" string include in line, store host name
ElseIf InStr(lineList(line), "Hostname") Then
'Check for getting right pair.
If ipFlag Then
ipFlag = False
Else
ipIndex = ipIndex + 1
End If
'Getting host(s)
tempHosts = Trim(Split(lineList(line), ":")(1))
'If there is several host in string which are separated by ","
If InStr(tempHosts, ",") Then
'Loop host list which is separated by "," and store
For tempIndex = 0 To UBound(Split(tempHosts, ","))
ReDim Preserve hostList(hostIndex)
hostList(hostIndex) = Trim(Split(tempHosts, ",")(tempIndex))
hostIndex = hostIndex + 1
Next tempIndex
'Else single host is store
Else
ReDim Preserve hostList(hostIndex)
hostList(hostIndex) = tempHosts
hostIndex = hostIndex + 1
End If
End If
Next line
'Adjust two list size
If hostIndex > ipIndex Then
ReDim Preserve ipList(hostIndex - 1)
ElseIf ipIndex > hostIndex Then
ReDim Preserve hostList(ipIndex - 1)
End If
'Loop host list
For index = 1 To UBound(hostList)
'Add host & ip pair
result = result & ipList(index) & vbTab & hostList(index) & vbNewLine
Next index
'Show result
MsgBox result
End Sub
And then I tested my code and I got the following result.
然后我测试了我的代码,得到了以下结果。
I try what I can for that problem. I can't think for next anymore input data. So, try to adjust input data according to my suggestion. If you can, you can modify my code to meet your requirement. Thanks for asking an challenging question.
我尽我所能解决这个问题。我不能再考虑下一个输入数据了。因此,请根据我的建议尝试调整输入数据。如果可以,您可以修改我的代码以满足您的要求。感谢您提出具有挑战性的问题。
#2
0
In VBA this will put in the cell to the right e.g. if above is in A1, select A1 run this and then in B1,C1,D1 you would have "a01...","10.89...","swnet"
在VBA中,这将放入右侧单元格,例如如果上面是A1,选择A1运行这个,然后在B1,C1,D1中你会得到“a01 ...”,“10.89 ......”,“swnet”
Sub t()
With ActiveCell
sp = Split(.Value, Chr(10))
For i = 0 To UBound(sp)
sp2 = Split(sp(i), ":")
.Offset(0, 1 + i).Value = Trim(sp2(1))
Next
End With
End Sub
Could change activecell to a range and then loop the above code to replicate for more tahn one line at a time
可以将activecell更改为一个范围,然后循环上面的代码,以便一次复制更多的一行
#3
0
This gets most of them. It assumes the data are in cell A1 of the active worksheet. It can easily be modified to work on other cells. Let me know if you need that.
这得到了大部分。它假定数据位于活动工作表的单元格A1中。它可以很容易地修改为适用于其他单元格。如果您需要,请告诉我。
Public Sub ParseHostIP()
Dim i As Long
Dim s As String, sIP As String, sHost As String
Dim v As Variant, vIP As Variant, vHost As Variant
s = [a1]
s = Replace(Replace(Replace(s, "IP Address:", "~~"), "IP ADDRESS:", "~~"), "IP:", "~~")
s = Replace(Replace(s, "Hostname:", "~~"), " ", vbNullString)
v = Split(s, vbLf)
For i = 0 To UBound(v)
If Left$(v(i), 2) = "~~" Then
'Stop
If Left$(v(i + 1), 2) = "~~" Then
sIP = sIP & "," & Mid$(v(i), 3)
sHost = sHost & "," & Mid$(v(i + 1), 3)
i = i + 1
End If
End If
Next
vIP = Split(Mid$(sIP, 2), ",")
vHost = Split(Mid$(sHost, 2), ",")
'*******************************************
' Done.
' The rest is here only to display results.
'*******************************************
s = vbNullString
For i = 0 To UBound(vIP)
s = s & vIP(i) & vbTab & vHost(i) & vbLf
Next
MsgBox s
End Sub
Update #1
Based on new information provided in the comments here is a revised version:
根据评论中提供的新信息,这里是修订版:
Public Sub ParseHostIP()
Dim i As Long, k As Long
Dim s As String, sIP As String, sHost As String
Dim vIn As Variant, vOut As Variant, v As Variant
With Worksheets(1).Columns(2).Cells(4)
vIn = .Resize(.End(xlDown).Row).Value
End With
ReDim vOut(1 To UBound(vIn), 1 To 2)
For k = 1 To UBound(vIn)
s = vIn(k, 1)
s = Replace(Replace(Replace(s, "IP Address:", "~~"), "IP ADDRESS:", "~~"), "IP:", "~~")
s = Replace(Replace(s, "Hostname:", "~~"), " ", vbNullString)
v = Split(s, vbLf)
For i = 0 To UBound(v)
If Left$(v(i), 2) = "~~" Then
'Stop
If Left$(v(i + 1), 2) = "~~" Then
sIP = sIP & "," & Mid$(v(i), 3)
sHost = sHost & "," & Mid$(v(i + 1), 3)
i = i + 1
End If
End If
Next
vOut(k, 1) = Mid$(sIP, 2)
vOut(k, 2) = Mid$(sHost, 2)
Next
Worksheets(2).Range("a1").Resize(UBound(vOut), 2) = vOut
End Sub