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)
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.
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.
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.
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.
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.
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.
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.