This post is basically a reminder for myself, since for some reason I run into this challenge over and over again.

The challenge is that I have to switch between the following notations:

10.1.2.0/24
10.1.2.0 255.255.255.0

This is because different programs/systems require different inputs, and I don’t always feel like using scripting (like Python) to transform the content.

Excel

So here are some Excel formulas that can be used.

Generate a netmask from a CIDR notation (e.g. 10.1.2.0/24) from cell A119:

=(IF(ISNUMBER(SEARCH("/32",A119)),"255.255.255.255",
IF(ISNUMBER(SEARCH("/31",A119)),"255.255.255.254",
IF(ISNUMBER(SEARCH("/30",A119)),"255.255.255.252",
IF(ISNUMBER(SEARCH("/29",A119)),"255.255.255.248",
IF(ISNUMBER(SEARCH("/28",A119)),"255.255.255.240",
IF(ISNUMBER(SEARCH("/27",A119)),"255.255.255.224",
IF(ISNUMBER(SEARCH("/26",A119)),"255.255.255.192",
IF(ISNUMBER(SEARCH("/25",A119)),"255.255.255.128",
IF(ISNUMBER(SEARCH("/24",A119)),"255.255.255.0",
IF(ISNUMBER(SEARCH("/23",A119)),"255.255.254.0",
IF(ISNUMBER(SEARCH("/22",A119)),"255.255.252.0",
IF(ISNUMBER(SEARCH("/21",A119)),"255.255.248.0",
IF(ISNUMBER(SEARCH("/20",A119)),"255.255.240.0",
IF(ISNUMBER(SEARCH("/19",A119)),"255.255.224.0",
IF(ISNUMBER(SEARCH("/18",A119)),"255.255.192.0",
IF(ISNUMBER(SEARCH("/17",A119)),"255.255.128.0",
IF(ISNUMBER(SEARCH("/16",A119)),"255.255.0.0",
IF(ISNUMBER(SEARCH("/15",A119)),"255.254.0.0",
IF(ISNUMBER(SEARCH("/14",A119)),"255.252.0.0",
IF(ISNUMBER(SEARCH("/13",A119)),"255.248.0.0",
IF(ISNUMBER(SEARCH("/12",A119)),"255.240.0.0",
IF(ISNUMBER(SEARCH("/11",A119)),"255.224.0.0",
IF(ISNUMBER(SEARCH("/10",A119)),"255.192.0.0",
IF(ISNUMBER(SEARCH("/9",A119)),"255.128.0.0",
IF(ISNUMBER(SEARCH("/8",A119)),"255.0.0.0",
IF(ISNUMBER(SEARCH("/7",A119)),"254.0.0.0",
IF(ISNUMBER(SEARCH("/6",A119)),"252.0.0.0",
IF(ISNUMBER(SEARCH("/5",A119)),"248.0.0.0",
IF(ISNUMBER(SEARCH("/4",A119)),"240.0.0.0",
IF(ISNUMBER(SEARCH("/3",A119)),"224.0.0.0",
IF(ISNUMBER(SEARCH("/2",A119)),"192.0.0.0",
IF(ISNUMBER(SEARCH("/1",A119)),"128.0.0.0",
"")))))))))))))))))))))))))))))))))

Strip the netmask bits from the CIDR notation (in cell A2):

=LEFT(A2, SEARCH("/", A2) -1)

And to convert from network address and netmask to CIDR notation (combining cell B2 with C2):

=CONCAT(B2,(
IF(C2="255.255.255.255","/32",
IF(C2="255.255.255.254","/31",
IF(C2="255.255.255.252","/30",
IF(C2="255.255.255.248","/29",
IF(C2="255.255.255.240","/28",
IF(C2="255.255.255.224","/27",
IF(C2="255.255.255.192","/26",
IF(C2="255.255.255.128","/25",
IF(C2="255.255.255.0","/24",
IF(C2="255.255.254.0","/23",
IF(C2="255.255.252.0","/22",
IF(C2="255.255.248.0","/21",
IF(C2="255.255.240.0","/20",
IF(C2="255.255.224.0","/19",
IF(C2="255.255.192.0","/18",
IF(C2="255.255.128.0","/17",
IF(C2="255.255.0.0","/16",
IF(C2="255.254.0.0","/15",
IF(C2="255.252.0.0","/14",
IF(C2="255.248.0.0","/13",
IF(C2="255.240.0.0","/12",
IF(C2="255.224.0.0","/11",
IF(C2="255.192.0.0","/10",
IF(C2="255.128.0.0","/9",
IF(C2="255.0.0.0","/8",
IF(C2="254.0.0.0","/7",
IF(C2="252.0.0.0","/6",
IF(C2="248.0.0.0","/5",
IF(C2="240.0.0.0","/4",
IF(C2="224.0.0.0","/3",
IF(C2="192.0.0.0","/2",
IF(C2="128.0.0.0","/1",
""))))))))))))))))))))))))))))))))))

The way it looks in Excel:

Some Python Code

>>> from netaddr import IPAddress
>>> print(IPAddress('255.255.255.0').netmask_bits())
24

or

>>> from ipaddress import IPv4Network
>>> print(IPv4Network('0.0.0.0/255.255.255.0').prefixlen)
24

or

>>> netmask = '255.255.255.0'
>>> print(sum(bin(int(x)).count('1') for x in netmask.split('.')))
24

or

>>> from ipaddress import IPv4Network
>>> net = IPv4Network("192.4.2.0/24")
>>> print(net.netmask)
255.255.255.0

Convert Notations of Networks, Netmasks and CIDR (visa versa)

Excel

Some Python Code