Rajiv Bhardwaj - Official Blog

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


Categories: Excel IP Functions

IP_Mod: IP Modification Function » « GTP Tunnel in CDMA


  1. Overall Excellent Add-in for managing IPv4 addresses. It does pretty much all the operations that i need to do with IPV4 on day to task. its very handy since i have to deal lots of excel sheets with all IP addresses.

    One major issue that you need to be aware of if you are trying to use this IP add-in is, when you send your Excel file with all the formulas using this Add-in, all the people that need to view this data also need to download add-in and install it.

    and it does not support IPv6

  2. Hi Rajiv,

    It helped me a lot.
    Is there any other Add-in for IPv6 as well.


    • admin

      2018/02/14 — 15:27

      Hi Sudhakr,

      Sorry, I have not prepared the IPv6 version. Also, as i am not getting time to update the macro so i am not sure if i will do that in short term.
      But i have open sourced this Add-in on GitHub so somebody else might help in updating it.


Leave a Reply

Your email address will not be published.

Copyright © 2018 Rajiv Bhardwaj – Official Blog

Theme by Anders NorenUp ↑