Riaan's SysAdmin Blog

My tips, howtos, gotchas, snippets and stuff. Use at your own risk!


Sorting IP addresses in Excel

Sometimes you need to manipulate lists of IP addresses in Excel but of course it sorts on strings which is not ideal for IP addresses. A quick way to sort is adding another column with the following formula and sorting on that new column.

=((VALUE(LEFT(B6, FIND(".", B6)-1)))*256^3)+((VALUE(MID(B6, FIND(".", B6)+1, FIND(".", B6, FIND(".", B6)+1)-FIND(".", B6)-1)))*256^2)+((VALUE(MID(B6, FIND(".", B6, FIND(".", B6)+1)+1, FIND(".", B6, FIND(".", B6, FIND(".", B6)+1)+1)-FIND(".", B6, FIND(".", B6)+1)-1)))*256)+(VALUE(RIGHT(B6, LEN(B6)-FIND(".", B6, FIND(".", B6, FIND(".", B6)+1)+1))))


Bio Info for Riaan