Geeky Excel Formulas – Subnet Math / Group By Subnet using Built-In Excel 2010 Formulas

As some of you may know,  I spend 3 hours a day on the train travelling from cow country in Regional Victoria to the big smoke Melbourne…what a perfect time to test the power of Excel formulas to the limits. Now some of you may think Excel is just for managers crunching financial numbers…actually although I do use Excel quite a bit, I’m maybe one of those rare people who almost never uses it for financial information (ok except budgeting for my next high-end PC build)

So to start with I have a list of some IP addresses and their subnet address such as below:



Now I want to group them by network address…to do this we must check the IP address against the subnet. So there are a few stages we’re going to have to go through:

1) Split up the numbers by the decimal point

2) Convert each section to binary

3) Join the sections into one big binary number

4) Take the 1s from IP Address when there is a 1 in the SUBNET , other wise get 0s

5) Split binary number into 4 parts of 8

6) Convert back to decimal

Seems a bit complicated for Excel, eh? But not at all, especially if you have 3 hrs on a train to kill. Because my memory is constantly being replaced with new technology I couldn’t think off the top of my head what formulas I’d use, so I started with the fx icon



So I used this to get the first part of the IP address (Small steps at a time)





To get the second value in the IP address the formula gets more complex.





and for the 3rd IP octect:



At this point I strongly recommend a double-shot espresso to prevent brain disintegration before continuing.

Now for the fourth and final octet:




Now that’s the easy part, right? Well now we have to convert the four components into binary…How can we do this…again we go to our “fx” lookup and type in “binary” and click GO. Well pretty sweet – we can see Dec2Bin, a function is already there for us!



So use CONCATENATE to combine all binary numbers into one big mother binary number:

OK i’ll admit my first attempt didn’t work


After some corrections it started working:


I did some test IP addresses to make sure it’s working


OK looks accurate…now for the final part we have to figure out how we do our subnet math…I dragged the formula across to the right to automatically get our subnets in binary




So we then look for the position of ‘first’ 0 in the binary subnet –> we then extract everything from the binary IP up to that first zero. Maybe it’s not fool proof if you have some really funky subnets that are not all 1s followed by all 0s, but if we did a more precise formula, the formula would be REALLY massive, making this one look tiny.




Now I’m quite happy to have the network address and leave it in binary, it looks pretty nice, and all the bits are really clear. But for those of you who grew up on decimal, here is what we do to get it back to a decimal address:



OK so now we’ve got our Network address in binary, as you can see in column J.



The final process is to combine all the formulas into one big formula…this is not going to work really well in excel’s formula editor, so I’m going to use notepad. I start by copying the formula in J2 and pasting it into Notepad. Then I’ll copy the formula from I2 and do a search replace on all instances of I2 to the formula in I2. This gets repeated down the line…i.e. replace H2 with formula in H2, replace G2 with formula in G2, etc.




But as my formula got bigger, notepad couldn’t handle the size of my search & replaces…so I went to my other editor…PSPad…

The formula is pretty massive, 7,720 characters long to be exact. But it WORKS!!!!


Here it is in all it’s glory…


So now we can also create a Pivot table based on subnet…We can do this by selecting all cells by hitting Ctrl+A then selecting Insert tab and selecting PivotTable > PivotTable


Then range will already be selected, then click OK



In the PivotTable Field List  we can drag Network Address into Values. We can sort by subnets with most PCs by clicking arrow on Row Labels and selecting More Sort Options > Descending (Z to A) by: Count of Network Address

In the PivotTable Field List we can now select what fields we want to be displayed, and they can be displayed in groups of subnets.



If you have any of your own “geeky” uses of Excel or a suggestion for a challenging Excel problem let me know Smile

The .xlsx file used in this sample can be downloaded from here

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in Excel, Network and tagged . Bookmark the permalink.

