Excel – Sorting IP Addresses

We’re currently planning an IP address change across two sites for a client
and as such the first step is to pull together all existing machines with related address
so we can document the current and proposed IPs.
Using Angry IPScanner (http://angryip.org/w/Download) I ran a scan on the specific
range and exported the result to a csv.

Obviously not all IP’s in the range are in use and so show as dead, which we want to remove from the document. So
I selected the data, arranged by the column showing ping times and then deleted all the entries showing as dead at
the bottom. The problem I now came across in Excel (2010) is when I try to sort so the IP addresses are in order it starts with .1 then .10 .11 etc which obviously isn’t right…..

So here’s the workaround, it looks long winded but it’s pretty quick and easy:

The first step is to insert four new columns to the right of the column containing the IP addresses, let’s say that’s column A.

Excel Order IP Addresses
Select all cells containing IP addresses and then from the Data Tab select Text to Columns

excel2

On launching the text to Columns wizard you can now select Delimited for your original data type and then press next. Remove the tick from Tab as a delimiter and select Other, and enter a full stop. Now press next.

excel3

The final step in splitting the ip address is to ensure that the Destiation field contains =$A$2. This denotes where Excel will store the split data. Now click finish.

excel4

You will no have your ip addresses split into four parts in four columns as below

excel5

Now all you need to do is sort them and put them back into a single address. In this example I’ll be sorting on column D, so select all the data, click the data tab select sort and how you want to sort it (ascending for me). Now the final step is to join it back together.

In the top cell of the empty column (E in this example) type the following and then drag to copy to all subsequent cells

=CONCATENATE(A2,”.”,B2,”.”,C2,”.”,D2)

You obviously now have extra columns which you no longer require, but as column E is a formula it will break if others are removed. The way around this is:

– select column E
– copy the data
– select cell A2
– Click the lower section of the Paste button
– Select Paste Values (This is so the results are copied rather than the formula)

excel6

That’s it we’re done – you can now delete any unwanted columns.

This entry was posted in Excel 2010 and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.