Nov 06

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))))

Comments Off on Sorting IP addresses in Excel
comments

Reply