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.

LEFT(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1,1)+1)+1))&RIGHT(A1,LEN(A1)-FIND_

(“.”,A1,FIND(“.”,A1,FIND(“.”,A1,1)+1)+1))+1

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

Presentation

Categories: Excel IP Functions

IP_Mod: IP Modification Function » « GTP Tunnel in CDMA

1 Comment

  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

Leave a Reply

Your email address will not be published.

 

Copyright © 2017 Rajiv Bhardwaj – Official Blog

Theme by Anders NorenUp ↑