A question came up on my Twitter feed this week – how to sort a list of IP Addresses in Excel. On the face of it, this should be simple. However, because Excel treats the IP addresses as text strings, not numbers, the sort order will not be correct, for example 172.24.161.200 will come before 172.24.161.8 (2 comes before 8 in an alphabetic sort). This tutorial explains how to sort a list of IP addresses correctly.
For demonstration purposes I have a small list of IP addresses. In reality you’d likely have a much longer list. This list represents the IP addresses being used in a company and the location of the computer/device using them.
Sort the List – 1
Select the range A1:B6 and select Sort from the Data tab on The Ribbon (or the Data menu depending on which version of Excel you are using)
Sort the List – 2
Ensure that you tick the “My data has headers” box (1)
To sort this block of data by IP Address, select “IP Address” from the dropdown list in the “Column” section (2)
Notice that in the “Order” section (3) it shows as “A to Z”, indicating that Excel is treating the IP addresses as text strings rather than numeric values (If it treated them as numeric values it would say “smallest to largest”).
Click OK (4)
Sort the List – 3
The list of IP addresses is indeed sorted, but not numerically
Split the IP Address – 1
To correctly sort a list of IP addresses requires 3 steps. The first step is to split each IP address into separate cells. The screenshot below is the desired end result
Split the IP Address – 2
Rather than doing this manually, use Excel’s Text To Columns feature.
First of all, insert 4 blank columns to the right of column A, otherwise you will overwrite existing data when the Text to Columns command is issued.
Split the IP Address – 3
Select all the cells containing the IP addresses (A2:A6)
Select Text to Columns from the Data tab on the Ribbon (or the Data menu depending on which version of Excel you are using)
Split the IP Address – 4
The Text to Columns Wizard is launched. Ensure that “Delimited” is selected and click Next
Split the IP Address – 5
On step 2 of the Wizard, tick the “Other” box and type a full stop (period) into the box next to it. This indicates to Excel that the full stop character is the delimiter, i.e the character that is used to split up the text string into 4 separate parts.)
Split the IP Address – 6
On step 3 of the Wizard, ensure that the Destination field contains =$A$2. This is the location where Excel will store the split text. Note that it is not necessary to select A2:D6. Excel will use as many columns as necessary (which is why extra columns were inserted earlier).
Split the IP Address – 7
This is the result
Sort the List – 1
The second step of the 3-step process is to sort the list.
Select A2:F6 and use the settings as per the screenshot. Notice that in the “Order” section it shows as “Smallest to Largest”, indicating that Excel is treating the IP address elements as numeric values.
Sort the List – 2
This is the result of the multi-column sort:
Recreating the Original IP Addresses
The final step of the 3-step process is to combine the elements back into a single string.
Type this formula into E2:
The Concatenate function is used to create a single string from multiple cells and/or literal characters. In this case the function joins together the contents of A2, a full stop, the contents of B2, a full stop, the contents of C2, a full stop and the contents of D2.
Copy the formula to E3:E6
Tidying Up – 1
Columns B:D can now be deleted, however, the formula in column E refers to these columns. If these columns are deleted, the formulas will break. Therefore, before deleting columns B:D:
Select cells E2:E6
Select the Copy command
Click the lower section of the Paste button
Select Paste Values
The reason to use Paste Values rather than Paste, is so that Excel copies the result of the the formula rather than the formula itself.
Tidying Up – 2
Finally, delete columns B:D
**Updated 10th April 2011: I recently discovered an add-in that can sort a list of IP addresses with a couple of clicks. More information here