Tuesday, March 17, 2015

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.
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:


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:


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 335.20.30.40
IP2DEC Converts from dotted decimal to decimal 3232239394 169090600 Invalid IP Address 2383683112
IPFROMDEC Converts from decimal to dotted decimal #VALUE!
IPNetwork Returns the network number given an IP address and mask 24 Invalid IP Address
IPIsInSubnet Determines if the given IP address is within the given subnet 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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
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):

While this is nice, it would be even nicer if i could just do something like this:

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
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 block of IP addresses, I'd have the following:

 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.


  1. Thanks for the IPConversion ADD-IN. I've been using it for years in Excel 2013 on Windows 10. I recently built another PC with the same setup, but the ADD-IN only works the first time I add it into Excel - when I say it only works the first time, I mean that if I close Excel, reopen another spreadsheet, the IP conversion functions are no longer available even though the ADD-IN is still enabled in the options section of Excel. I can get it to work again by disabling it in Excels "manage add-ins," closing Excel and then re-enabling again. The add-in doesn't seem persistent after closing Excel.

  2. I would say it's probably that the add-in is getting added to the currently open worksheet instead of to the template worksheet or to excel itself. Try making sure when you add it that, the add-in gets added to normal.xlst.