how to extract only specific strings in VBA

Question

I have already asked a question at Extract strings from one text cell in VBA. First of all, I apologize for raising a challenging question that may take more of your time.

Basically it's a macro which extracts hostnames and IP addresses from one cell, and I have to extract from many such cells in one column. @Nicolas has already helped me write a very good script. However, due to the variation of my input data, it's very unpredictable how the IP address and hostname will be formatted. As a side note, that code could be found under the answer from @Nicolas.

Now I have the error message of:

Subscript out of range

at the line tempIps = Trim(Split(lineList(line), ":")(1)) since sometimes the input data looks like as follows, which have the keyword but don't have the real hostnames and ip addresses I need.

Hostname Rack Key IP Address Application Name

Sometimes at For Index = 1 To UBound(hostList) since there's no hostname or ip address as a keyword being found.

Here's a few examples for your reference, I could provide more if required:

Example 1:

Please refer long description 22:00 to 01:00 AM

MW SA to draw password for the following servers.

Hostname : a01gibapp1a IP Address : 10.89.96.21 Privileged ID : root  

Hostname : a01gibweb1a IP Address : 10.89.75.23 Privileged ID : root  

01:00 to 03:00 AM

MW SA to run the script implementation.sh which is under /staginapp/jul2015/20jul15/ in all the IB app Servers.
MW SA to run the script implementation.sh which is under /tmp/CH64698/ in all the web servers.
MW SA to restart the WebSphere instances one by one in all the IB App servers given above.
MW SA to restart the IB IHS instances if required.

03:00 to 04:00
AMS technical verification
BU Live Verification.

Example 2:

Please refer to long description & attachment Implementation steps:
=====================
Schedule time: 20th July 10PM to 21st July 3AM

Allow MW SA to draw the root password for below servers.
a01gibpns1a 10.89.71.26
a01gibpns2a 10.89.71.27
a01gibpns3a 10.89.71.34
a01gibpns4a 10.89.71.33

a01ribpns1a 10.89.35.83

Here's the whole part of my code:

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
Dim X As Integer, wslasteow As Integer

With Sheets(1)

wSlastRow = .Rows(.Range("W:W").Rows.Count).End(xlUp).Row

For X = 4 To wSlastRow

hostIndex = 1
ipIndex = 1

'Get address string from cell
addressStream = .Range("W" & X)

'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
     If InStr(lineList(line), "IP Address") Or InStr(lineList(line), "IP ADDRESS") Or InStr(lineList(line), "IP Address ") 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:") Or InStr(lineList(line), "HostName:") Or 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
Sheets(2).Range("A" & (X)).Value = result

Next X
End With

End Sub

Use Regex to extract ip's. msdn.microsoft.com/en-us/library/… — Maciej Los
– Maciej Los, Commented Jul 27, 2015 at 9:37
Can you please edit your question to include the actual code you are using? This improves the question since it can stand on its own. It also simplifies answering for folks since they don't have to guess how you'v implemented the code in the other question/answers. — Byron Wall
– Byron Wall, Commented Jul 27, 2015 at 23:41
@ByronWall Since the code is quite long, I scared that would take up a lot content:) But as you requested, I will include the code then:) — excel
– excel, Commented Jul 28, 2015 at 3:18
The goal is to have the minimum needed to reproduce your problem. If we don't have your code then we are below that minimum. Ideally you include the relevant part of the code only. — Byron Wall
– Byron Wall, Commented Jul 28, 2015 at 3:33
@ByronWall Thanks for your sincere advice, I have updated the question with my code, help it is of any help:) — excel
– excel, Commented Jul 28, 2015 at 4:02

luke_t · Accepted Answer · 2015-07-27 09:31:15Z

1

Can you not just check the string, to see if a character which you're attempting to split the string by is actually within the string before you split it?

If InStr(lineList(Line), ":") Then
    tempIps = Trim(Split(lineList(Line), ":")(1))
End If

This will prevent the code from trying to split the string if there's not any colons to split.

Let me know if I've interpreted what you're after incorrectly.

answered Jul 27, 2015 at 9:31

luke_t

2,9874 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Byron Wall Over a year ago

Seems like the best way to address the actual error OP is getting. It is also possible to store the array returned by Split and verify that UBound() is at least 1 before trying to pull the result. Split does not fail when the delimiter is missing, it just returns the whole string in the 0-spot.

luke_t Over a year ago

Thanks for pointing that out, @Byron! I'll take it into consideration in the future.

Bond · Accepted Answer · 2015-07-27 14:11:33Z

1

To extract an IP address from a string, use a regex. Add a reference to the Microsoft VBScript Regular Expressions 5.5 library and use a RegExp object to test your string against a pattern. For example, to extract an IP address from cell A1:

Dim re As New RegExp
re.Pattern = "(?:\d{1,3}\.){3}\d{1,3}"

If re.Test(Range("A1")) Then

    ' [A1] contains an IP address. Extract it...
    Debug.Print "IP address found = " & re.Execute(Range("A1"))(0)

End If

answered Jul 27, 2015 at 14:11

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Collectives™ on Stack Overflow

how to extract only specific strings in VBA

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related