On Thu, 15 May 2008 00:17:09 -0400, Ron Rosenfeld
wrote:

On Wed, 14 May 2008 08:07:02 -0700, mcmilja
wrote:

Hello,

Is there an excel funciton I can use to subtract a digit and add a digit for
an IP address format? For example: I have a known IP address of
172.24.137.149 in cell B2 and I would like to create a funtion in cell A2 to
subtract 1 digit which would result in 172.24.137.148 and another function in
cell C2 to add 1 digit which would result in 172.24.137.150.

Thanks,
Jaret

You could try this UDF which will add (or subtract if you enter a negative
number for the second argument) any value from your IP address:

It can probably be simplified but it's late.

To use this, enter the function =AddIP(IP, Add) in some cell where IP is, or
refers to a cell reference containing, an IP address; and Add is the amount you
want to add or subtract.

A2: =AddIP(B2,-1)
C2: =AddIP(B2,1)

To enter this, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.


This routine is a little simpler, and also checks that your octets are not
greater than 255:

===================================
Option Explicit
Function AddIP(IP As String, Add As Integer) As Variant
Dim aIP As Variant
Dim dWord As Double, dTemp As Double
Dim i As Long

aIP = Split(IP, ".")
'convert to dWord and Add
For i = 0 To 3
If aIP(i) 255 Then GoTo ErrExit
dWord = dWord + aIP(i) * 256 ^ (3 - i)
Next i
dWord = dWord + Add

If dWord = 256 ^ 4 Then GoTo ErrExit

For i = 0 To 3
If i 0 Then
dWord = dWord - aIP(i - 1) * 256 ^ (4 - i)
End If
aIP(i) = Int(dWord / 256 ^ (3 - i))
Next i

AddIP = Join(aIP, ".")
Exit Function
ErrExit: AddIP = CVErr(xlErrNum)
End Function
=============================
--ron