I have a spreadsheet that contains IP addresses for computers on a network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
--
Thank You

On Fri, 11 Sep 2009 09:40:03 -0700, HarryisTrying
wrote:

I have a spreadsheet that contains IP addresses for computers on a network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007

Are the IP addresses the only contents of the cell, or is there more than just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron

Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?

Function IsIPAddress(strIP As String) As Boolean

'4 numbers from 0 to 255 separated by dots?
'------------------------------------------
Dim i As Long
Dim n As Long
Dim arrSplit
Dim arrByte() As Byte

On Error GoTo ERROROUT

arrSplit = Split(strIP, ".")

If UBound(arrSplit) < 3 Then
Exit Function
End If

For i = 0 To 3

If Len(arrSplit(i)) 3 Then
Exit Function
End If

If CLng(arrSplit(i)) 255 Then
Exit Function
End If

arrByte() = arrSplit(i)
For n = 0 To UBound(arrByte) - 1 Step 2
If arrByte(n) < 48 Or arrByte(n) 58 Then
Exit Function
End If
Next n

Next i

IsIPAddress = True

ERROROUT:

End Function



RBS


"Ron Rosenfeld" wrote in message
...
On Fri, 11 Sep 2009 09:40:03 -0700, HarryisTrying
wrote:

I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007

Are the IP addresses the only contents of the cell, or is there more than
just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a
valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron

Thank you so much this works great.
--
Thank You


"RB Smissaert" wrote:

Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?

Function IsIPAddress(strIP As String) As Boolean

'4 numbers from 0 to 255 separated by dots?
'------------------------------------------
Dim i As Long
Dim n As Long
Dim arrSplit
Dim arrByte() As Byte

On Error GoTo ERROROUT

arrSplit = Split(strIP, ".")

If UBound(arrSplit) < 3 Then
Exit Function
End If

For i = 0 To 3

If Len(arrSplit(i)) 3 Then
Exit Function
End If

If CLng(arrSplit(i)) 255 Then
Exit Function
End If

arrByte() = arrSplit(i)
For n = 0 To UBound(arrByte) - 1 Step 2
If arrByte(n) < 48 Or arrByte(n) 58 Then
Exit Function
End If
Next n

Next i

IsIPAddress = True

ERROROUT:

End Function



RBS


"Ron Rosenfeld" wrote in message
...
On Fri, 11 Sep 2009 09:40:03 -0700, HarryisTrying
wrote:

I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007

Are the IP addresses the only contents of the cell, or is there more than
just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a
valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron

On Fri, 11 Sep 2009 21:09:27 +0100, "RB Smissaert"
wrote:

Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?

It'd probably be faster. Mine was pretty quick to code since I have the
Regular Expression for IP addresses in my library. Also, except for my =isIP()
function, the others do not reject constructs with more than four octets.
--ron

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
--
Thank You


"Ron Rosenfeld" wrote:

On Fri, 11 Sep 2009 09:40:03 -0700, HarryisTrying
wrote:

I have a spreadsheet that contains IP addresses for computers on a network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007

Are the IP addresses the only contents of the cell, or is there more than just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron

On Fri, 11 Sep 2009 17:14:02 -0700, HarryisTrying
wrote:

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
--
Thank You

That is because you used the =containsIP function. And a string of 5 octets
does, indeed, contain 4 octets.

If you use the =isIP function, it would return false.

It would be helpful to know the nature of the data, as I mentioned in my
response, in order to fabricate an appropriate regular expression
--ron

On Fri, 11 Sep 2009 17:14:02 -0700, HarryisTrying
wrote:

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
--
Thank You

One further note: the submissions by Rick and RBS will also not differentiate
between four and five octets.

My =isIP() does, but please post more data on the precise contents of the
cells, for further refinement.
--ron

One further note: the submissions by Rick and RBS will also not
differentiate between four and five octets.

Damn! I missed that. Here are both functions repaired to handle that
problem...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 Then
For X = 0 To 3
If Split(IP, ".")(X) 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Fri, 11 Sep 2009 17:14:02 -0700, HarryisTrying
wrote:

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
--
Thank You

One further note: the submissions by Rick and RBS will also not
differentiate
between four and five octets.

My =isIP() does, but please post more data on the precise contents of the
cells, for further refinement.
--ron

One further note: the submissions by Rick and RBS will also not
differentiate
between four and five octets.

My function should handle that fine and can't see anything wrong with it,
plus I think
it will be the fastest of all the posted code.

RBS

"Ron Rosenfeld" wrote in message
...
On Fri, 11 Sep 2009 17:14:02 -0700, HarryisTrying
wrote:

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
--
Thank You

One further note: the submissions by Rick and RBS will also not
differentiate
between four and five octets.

My =isIP() does, but please post more data on the precise contents of the
cells, for further refinement.
--ron

On Sat, 12 Sep 2009 09:11:31 +0100, "RB Smissaert"
wrote:

My function should handle that fine and can't see anything wrong with it,
plus

You're correct. It does handle that. I don't know why I was getting the
results I thought I did last night.

I think
it will be the fastest of all the posted code.

Regular expression solutions will generally be the slowest. And mine is.
Depending on the speed of the machine, and the number of calculations, it could
certainly make a difference.

In the speed measurements I did on my machine, using the hi resolution timer,
it seems that Rick's first (modified) submission is the fastest, though.

Of course, we still don't know if the data to be checked are cells that consist
solely of an IP address, or cells that merely contain an IP address along with
other information.
--ron

On Fri, 11 Sep 2009 17:14:02 -0700, HarryisTrying
wrote:

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
--
Thank You

Here's another approach that will reject

1.1.1.1.1

but accept

1.1.1.1.1 1.1.1.1

since the latter "contains" a valid IP address that is separated from the
invalid construct:

===============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"(?:^|\s)\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b(?:\s|$)"

containsIP = re.Test(str)
Set re = Nothing
End Function
===========================
--ron

This function should do what you want...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" Then
For X = 0 To 3
If Split(IP, ".")(X) 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

--
Rick (MVP - Excel)


"HarryisTrying" wrote in message
...
I have a spreadsheet that contains IP addresses for computers on a network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
--
Thank You

I guess this somewhat longish non-looping one-liner will also work as
well...

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
This function should do what you want...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" Then
For X = 0 To 3
If Split(IP, ".")(X) 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

--
Rick (MVP - Excel)


"HarryisTrying" wrote in message
...
I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets
seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
--
Thank You

Here are both of my functions, repaired to handle the problem Ron pointed
out about them...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 Then
For X = 0 To 3
If Split(IP, ".")(X) 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I guess this somewhat longish non-looping one-liner will also work as
well...

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
This function should do what you want...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" Then
For X = 0 To 3
If Split(IP, ".")(X) 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

--
Rick (MVP - Excel)


"HarryisTrying" wrote in
message ...
I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets
seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
--
Thank You

On Fri, 11 Sep 2009 22:55:47 -0400, "Rick Rothstein"
wrote:

Here are both of my functions, repaired to handle the problem Ron pointed
out about them...

Not wanting to take unwarranted credit, it was the OP who first pointed out the
problem.
--ron

Here are both of my functions, repaired to handle the problem Ron pointed
out about them...

Not wanting to take unwarranted credit, it was the OP who first pointed out the
problem.

Well, that is true in so far as the OP pointed out that your function contained that defect; however, you were the one that pointed out that both my and RBS's functions also contained that defect. So... credit still goes to you.<g

--
Rick (MVP - Excel)