Quote of the Day
Abolition was a pipe dream in 1835 – it was reality 25 year later.
— Tom Rick, defense analyst, quoting his historian wife about how fast things can change in the US.
Figure 1: Example of an IP Address Expressed in Octets.
My oldest son called me last night and was wondering how to use Excel to sort a long list of IPv4 addresses in numerical order. These IPv4 addresses are written in a human readable form using octets (Figure 1), which Excel treats as text and will sort in alphabetic order. Make no mistake about it – while IPv4 addresses are often written in terms of octets, the IPv4 address is a 32-bit binary value.
My son wants me to convert the octets string into their binary values, and sort the list in order of increasing binary value. While that is what my son wants, I have an evil agenda of my own in mind – I want him to learn more about Excel. Thus, I decided to solve the problem in four different ways:
- Using the text-to-columns command.
This approach allows me to separate out the octets and use a simple array function to turn into a binary value. This approach is easy but it does not update real-time – you must rerun the text-to-columns command if you add more data.
- Using text functions to separate out the octets and generate the binary value.
This approach updates real-time, but the resulting function is long and difficult to understand.
- VBA function using parsing.
This is conceptually simple, but it takes a fair number of source lines of code.
- VBA function using the Instr function.
I like this approach because it is short and easy to understand. It is the approach I prefer. However, many folks do not like using VBA.
My son can examine each approach and decide which one is appropriate for his task. While working through the methods, he will also learn some Excel tricks.
Here is my Excel workbook that converts IPv4 addresses from octets to binary. I did not sort the data as he knows how to do sorting. It does have some VBA in it, but nothing pathological.