Rajiv Bhardwaj - Official Blog

Tag: right

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

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

Copyright © 2017 Rajiv Bhardwaj – Official Blog

Theme by Anders NorenUp ↑