Rajiv Bhardwaj - Official Blog

Tag: Microsoft office (page 2 of 2)

IP_Mod: IP Modification Function

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, 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(“”,1) will give


As i have set the value of Step to be 1 by default so RB_IP_Mod(“”) 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) will give you

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, 3) will give you

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,3) will give you 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(, -5) will give

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(, 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(, 245) will give “#NUM” error as resultant IP 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.

Excel based IP Functios: RBIPFx

After I got tired with manually modifying IP address in excel, I decided to give a try to creating some kind of formula.

I got some success with inbuilt “left” & “right” functions. e.g. by using something like this to increment the IP address at position A1.



But soon it became quite cumbersome because of 3 reasons.

1. Every time I wanted to use this formula I had to change the reference cell ID “A1” manually. It defeated the original purpose of automation.

2.  It didn’t work well for changing the Class A or B IP Subnets. I mean updating any of the first three “parts” of IP address.

3. As I started working on some IP hungry nodes.  Handling of huge number of IPs became almost impossible by this way.

Don’t have much experience with macros & all but still was able to create a set working function.

I have complied all the IP address related functions in Add-in format which can be downloaded from this page. Although, .xla format is not fully portable as excel sheets created by one user cannot be used by another user without updating the links. But it’s the easiest way to transfer reusable functions.

This Add-in RBIPFx (Rajiv Bhardwaj’s IP functions) include following functions in present version (0.1).

1. RB_IP_Bits2Mask

2. RB_IP_ErrChk

3. RB_IP_Mod: Click here for details.

4. RB_IP_NextSub

In the recent version i have added some new functions and also removed the ‘RB_” prefi:

1. IP_Bits2Mask
2. IP_ErrChk
3. IP_Hosts
4. IP_IP2Mbits
5. IP_Mod
6. IP_NextSub
7. IP_Bcast
8. IP_Count
9. IP_IsExist
10. IP_Mask2Bits
11. IP_Subnet

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

Installing Excel Addins

Also please see the presentation at following link



Copyright © 2017 Rajiv Bhardwaj – Official Blog

Theme by Anders NorenUp ↑