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
TechnologyManguAsked:
Who is Participating?
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.

Saurabh Singh TeotiaCommented:
In C7 you can simply apply this formula...

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

Open in new window


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),".","")

Open in new window


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

Saurabh...
0
QlemoBatchelor, 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.

Add this to your VBA code:
Function ipWithCIDR(ip1 As String, ip2 As String) As String
  Dim maskbits As Integer
  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))
  ipWithCIDR = IpAnd(IpWithoutMask(ip1 & "/" & maskbits), IpMask(ip1 & "/" & maskbits)) & "/" & maskbits
End Function

Open in new window

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.