Funciton to subtract and add a digit to an IP address format?

M

mcmilja

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
 
G

Gary''s Student

Use the following UDFs:

Function upOne(r As Range) As String
s = Split(r.Value, ".")
s(UBound(s)) = s(UBound(s)) + 1
upOne = Join(s, ".")
End Function


Function downOne(r As Range) As String
s = Split(r.Value, ".")
s(UBound(s)) = s(UBound(s)) - 1
downOne = Join(s, ".")
End Function
 
R

Ragdyer

I don't know squat about coding Gary, but I don't believe, by looking at
your UDF, that you took into consideration that an octet can *only* go to
255.

So,
172.15.255.255
plus 1, would be
172.16.0.0

Subtraction would also encompass similar multi-faceted calculations.
 
R

Ron Rosenfeld

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
 
R

Ron Rosenfeld

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
 
M

mcmilja

Hello Ron,

I followed your procedure below but it did not work for me. Any thoughts?

Thanks,
Jaret
 
R

Ron Rosenfeld

Hello Ron,

I followed your procedure below but it did not work for me. Any thoughts?

I have plenty of thoughts :)

What does "did not work" mean? And what did you do?

If *I* follow the procedure, it works fine.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top