View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default How do create a formula to add IP addresses?

"jeffreyj" wrote:
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.


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.