Extract strings from one text cell in VBA

Question

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:

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:

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.

How exactly does the text appear in the cell? Are there line breaks? — Alex P
– Alex P, Commented Jul 20, 2015 at 8:15
@AlexP Yes, it looks exactly as the screenshot I uploaded in the question, there are many line breaks. — excel
– excel, Commented Jul 20, 2015 at 8:27
Say clearly, Is this all text are in one cell or many cell? Not clear..! — R.Katnaan
– R.Katnaan, Commented Jul 20, 2015 at 8:29
@Nicolas Hi I have just edited the question, sorry for not being clea, all text are in one cell. — excel
– excel, Commented Jul 20, 2015 at 8:37
In your sample data, I found three name for ip "IP ADDRESS", "IP Address" and "IP". I think, you should centralized them to "IP Address". Not only that but also some host name and ip address has no title as w01gdnv1a or w01ggdwtd1a or w01ggdwtd10a 10.89.140.92(Informatica Client). Firstly, you should modify it, if can. — R.Katnaan
– R.Katnaan, Commented Jul 21, 2015 at 10:08

R.Katnaan · Accepted Answer · 2015-07-23 05:07:32Z

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 Address:
Hostname:
................................
---------------------------
IP ADDRESS:
Hostname:
................................
---------------------------
IP:
Hostname:
................................
---------------------------
IP Address:
................................
---------------------------
IP Address:
Hostname:

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:

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.

Hi thanks for your help, but when I run this code, I get error Subscript out or range at result = result & hostList(Index) & vbTab & ipList(Index) & vbNewLine, the macro is not able to find IP address
Actually, you gave wrong information to us. But, don't worry, I got you. I modified my code. Try with it. Take care in posting question for next time.
Thank you so much for modifying your answer, but it still gives me the same error, the problem goes with ipList(Index)
Because of incomplete pair of host name and ip address. I means, In your input text, count of host names is greater than count of ip address. So, It give error. My code can work perfectly only if count of host names is equal with count of ip address. Check your input data.
Ok, remove your sample data photo and post your input data as text. So, we can think more and more.

99moorem · Accepted Answer · 2015-07-20 08:45:06Z

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"

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

answered Jul 20, 2015 at 8:45

99moorem

1,9931 gold badge16 silver badges28 bronze badges

Comments

Excel Hero · Accepted Answer · 2015-07-24 14:58:37Z

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.

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

edited Jul 24, 2015 at 14:58

answered Jul 23, 2015 at 9:53

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

6 Comments

excel Over a year ago

Hi I'm testing it, thanks for your help, will get back to you soon^^

excel Over a year ago

Hi actually all the data are in column 2 starting from row 4 until the last row with data, and I need to then paste the results to the cells in the second worksheet like this: Sheets(2).Range("A1").Value = result

Excel Hero Over a year ago

For output do you want the hostnames and IPs to disply in separate columns?

Excel Hero Over a year ago

I have revised the solution based on your needs. Please see Update #1.

excel Over a year ago

Sorry for the late reply, I changed Columns number to 23 to fit my code, the result looks like a bit weird. All IP addresses are in column are in column A and Hostnames are in column B, However, there are 27 rows of same IP addresses and hostnames from the same CR no at cell 5.

|

Collectives™ on Stack Overflow

Extract strings from one text cell in VBA

3 Answers 3

7 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related