32 Responses to Geeky Excel Formulas – Subnet Math / Group By Subnet using Built-In Excel 2010 Formulas

  1. John McCaffery says:

    Wow, this is very interesting and amazing, I think!

    • Mike says:

      Just wondering – with the first part of splitting the IP address into column per octet – would using the Text to Columns function not be easier (using the “.” as the delimeter)?

  2. Dieter says:

    Your function to trim my second octet isn’t working for me. My functions are in another language so, there could be missing there, but ok.

    What I am wondering… you use in a formula: (FIND(“.”,A2)+1)-FIND(“.”,A2))
    But this will allways give you the value 1, not?!

  3. Adam says:

    This is brilliant.

    I do have one issue with the 2nd octet. It appears when I use 2 digits for the first octet i.e. the second octet loses the last digit.

    If I enter as it appears to be working correctly, any suggestions as I have spent a few hours trying to work it out.

    Many thanks.


  4. Deon says:

    Thank you very much. This helped me with getting my subnets from IP addresses for a report I had to do.

  5. Simon says:

    This was excellent. There is a minor error when the subnet mask is smaller than a Class C – is easily fixed, at the end of the forumla the number 26 needs to be changed to 25 – it was only evaluating the last 7 digits in the octet instead of 8.

  6. There is a quicker more elegant way of breaking down an IP address into its parts by creating a custom function, see beow:

    In excell open VBA (Macro -> Visual Basic Editor, or Ctrl+F11)

    Insert -> Module

    and, in that module type the text in-between the dashed lines:

    Public Function Octet(inIp As String, tetNo As Integer) as Byte
    Dim a() As String
    a = Split(inIp,”.”)
    Octet = Cbyte(a(tetNo-1)) ‘ Subtract 1 for 1,2,3,4
    End Function

    So for example if cell A3 has and you enter the formula =Octet(A3,2) it will return the second octet or =Octet(A3,3) will return the 3rd octet etc…

  7. York says:

    You are cool man. Thank you.

  8. Josh Rogers says:

    Excellent work. You’re long train ride has saved me hours of work figuring out the same thing.

  9. Scott1962 says:

    Nice, But how do you display the /24 bit from the valid IP Address?
    if I put in I would like to get and a /27

  10. says:

    in offce 2007 (transp view; )
    Subnet Mask
    addr_dot_1 =FIND(“.”;Table4[[#This row];[IP-Address]];1)
    mask_dot_1 =FIND(“.”;Table4[[#This row];[Subnet Mask]];1)
    addr_dot_2 =FIND(“.”;Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_1]]+1)
    mask_dot_2 =FIND(“.”;Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_1]]+1)
    addr_dot_3 =FIND(“.”;Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_2]]+1)
    mask_dot_3 =FIND(“.”;Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_2]]+1)
    addr_first =MID(Table4[[#This row];[IP-Address]];1;Table4[[#This row];[addr_dot_1]]-1)
    mask_first =MID(Table4[[#This row];[Subnet Mask]];1;Table4[[#This row];[mask_dot_1]]-1)
    addr_second =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_1]]+1;Table4[[#This row];[addr_dot_2]]-Table4[[#This row];[addr_dot_1]]-1)
    mask_second =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_1]]+1;Table4[[#This row];[mask_dot_2]]-Table4[[#This row];[mask_dot_1]]-1)
    addr_third =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_2]]+1;Table4[[#This row];[addr_dot_3]]-Table4[[#This row];[addr_dot_2]]-1)
    mask_third =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_2]]+1;Table4[[#This row];[mask_dot_3]]-Table4[[#This row];[mask_dot_2]]-1)
    addr_fourth =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_3]]+1;LEN(Table4[[#This row];[IP-Address]])-Table4[[#This row];[addr_dot_3]])
    mask_fourth =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_3]]+1;LEN(Table4[[#This row];[Subnet Mask]])-Table4[[#This row];[mask_dot_3]])
    addr_first2 =DEC2BIN(Table4[[#This row];[addr_first]];8)
    mask_first3 =DEC2BIN(Table4[[#This row];[mask_first]];8)
    addr_second4 =DEC2BIN(Table4[[#This row];[addr_second]];8)
    mask_second5 =DEC2BIN(Table4[[#This row];[mask_second]];8)
    addr_third6 =DEC2BIN(Table4[[#This row];[addr_third]];8)
    mask_third7 =DEC2BIN(Table4[[#This row];[mask_third]];8)
    addr_fourth8 =DEC2BIN(Table4[[#This row];[addr_fourth]];8)
    mask_fourth9 =DEC2BIN(Table4[[#This row];[mask_fourth]];8)
    addr_bin_concatenate =CONCATENATE(Table4[[#This row];[addr_first2]];Table4[[#This row];[addr_second4]];Table4[[#This row];[addr_third6]];Table4[[#This row];[addr_fourth8]])
    mask_bin_concatenate =CONCATENATE(Table4[[#This row];[mask_first3]];Table4[[#This row];[mask_second5]];Table4[[#This row];[mask_third7]];Table4[[#This row];[mask_fourth9]])
    0_pos_in_mask =FIND(0;Table4[[#This row];[mask_bin_concatenate]];1)
    network_bin_arrd_part =LEFT(Table4[[#This row];[addr_bin_concatenate]];Table4[[#This row];[0_pos_in_mask]]-1)
    network_bin_mask_part =RIGHT(Table4[[#This row];[mask_bin_concatenate]];33-Table4[[#This row];[0_pos_in_mask]])
    network_bin_ =CONCATENATE(Table4[[#This row];[network_bin_arrd_part]];Table4[[#This row];[network_bin_mask_part]])
    network_len =LEN(Table4[[#This row];[network_bin_]])
    network_bin_1 =MID(Table4[[#This row];[network_bin_]];1;8)
    network_bin_2 =MID(Table4[[#This row];[network_bin_]];9;8)
    network_bin_3 =MID(Table4[[#This row];[network_bin_]];17;8)
    network_bin_4 =MID(Table4[[#This row];[network_bin_]];25;32)
    network_dec_1 =BIN2DEC(Table4[[#This row];[network_bin_1]])
    network_dec_2 =BIN2DEC(Table4[[#This row];[network_bin_2]])
    network_dec_3 =BIN2DEC(Table4[[#This row];[network_bin_3]])
    network_dec_4 =BIN2DEC(Table4[[#This row];[network_bin_4]])
    network_address =CONCATENATE(Table4[[#This row];[network_dec_1]];”.”;Table4[[#This row];[network_dec_2]];”.”;Table4[[#This row];[network_dec_3]];”.”;Table4[[#This row];[network_dec_4]])

  11. cousty says:

    Hi, just been reading through this and it looks great however I am having one or two problems with the formulas and I can’t seem to download it. Is it still available for download ?

  12. BS says:

    Am I not doing the things correctly or is there a bug in the second value in the IP address, if the first value has less that 3 digits??

    • there is a bug, fix in the comments(I think). I may not have uploaded the fix.

      • BS says:

        Hi chentiangemalc,

        thank you very much for your quick answer.
        Actually, I did not see Adam’s post, and did not see either your file ( gives a 404, BTW). I just copied/pasted the formulae I needed from your post.

        FYI, I modified your formula for the second octet and used (where D2 is the full IP)

        (please check it as my Office suite is not in English, so the functions & syntax I use in Excel are not the same)



  13. Jeff M. says:

    I’m trying to get a subnet from entering a network address and a broadcast address. I can’t get it to work, but I’m trying to compare binary of the network address and the broadcast address to get the subnet mask. For example:

    11000000101001110000000000000000 + 11000000101010001111111111111111 = 11111111111111100000000000000000

    192.167..0.0 + =

    How do I configured Excel to do this? Thanks..

    • John Brawn says:

      Actually, your example numbers don’t work. The masked subnet that includes goes from to The smallest subnet that includes and is with a mask of . You always have to be careful of CIDR boundaries when doing this stuff. Maybe your code was giving you the right answer and you didn’t realize it?

  14. Seeking knowledge says:


    Hope that you are doing fine.
    I just have notice that last octet equation should be BIN2DEC(MID(I2,25,8)) not BIN2DEC(MID(I2,26,8)) in the formula where changing the network IP from binary to decimal.

    Have a great day.

    • Yes that is correct. I thought I corrected this, but obviously not…

      • Seeking knowledge says:

        Your way for dividing the problem in steps is great…
        I was trying to make formula like this but i was lazy as though that it would be hard to do.
        Thank you for the great post, my problem is solved now :)

  15. George says:

    Hey man can you upload it somewhere else?

  16. Tom says:

    This is the most insane and insanely useful thing I’ve seen for a while. Thanks. I owe you beer.

  17. William says:

    You, sir, are a god among mortals! Thank you so much for this.

    Of course, I have a question. I’m trying to munge the formulas so I can also find the first and last useable IP addresses in the range but I am being a monumental idiot and not able to do it.

    If anyone has some help for me I’m sure it’s something many people would appreciate.


  18. Gary says:

    Very good solution, save me time in working this all out. I also had an issue with the 2nd Octet giving 2 digit answers when it should have been 3. I removed a set of () from the formula
    you have
    this worked for me

  19. turgay says:


    Binary IP and Binary Address formül is not working. Can I help me!



  20. rich says:


  21. HELYANTI says:

    thank you for the tutorial. it is very helpful. However the URL to download file doesn’t work.

  22. Rom says:

    As always from Chentiangemalc amazing tools. link to download worked with no problems!23010&authkey=!AKuKOKyhDNAw2YU&ithint=file%2cxlsb
    and the subnets I checked are coming correct, save me lots of time building changes.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s