How do create a formula to add IP addresses?

J

jeffreyj

I am trying to make a formula that adds the last part of an IP address, such
that when I type in one IP address suchas 147.202.70.132, they automatically
fill in below sequentially with 133, 134, 135, etc. Can anyone help me with
this? Thanks.
 
C

Chip Pearson

I'd use a VBA function.

Function NextIPAddress(IP As String) As String
Dim Arr As Variant
Arr = Split(IP, ".")
Arr(UBound(Arr)) = Arr(UBound(Arr)) + 1
NextIPAddress = Join(Arr, ".")
End Function

You can then call this from a worksheet cell with code like
=NextIPAddress(A1)

You be using Excel 2000 or later in order to use the Split and
Join functions.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

Bob Phillips

Just drag and copy them down, Excel does it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
J

joeu2004

jeffreyj said:
I am trying to make a formula that adds the last part of an
IP address, such that when I type in one IP address suchas
147.202.70.132, they automatically fill in below sequentially
with 133, 134, 135, etc.
Click to expand...

If A1 contains the initial IP address, A2 can be:

=REPLACE(A1, FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,
LEN(A1),
MIN(255,
MID(A1, FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1, LEN(A1)) + 1))

When you copy the formula down the column, Excel will update
the relative cell references appropriately.

Obviously it would be better to put the FIND() result into a helper
cell to avoid evaluating it twice.
 
J

joeu2004

Bob Phillips said:
Just drag and copy them down, Excel does it.
Click to expand...

I shoulda remembered that myself. But on second thought, I
think Chip's suggestion of using a macro is the best idea because
it can be extended to cover the more general case of "incrementing
an IP address". The OP assumes that requires only incrementing
the last octet, which is the case for Class C IP addresses. But in
the case of a Class A, B and D IP addresses, incrementing 255
may carry over to the next octet. For example, the next address
after 1.2.3.255 is 1.2.4.0.
 
J

jeffreyj

I know Excel does this. I need a formula so that it updates automatically.
I want to set a cell equal to an IP address in a different worksheet A. And
when I update the IP address in worksheet A, it updates it in B and fills in
the IP addresses below, automatically
 
J

jeffreyj

Chip,

Say I wanted to add onto this function, and instead of just giving the next
IP address, I want to check to see if the next IP address is divisible by 4.
If it is divisible by 4 then put it in the cell, if not, put the next
available address that is divisible by 4. How would I do this? thanks.
 
C

Chip Pearson

Try

Function NextIPAddress(IP As String) As String
Dim Arr As Variant
Dim V As Integer
Arr = Split(IP, ".")
V = CInt(Arr(UBound(Arr)))
V = V + 1
If V Mod 4 = 0 Then
Arr(UBound(Arr)) = V
Else
V = V + 4 - (V Mod 4)
Arr(UBound(Arr)) = CStr(V)
End If
NextIPAddress = Join(Arr, ".")
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top