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.
The routine does not check that your original IP address is what is normally
considered valid; nor does it convert them.
================
Option Explicit
Function AddIP(IP As String, Add As Integer) As Variant
Dim aIP As Variant
Dim dWord As Double
Dim iIP As Variant
Dim i As Long
aIP = Split(IP, ".")
'convert to dWord and Add
For i = 0 To 3
dWord = dWord + aIP(i) * 256 ^ (3 - i)
Next i
dWord = dWord + Add
If dWord >= 256 ^ 4 Then
AddIP = CVErr(xlErrNum)
Exit Function
End If
aIP(0) = Int(dWord / 256 ^ 3)
aIP(1) = Int((dWord - aIP(0) * 256 ^ 3) / 256 ^ 2)
aIP(2) = Int((dWord - aIP(0) * 256 ^ 3 - aIP(1) * 256 ^ 2) / 256)
aIP(3) = dWord - aIP(0) * 256 ^ 3 - aIP(1) * 256 ^ 2 - aIP(2) * 256
AddIP = Join(aIP, ".")
End Function
============================
--ron