I was working on a project the other day where I had a list of ~ 500 /30 subnets in Excel that I needed to break out so each host in the /30 was in it’s own column.  Seemed pretty straight forward, but there was no built in function to handle this.  With all of the other math functions Excel has built in, you would think that in 2014 some functions that deal with IP addresses would be standard.  Guess not.  Rather than spend the time to write one from scratch, a quick Google search came up with an entire set of functions written by Rajeev Bhardwaj. He put together an excellent presentation that shows the usage of each of the functions located here:   http://www.slideshare.net/rajivss/ip-functions-presentationgen-v11 .  This not only helped me solve my current problem but all of the other tools included in the Add-In will definitely get used in the future.

Here’s a quick summary of the included tools:

• IP_Bits2Mask – Converts the number of bits from a / notation to an expanded subnet mask
• IP_ErrChk – Checks to see if a value is a valid IP address or not
• IP_Hosts – Calculates the number of hosts from a specific subnet mask
• IP_IP2Mbits – Calculates the subnet mask required to obtain a certain number of host addresses
• IP_Mod – Takes any octet of the IP address and increments/decrements it by the set value
• IP_NextSub – Calculates the next subnet
• IP_Bcast – Calculates the broadcast address for a given IP
• IP_Count – Counts the occurrence of an IP in a range of subnets
• IP_IsExist – Check if an IP address exists in a subnet
• IP_Mask2Bits – Calculates the mask bits from a subnet mask in dotted notation
• IP_Subnet – Calculates the subnet address for a given host IP

I would recommend you visit Rajeev’s site and download the Excel add-in to be able to take advantage of all these functions.  You can visit his site to download the tools here: http://rajivbhardwaj.com/download/