Tuesday, March 17, 2015

Custom Formula: IPFromDEC (IP address from decimal)

See part 1 of this thread.
See part 2 of this thread.
Download
Install Instructions

UPDATE: I cleaned up the code. It may be a little less intuitive, but the code itself is simpler. I added a new boolean checker: IPIsValidIP(), which returns true if the IP address is a valid dotted decimal IP address.


UPDATE: I added a new formula: IPGetCIDRList(). This list takes two IP addresses and defines all the address blocks between them, inclusively. For example:

IPGetCIDRList("10.0.0.0","10.0.0.5")="10.0.0.0/30,10.0.0.4/31"

This is handy if you want to get all the address blocks in a range. The more simple the range, the shorter the list of summarizations. The more complex the range, the longer the list. For example:

IPGetCIDRList("10.1.2.3","192.168.35.7")="10.1.2.3/32, 10.1.2.4/30, 10.1.2.8/29, 10.1.2.16/28, 10.1.2.32/27, 10.1.2.64/26, 10.1.2.128/25, 10.1.3.0/24, 10.1.4.0/22, 10.1.8.0/21, 10.1.16.0/20, 10.1.32.0/19, 10.1.64.0/18, 10.1.128.0/17, 10.2.0.0/15, 10.4.0.0/14, 10.8.0.0/13, 10.16.0.0/12, 10.32.0.0/11, 10.64.0.0/10, 10.128.0.0/9, 11.0.0.0/8, 12.0.0.0/6, 16.0.0.0/4, 32.0.0.0/3, 64.0.0.0/2, 128.0.0.0/2, 192.0.0.0/9, 192.128.0.0/11, 192.160.0.0/13, 192.168.0.0/19, 192.168.32.0/23, 192.168.34.0/24, 192.168.35.0/29"

Download using the same link as below. If you're upgrading, close Excel and copy the downloaded XLAM file to the same location as your existing IPConversion.xlam file. If you don't know where that is, look in your add ins list (File>>Options>>Add Ins, look in the location column).


UPDATE: I compiled and have now published my IPConversion.xlam.  How to install this add-in.
How to use:
Function Argument 1 Argument 2 192.168.15.34 10.20.30.40 335.20.30.40 142.20.30.40
IP2DEC Converts from dotted decimal to decimal 3232239394 169090600 Invalid IP Address 2383683112
IPFROMDEC Converts from decimal to dotted decimal 192.168.15.34 10.20.30.40 #VALUE! 142.20.30.40
IPNetwork Returns the network number given an IP address and mask 24 192.168.15.0 10.20.30.0 Invalid IP Address 142.20.30.0
IPIsInSubnet Determines if the given IP address is within the given subnet 192.168.0.0 16 TRUE FALSE FALSE FALSE
IPGetOctet Returns the octet specified (1-4) 3 15 30 Invalid IP Address 30
IPGetCIDRList Returns a comma separated list of CIDR address blocks between the two provided IP addresses 192.168.255.255 192.168.15.34/31, 192.168.15.36/30, 192.168.15.40/29, 192.168.15.48/28, 192.168.15.64/26, 192.168.15.128/25, 192.168.16.0/20, 192.168.32.0/19, 192.168.64.0/18, 192.168.128.0/17 10.20.30.40/29, 10.20.30.48/28, 10.20.30.64/26, 10.20.30.128/25, 10.20.31.0/24, 10.20.32.0/19, 10.20.64.0/18, 10.20.128.0/17, 10.21.0.0/16, 10.22.0.0/15, 10.24.0.0/13, 10.32.0.0/11, 10.64.0.0/10, 10.128.0.0/9, 11.0.0.0/8, 12.0.0.0/6, 16.0.0.0/4, 32.0.0.0/3, 64.0.0.0/2, 128.0.0.0/2, 192.0.0.0/9, 192.128.0.0/11, 192.160.0.0/13, 192.168.0.0/16 142.20.30.40/29, 142.20.30.48/28, 142.20.30.64/26, 142.20.30.128/25, 142.20.31.0/24, 142.20.32.0/19, 142.20.64.0/18, 142.20.128.0/17, 142.21.0.0/16, 142.22.0.0/15, 142.24.0.0/13, 142.32.0.0/11, 142.64.0.0/10, 142.128.0.0/9, 143.0.0.0/8, 144.0.0.0/4, 160.0.0.0/3, 192.0.0.0/9, 192.128.0.0/11, 192.160.0.0/13, 192.168.0.0/16
IPIsValidIP Returns true if the specified IP address is a valid IP address TRUE TRUE FALSE TRUE

