Custom Formula: IPFromDEC (IP address from decimal)

THIS CONTENT IS NOW HOSTED ON GITHUB: https://github.com/sweenig/ipfromdec/




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
1	192.168.1.0	24
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.