View Single Post
Old 10-17-2016, 04:08 PM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 1,099
it showed was [CRLF] after the text
Are you sure there weren't also some ""s there? If there's a CRLF at the end, excel typically wraps the entire thing in ""s and " is not valid as part of an IP address.

In a regular expression, a '^' (caret) character in a VBScript regular expression indicates the beginning of the input string text. If the Multiline property is set to true on the regular expression object (e.g. re.Multiline = True), then '^' also matches the position following CR and/or LF.

A '$' (dollar) character indicates the end of the line of the end of input text, and if Multiline is True, will also match the position preceding a CR and/or LF. When you removed the $ from the end of the line, you're allowing for the extra stuff at the end of the input.

You could use the regular expression to "extract" the valid portion representing the address. For example, try running this script independently from your other one, after copying a cell to the clipboard:
' PromptForAndValidateIPAddress.vbs

strAddress = crt.Clipboard.Text

' Use a regexp pattern to parse out only valid IP address from clipboard.
Set re = New RegExp
' Set the pattern to match only an IPv4 address
re.Pattern = _
     "((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.){3}" & _

Sub Main()
    ' This loop allows us to continue prompting for valid
    ' input until either the user cancels, or provides a
    ' submission that has a valid IP address (somewhere).
        If re.Test(strAddress) Then
            ' Get the first matching value
            Set objMatch = re.Execute(strAddress)(0)
            strAddress = objMatch.Value
            Exit Do
            strAddress = crt.Dialog.Prompt(_
                "Please specify a valid IPv4 address", _
                "Enter IP Address", _
            If strAddress = "" Then
                Exit Sub
            End If
        End If

    MsgBox "This the address we extracted: [" & strAddress & "]"
End Sub
In other words, without the ^ and the $, you're saying "anywhere" within the text, find something that matches this pattern, and when you use objMatch.Value, you're actually getting only that portion of the input string that matched the pattern you gave.

The script will continue to prompt until it is able to extract a valid IP address from the input, somewhere, anywhere in the input. So, if all you're trying to accomplish is to get at the valid IP address that's somewhere within the cell's value, this approach might do the trick.

Jake Devenport
VanDyke Software
Technical Support
YouTube Channel:
Reply With Quote