This formula takes a column called Mask and calculates the CIDR. It assumes that the subnet mask is valid. Note that if the column is called Subnet Mask this formula won’t work – it’s probably too long.
=IF(Mask="","",IF(Mask="0.0.0.0",0,0)+IF(Mask="128.0.0.0",1,0)+IF(Mask="192.0.0.0",2,0)+IF(Mask="224.0.0.0",3,0)+IF(Mask="240.0.0.0",4,0)+IF(Mask="248.0.0.0",5,0)+IF(Mask="252.0.0.0",6,0)+IF(Mask="254.0.0.0",7,0)+IF(Mask="255.0.0.0",8,0)+IF(Mask="255.128.0.0",9,0)+IF(Mask="255.192.0.0",10,0)+IF(Mask="255.224.0.0",11,0)+IF(Mask="255.240.0.0",12,0)+IF(Mask="255.248.0.0",13,0)+IF(Mask="255.252.0.0",14,0)+IF(Mask="255.254.0.0",15,0)+IF(Mask="255.255.0.0",16,0)+IF(Mask="255.255.128.0",17,0)+IF(Mask="255.255.192.0",18,0)+IF(Mask="255.255.224.0",19,0)+IF(Mask="255.255.240.0",20,0)+IF(Mask="255.255.248.0",21,0)+IF(Mask="255.255.252.0",22,0)+IF(Mask="255.255.254.0",23,0)+IF(Mask="255.255.255.0",24,0)+IF(Mask="255.255.255.128",25,0)+IF(Mask="255.255.255.192",26,0)+IF(Mask="255.255.255.224",27,0)+IF(Mask="255.255.255.240",28,0)+IF(Mask="255.255.255.248",29,0)+IF(Mask="255.255.255.252",30,0)+IF(Mask="255.255.255.254",31,0)+IF(Mask="255.255.255.255",32,0)) |
Note that if I was to do this again, I would probably just create a new list with the valid subnets and their CIDR values and then do a lookup on it.
One Comment
thank you.plesae i want to knwo everything about IP/SUBNET MASK.
Post a Comment