Sort ip address in excel

wordpress meta

title: 'Sorting IP addresses in Excel'
date: '2014-11-06T06:50:44-06:00'
status: publish
permalink: /sort-ip-address-in-excel
author: admin
excerpt: ''
type: post
id: 768
category:
    - Uncategorized
tag: []
post_format: []

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