My work

This blog is about stuff I learned at work.
It might come in handy someday.

Oh, and I just moved to a new geographic location this August 2014. :)

In the mean time, visit my other blog listed on the right under "Fun Stuff".

How to sort IP addresses in a spreadsheet

At the moment of this writing, Excel and Open Office doesn't sort IP addresses the way we needed them to be sorted.
For example, using conventional methods, sorting the following:
10.0.0.1
10.0.0.2
10.0.10.1
10.0.0.10
would show as:
10.0.0.1
10.0.0.10
10.0.0.2
10.0.10.1
To be able to sort them the way we needed without resorting to macros and very complex equations. We can use the decimal to hexadecimal conversion. During the conversion, we should not alter the original document before finishing this. Make a copy if you must and work with that.
We must first copy the IP address column to a text file and save it as txt. Saving it directly as csv will only show 1 column when we needed 4. Open it using Excel or Open Office and use "." as delimiters. If this doesn't work, you may have to replace the periods to commas before exporting. Our objective it to make the IP addresses form separate columns for each decimal. So instead of 10.0.0.1, we would be having 4 columns with the values 10, 0, 0 and 1 respectively.
Then, using the equation:
Excel
=dec2hex(cell,2)
OpenOffice
=dec2hex(cell;2)
We would then be converting each decimal to its hexadecimal equivalent for each columns.
Then, concatenating them to a fifth column would result in our desired value:
MS
=concatenate(B5,B6,B7,B8)
Oo
=concatenate(B5;B6;B7;B8)
Assuming that the IP address segments are in the first 4 columns with a header followed by their Hex counterparts in the next 4. The equation would combine the values into 1 column:
Copying the last column and then insert this column into the original using 'Copy' and then 'Paste Special' and selecting values only (equations is unchecked). We can now use that for sorting.
Sample data table is show below with equations in place. The first 4 values are on the first 4 columns (A~D), Row 2:
For MS Excel:
10    0    0    1    =DEC2HEX(A2,2)    =DEC2HEX(B2,2)    =DEC2HEX(C2,2)    =DEC2HEX(D2,2)    =CONCATENATE(E2,F2,G2,H2)
For Open Office:
10    0    0    1    =DEC2HEX(A2;2)    =DEC2HEX(B2;2)    =DEC2HEX(C2;2)    =DEC2HEX(D2;2)    =CONCATENATE(E2;F2;G2;H2)