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

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:

View fullsize

View fullsize

View fullsize

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

Windows/Office Frustrations

The title should cover the topic appropriately.... The last couple of days there's this delay on opening Office documents on my work laptop. Every MS-Word or Excel document I open (by double clicking the document) takes approximately 25 to 30 seconds to open.

Using tools like procmon (formerly known as filemon from SysInternals) displayed nothing interesting. Apart from a 15 to 20 seconds delay between the WINWORD.EXE appearances in the logging.
Since this nagged the hell out of me, I tried some stuff (in a non-chronological order);

• Removed McAfee AV
• Removed all tooling I installed in the last couple of days
• Removed  MS-Word
• Removed Office 2003 completely
• Removing all references to Office, MS-Word or Excel in the registry, and on the filesystem.

Running Word with the /a switch or even typing winword.exe c:\test.doc works fine. Everything works, except the default opening of a file by double clicking it.

Someone suggested to add another user on the system and try it with that user account. So I did. I logged on as the new user and every document launched as it's supposed to do :-) . So I logged out, and tried the original user, and guess what? The document opened lightning fast. I couldn't be more happy, because I didn't feel like reinstalling the entire system.

After working a couple of hours I restarted the system (application update), and everything went back to 'normal'... Opening documents took forever again.....
It seems that launching Word from a FRESH user account resets something. Everything afterwards works fine, just as long as you don't reboot... And since it's Windows...... Well, Friday is gonna be a re-installment day. Am I looking forward to that (that's something of a rhetorical thing).