In a previous post, I showed how to convert an IP address from dotted decimal notation to a decimal number. Well, i found myself in a situation where i needed to do the reverse.  So, there are two ways of doing this.

The first involves using a big formula to chop the decimal value into its equivalent dotted decimal counter parts. The formula goes like this (this formula references cell B2 where the decimal format of an IP address should be):
=ROUNDDOWN(B2/2^24,0)&"."&ROUNDDOWN(MOD(B2,2^24)/2^16,0)&"."&ROUNDDOWN(MOD(MOD(B2,2^24),2^16)/2^8,0)&"."&MOD(MOD(MOD(B2,2^24),2^16),2^8)

While this is nice, it would be even nicer if i could just do something like this:
=IPFromDEC(B2)

This is the second method.  If you've already created your IP2DEC.xlam file and have it enabled as an add in, you're ready to go, you can add a custom formula to break the IP address back out into the same add-in (if you haven't, click here to see how).

Open a blank workbook in Excel.  Press Alt+F11 or click 'Visual Basic' on the Developer tab in the ribbon bar.  If the project explorer isn't visible, show it by pressing Ctrl+R or by choosing View>>Project Explorer.  You should see two projects in there, one for the new blank workbook that opened and one for the IP2DEC add-in.  You should see Module1 under the IP2DEC add-in (if you don't see this, you didn't do the steps on the previous post).  Double click it.  You should now see the IP2DEC public function code.  Now all you need to do append some code to the bottom of the module that will define the function for converting back to dotted decimal format.
Public Function IPFROMDEC(ipaddress) As String
If ipaddress + 1 - 1 > 4294967295# Then GoTo toobig
Dim firstoctet As String, secondoctet As String, thirdoctet As String, fourthoctet As String
firstoctet = Int(ipaddress / (2 ^ 24))
secondoctet = ipaddress - (firstoctet * 2 ^ 24)
secondoctet = Int(secondoctet / (2 ^ 16))
thirdoctet = ipaddress - (firstoctet * 2 ^ 24) - (secondoctet * 2 ^ 16)
thirdoctet = Int(thirdoctet / (2 ^ 8))
fourthoctet = ipaddress - (firstoctet * 2 ^ 24) - (secondoctet * 2 ^ 16) - (thirdoctet * 2 ^ 8)
fourthoctet = Int(fourthoctet)
Select Case 255
Case Is < firstoctet
GoTo toobig
Case Is < secondoctet
GoTo toobig
Case Is < thirdoctet
GoTo toobig
Case Is < fourthoctet
GoTo toobig
End Select
IPFROMDEC = firstoctet & "." & secondoctet & "." & thirdoctet & "." & fourthoctet
Exit Function
toobig:
IPFROMDEC = "Invalid IP Address"
End Function
Hit the save button and go try it out.  Put an IP address in one cell and use IP2DEC() to convert it to decimal.  Then use IPFROMDEC() to convert it back.

You might think this an exercise in futility, however, this can come in handy when trying to parse out IP address blocks given CIDR notation.  For example, if you wanted to calculate the starting and ending IP address for the 192.168.1.0/24 block of IP addresses, I'd have the following:

A
B
1192.168.1.024
2=IPFROMDEC(IP2DEC(A1)+2^(32-B1)-1)192.168.1.255
 The result of the formula in A2 is shown in B2.  This can also be very handy when trying to determine whether or not a given IP address is within a given subnet.