IP_IsValid()
=IP_IsValid( ) 
How to check if a cell contains a valid, formatted IP Address in Microsoft Excel
Supports IPv4 and IPv6 addresses
Input value is tested and validated as an IP Address. Format validation is performed. Test IPv4 and IPv6 addresses. Returns TRUE/FALSE
Arguments
| IPAddress | Required |
Input value to test as an IP Address. Supports IPv4 or IPv6 addresses
| ReturnType | Optional - default is 0 |
0 EitherIPv4orIPv6 = TRUE/FALSE
If IPAddress passes validation, formatted as dotted quad notation for IPv4 or a text representation as specified in RFC 5952 for IPv6, then TRUE is returned else the return is FALSE
Reference: RFC 5952 http://tools.ietf.org/html/rfc5952
1 IPv4Only = TRUE/FALSE
If IPAddress passes validation, formatted as dotted quad notation for IPv4, then TRUE is returned else the return is FALSE
2 IPv6Only = TRUE/FALSE
If IPAddress passes validation, formatted as a text representation as specified in RFC 5952 for IPv6, then TRUE is returned else the return is FALSE
Reference: RFC 5952 http://tools.ietf.org/html/rfc5952
Usage Examples
=IP_IsValid("152.134.5.23")
Tests IPAddress “152.134.5.23” and returns TRUE because IPAddress is a valid IPv4 address
=IP_IsValid("152.134.5.23", "IPv6Only")
Tests IPAddress “152.134.5.23” and returns FALSE because IPAddress is not a valid IPv6 address
=IP_IsValid(A27, 2)
Cell A27 contains text "::ffff:192.0.2.128". Tests IPAddress “::ffff:192.0.2.128” and returns TRUE because IPAddress is a valid IPv6 address
=IP_IsValid(A1)
Cell A1 contains text "LastName". Tests IPAddress from cell A1 “LastName” and returns FALSE because IPAddress is not valid IPv4 address
=IP_IsValid(A15)
Cell A15 contains nothing. Tests IPAddress from cell A15 and returns #ipte_ARG1-ERROR because IPAddress is not supplied
=IP_IsValid(A15)
Cell A15 contains nothing. Change the formula to =IF(ISBLANK(A15),"Nothing to test", IP_IsValid(A15))
Notes
Any argument can be a cell reference like A7 or $D$3
Arguments can be entered as “string” or number value