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:

image

 

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

image

 

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

=MID(A2,1,FIND(“.”,A2,1)-1)

 

image

 

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

=MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1)

 

image

 

and for the 3rd IP octect:

=MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1)

image

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

Now for the fourth and final octet:

=MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1)

image

 

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!

image

 

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

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

image

After some corrections it started working:

=CONCATENATE(DEC2BIN(MID(A2,1,FIND(“.”,A2,1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1),8))

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

image

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

=CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))

image

 

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.

=CONCATENATE(MID(G2,1,FIND(“0”,H2)-1),MID(H2,FIND(“0”,H2),33-FIND(“0”,H2)))

image

 

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:

=CONCATENATE(BIN2DEC(MID(I2,1,8)),”.”,BIN2DEC(MID(I2,9,8)),”.”,BIN2DEC(MID(I2,17,8)),”.”,BIN2DEC(MID(I2,26,8)))

 

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

image

 

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.

image

image

 

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!!!!

image

Here it is in all it’s glory…

=CONCATENATE(BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A2,1,FIND(“.”,A2,1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))))),1,8)),”.”,BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A2,1,FIND(“.”,A2,1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))))),9,8)),”.”,BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A2,1,FIND(“.”,A2,1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))))),17,8)),”.”,BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A2,1,FIND(“.”,A2,1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1),8),DEC2BIN(MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))))),26,8)))

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

image

Then range will already be selected, then click OK

image

 

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.

image

 

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 http://www.tiange.com.au/subnets.zip

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.

38 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. 10.232.171.128 the second octet loses the last digit.

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

    Many thanks.

    Adam

  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 192.168.1.129 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 192.168.1.32 I would like to get 255.255.255.224 and a /27

  10. me_tm@mail.ru says:

    in offce 2007 (transp view; )
    IP-Address 4.69.17.254
    Subnet Mask 255.255.255.248
    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 (http://www.tiange.com.au/ 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)
        =IF(ISBLANK(D2),””,VALUE(LEFT(RIGHT(D2,LEN(D2)-FIND(“.”,D2)),FIND(“.”,RIGHT(D2,LEN(D2)-FIND(“.”,D2)))-1)))

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

        Regards

        BS

  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 + 192.168.255.255 = 255.254.0.0

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

    • John Brawn says:

      Actually, your example numbers don’t work. The 255.254.0.0 masked subnet that includes 192.168.255.255 goes from 192.168.0.0 to 192.169.255.255. The smallest subnet that includes 192.167.0.0 and 192.168.255.255 is 192.160.0.0/12 with a mask of 255.240.0.0 . 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:

    Hello,

    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?
    Thanks!

  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.

    ~W

  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
    =MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,(FIND(“.”,A2)+1)-FIND(“.”,A2))-1)
    this worked for me
    =MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,FIND(“.”,A2)+1)-FIND(“.”,A2)-1)

  19. turgay says:

    Hi,

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

    Thanks,

    =CONCATENATE(DEC2BIN(MID(B2,1,FIND(“.”,B2,1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2)+1,FIND(“.”,B2,(FIND(“.”,B2)+1)-FIND(“.”,B2))-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)-FIND(“.”,B2,FIND(“.”,B2)+1)-1),8),DEC2BIN(MID(B2,FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1,LEN(B2)-FIND(“.”,B2,FIND(“.”,B2,FIND(“.”,B2)+1)+1)+1),8))

  20. rich says:

    Amazing

  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 https://onedrive.live.com/redir?resid=E1A3C870740A073D!23010&authkey=!AKuKOKyhDNAw2YU&ithint=file%2cxlsb
    and the subnets I checked are coming correct, save me lots of time building changes.

  23. Ali says:

    I am looking for such a related excel that we can calculate the range of IP according to demand. For instance we have 50 host and able to find first and last one.

  24. charles says:

    Thee was a problem with subnets in which the last octet of the VLAN was 128 or larger. Replacng the trailing “26” in the formula with “25” fixed it for me. Here’s the formula with this fix and the fix identified by Gary:
    =CONCATENATE(BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A1,1,FIND(“.”,A1,1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1)+1,FIND(“.”,A1,FIND(“.”,A1)+1)-FIND(“.”,A1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1)+1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))))),1,8)),”.”,BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A1,1,FIND(“.”,A1,1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1)+1,FIND(“.”,A1,FIND(“.”,A1)+1)-FIND(“.”,A1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1)+1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))))),9,8)),”.”,BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A1,1,FIND(“.”,A1,1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1)+1,FIND(“.”,A1,FIND(“.”,A1)+1)-FIND(“.”,A1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1)+1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))))),17,8)),”.”,BIN2DEC(MID(CONCATENATE(MID(CONCATENATE(DEC2BIN(MID(A1,1,FIND(“.”,A1,1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1)+1,FIND(“.”,A1,FIND(“.”,A1)+1)-FIND(“.”,A1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1)+1)-1),8),DEC2BIN(MID(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)+1),8)),1,FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)))-1),MID(CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8)),FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))),33-FIND(“0”,CONCATENATE(DEC2BIN(MID(B1,1,FIND(“.”,B1,1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1)+1,FIND(“.”,B1,(FIND(“.”,B1)+1)-FIND(“.”,B1))-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)-FIND(“.”,B1,FIND(“.”,B1)+1)-1),8),DEC2BIN(MID(B1,FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1,LEN(B1)-FIND(“.”,B1,FIND(“.”,B1,FIND(“.”,B1)+1)+1)+1),8))))),25,8)))

  25. charles says:

    P.S.: You’ll still need to handle the replacement of the “fancy font matching quotes” in the formula with standard non-matching quotes that Excel expects, otherwise you’ll get a #NAME?
    error. The web site input box replaces the standard quotes with “fancy font matching quotes” when posting to the web site.

  26. Deepan Sridhar says:

    Can someone please upload the latest excel sheet. Unable to download from https://onedrive.live.com/redir?resid=E1A3C870740A073D!23010&authkey=!AKuKOKyhDNAw2YU&ithint=file%2cxlsb

  27. Darren Rogers says:

    OK, first off awesome, and thanks. I really needed this for some work I am doing in an environment where I cannot use VB Scripts or Macros, this helped a lot. However, There is an error which I cannot figure out how to correct.
    regarding the Network ID formula
    =CONCATENATE(MID(AK1,1,FIND(“0”,AR1)-1),MID(AR1,FIND(“0”,AR1),33-FIND(“0”,AR1)))

    This does not work for instance when you have an IP address for example 10.10.10.129/25, the correct return s/b 10.10.10.128 for the network ID, the formula returns 10.10.10.0.

    So… did I miss something somewhere? I can post my entire spreadsheet if necessary.

    Darren

    • Yes I think there are some errors in the formula; I fixed some them will have to check if I uploaded it. It’s not the easiest thing to troubleshoot. To work out bugs reasonably easily you need to check the results of each section of the formula. In retrospect I should have just used a comptuer program to generate the formula.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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