IP_Mod:  This is the primary function of RBIPFx Excel Add-in. It modifies the IP addresses.

Syntax: RB_Mod_IP( IP address, optional Step, Optional Part)

Parameters:

IP Address: As the name suggest is IPV4 IP address which you want to modify.

Step: It is the number by which you want to increment or decrement the given IP address. It is optional and default value of this parameter is 1.

Part: It is the part of IP address you want to modify. As IPv4 address as 4 parts separated by “.”.

i.e For IP 10.11.12.13, Part 1 =10, part 2=11, Part3 =12 & Part4 =13.

It is also optional parameter & default value of this part is 4. i.e. last part (Class C subnet)

USAGE: Following are some of the ways, this function can be used.

i)  You can increment the IP by one just by using the IP address parameter. i.e.

RB_IP_Mod(“10.11.12.13”,1) will give 10.11.12.14.

RB_IP_Mod

As i have set the value of Step to be 1 by default so RB_IP_Mod(“10.11.12.13”) will also do.

Dragging this function in excel column will give you a series of IPs.

ii)  But if you want to increment the IP with bigger value then you need to use the parameter STEP.

e.g

RB_IP_Mod(“10.11.12.13”, 10) will give you 10.11.12.23.

iii) If you want to create a new IP subnet then you may want to increment the 3rd part of the IP directly. You can do that by using the Part parameter.

e.g

RB_IP_Mod(“10.11.12.13”, 10, 3) will give you 10.11.22.13

iv)  Many people use IP address with subnet maskbits (e.g. xx.xx.xx.xx/24) included. This function can handle that too. It automatically detects the subnet mask separator (“/”) and uses the same format for output.

e.g

RB_IP_Mod(“10.11.12.13/24”,10,3) will give you 10.11.22.13/24 as output.

Please note that it recognizes only “/” as separator.

v)  If you want to decrement an IP address instead of incrementing then you can do that too by just giving a negative Step Value.

e.g RB_IP_Mod(10.11.12.13, -5) will give 10.11.12.8

Error Handling: It can handle basic error situations.

if you input a step value more than 255 or less than -255. Function will return an error.

e.g.

RB_IP_Mod(10.11.12.13, 258) will give “#NUM” as return. This error is inline with basic excel errors as it signifies that wrong value of number is being used.

I initially had pop-up message for such situations but it becomes quite irritating if we have a large sheet full of IP addresses.

One more situation which I have captured is that sometimes input is well within range but the resultant IP is out of range. It also generates error in such situation.

e.g.  RB_IP_Mod(10.11.12.13, 245) will give “#NUM” error as resultant IP 10.11.12.258 is not the valid one. For me this error functionality is a real life saver as I tend to forget this “255” limit.

For procedure to install the add-in check this post.

Installing Excel Addins

To download the  add-in, go to download page.

Please note that initially this function was named RB_IP_Mod but in the latest version is named IP_Mod.