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)

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

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)**

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)**

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)**

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:

**=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**

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))**

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

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

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…

**=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**

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

The .xlsx file used in this sample can be downloaded from here http://www.tiange.com.au/subnets.zip

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

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)?

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

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

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

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.

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…

You are cool man. Thank you.

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

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

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]])

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 ?

I’ll try to upload somewhere else later

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.

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

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

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?

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…

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

Hey man can you upload it somewhere else?

Thanks!

https://onedrive.live.com/redir?resid=E1A3C870740A073D!23010&authkey=!AKuKOKyhDNAw2YU&ithint=file%2cxlsb

Your formula only works if the second octate is the same size of the first one … 10.238.xxx.xxx for say a 255.255.xxx.xxx subnet gets chopped to 10.23. ..not 10.238

yes I fixed that but may not have uploaded the fix.

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

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

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)

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

Amazing

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

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.