# Converting IP Range to CIDR in Excel

I have a spreadsheet (downloaded from a different site) that allows you to convert CIDR notation to ranges, masks, and other details, and I am trying to figure out if I can use a function to convert a range to CIDR. I don't have the knowledge to convert some of the scripts/functions to help.

What I'd like is a function that takes 2 cells and provides CIDR notation in a 3rd.

I've attached examples.
IP-Calculator.xlsm
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
In C7 you can simply apply this formula...

``````=B7&"/"&RIGHT(B8,2)
``````

In additional another safe way to do this since IP address at the end can be 3 characters as well you can do this as well to ensure it picks up correctly irrespective if its 3 characters or 2 characters...

``````=B7&"/"&SUBSTITUTE(RIGHT(B8,3),".","")
``````

Assuming the last ip address will be of 2 digits...

Saurabh...
"Batchelor", Developer and EE Topic AdvisorCommented:
Gosh, that's soooo wrong.
For getting the CIDR notation, we need to determine the network address (B6) from B7. That can get tricky, if B7 isn't really the first usuable address. Imagine you would enter 172.16.64.2, because you do not know better ...

The basic idea is to build an XOR of lowest and highest address. This result is "the difference", in bits changing between both. In the example it is 15.
From that we have to find the first binary one from left. I'll use the mathematic approach using logarithmus dualis (based on 2), with some rounding up. Here that results in 4, which means 4 bits are used for the hosts.
Subtract that from 32, and you get the mask bits - 28.
Apply that to the lowest or highest or any address of the range as mask, and get the network.

But we have an issue: the numbers might get too big for using XOR, as we need 32 bits for representing a complete IP, and VBA allows only for 31 bits plus sign. So we have to do it with a detour.

``````Function ipWithCIDR(ip1 As String, ip2 As String) As String
Dim diff As Long
Dim tip1 As String, tip2 As String
' The IPStrToBin function returns double, which can be too big for XOR
' hence we need to get more tricky
diff = 0
tip1 = ip1
tip2 = ip2
For i = 0 To 3
diff = diff + (IpParse(tip1) Xor IpParse(tip2)) * (256 ^ i)
Next

maskbits = 31 - Int(Log(diff) / Log(2))
End Function
``````
Then use this in A2:   =ipWithCIDR(B7;B8)
Of course you have to make sure B7 and B8 contains values, no formulas. Or you put the formula above in B2, so you can set A2 and compare with B2, then leave B7 and B8 as-is.

Experts Exchange Solution brought to you by