View Single Post
  #8  
Old 10-17-2016, 05:08 PM
jdev's Avatar
jdev jdev is offline
VanDyke Technical Support
 
Join Date: Nov 2003
Location: Albuquerque, NM
Posts: 941
Quote:
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:
Code:
' 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}" & _
     "(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])"

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).
    Do
        If re.Test(strAddress) Then
            ' Get the first matching value
            Set objMatch = re.Execute(strAddress)(0)
            strAddress = objMatch.Value
            Exit Do
        Else
            strAddress = crt.Dialog.Prompt(_
                "Please specify a valid IPv4 address", _
                "Enter IP Address", _
                strAddress)
            If strAddress = "" Then
                Exit Sub
            End If
        End If
    Loop

    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
__________________
Jake Devenport
VanDyke Software
Technical Support
YouTube Channel: https://www.youtube.com/vandykesoftware
Email: support@vandyke.com
Web: https://www.vandyke.com/support
Reply With Quote