by Allen Wyatt
(last updated October 15, 2016)
Chuck has a worksheet that, in one column, contains a series of IP addresses. These are in the familiar format of 192.168.2.1. If he sorts the addresses, they are not numerically sorted. For instance, Excel places 192.168.1.100 between 192.168.1.1 and 192.168.1.2. Chuck wonders if there is a way to sort a column of IP addresses so they appear in the proper sequence.
This happens because Excel views an IP address as text, not as a number or a series of numbers. There are a few ways you can work around the problem, a few of which I'll discuss in this tip. You should choose the approach that is right for your needs, as defined by your data and how you use that data.
One approach is to make sure that each octet of your IP addresses consist of three digits. (An octet is each part of the IP address, separated by periods.) For instance, instead of an address such as 192.168.1.1, you would use 192.168.001.001. This "front pads" each octet with zeros and, if all of your IP addresses are in this format, they will sort correctly.
If you prefer to use a formula to ensure the front-padding of each octet, you could use the following:
=TEXT(LEFT(A1,FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND( ".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND(".",A1,FIND(".",A1,1)+1)+1,FIND(".",A1, FIND(".",A1,FIND(".",A1,1)+1)+1)-FIND(".",A1,FIND(".",A1,1)+1)-1), "000") & "." & TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND( ".",A1,1)+1)+1)),"000")
This formula is quite long, but it is still a single formula. Place it in the column next to your first IP addresss (assuming that address is in cell A1) and then copy it down as many rows as required. When you do your sorting, sort by column B, and the addresses will be in the proper sequence.
If you work with a lot of IP addresses, you may want to create a user-defined function that will front-pad each octet of the IP address with zeros and then return a fully formatted IP. The following will perform the task:
Function FormatIP(IPAddr As String) As String Dim Dot1 As Integer Dim Dot2 As Integer Dim Dot3 As Integer Dim Octet1 As String Dim Octet2 As String Dim Octet3 As String Dim Octet4 As String Dot1 = InStr(1, IPAddr, ".", vbTextCompare) Dot2 = InStr(Dot1 + 1, IPAddr, ".", vbTextCompare) Dot3 = InStr(Dot2 + 1, IPAddr, ".", vbTextCompare) Octet1 = Left(IPAddr, Dot1-1) Octet2 = Mid(IPAddr, Dot1+1, Dot2-Dot1-1) Octet3 = Mid(IPAddr, Dot2+1, Dot3-Dot2-1) Octet4 = Mid(IPAddr, Dot3+1, Len(IPAddr)) FormatIP = Right("000" & Octet1, 3) & "." FormatIP = FormatIP & Right("000" & Octet2, 3) & "." FormatIP = FormatIP & Right("000" & Octet3, 3) & "." FormatIP = FormatIP & Right("000" & Octet4, 3) End Function
In Excel, then, you could use the UDF in this manner, assuming your original IP address in in cell A1:
Another approach is to simply divide the IP addresses into separate columns, putting each octet in its own column. This is easy to do if you use the Text to Columns tool, in this manner:
Figure 1. The beginning of the Convert Text to Columns Wizard.
Once done, you can sort the four columns as you would normally sort numbers. Then, when you want to put the IP addresses back together, you could use a formula such as this:
=A1 & "." & B1 & "." & C1 & "." & D1
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13481) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Sorting is one of the basic operations done in a worksheet. If your sorting won't work and you instead get an error message, ...Discover More
Need to sort your data based on the color of the cell or the color of the text within the cell? Excel makes it easy to do the ...Discover More
Want to sort addresses by even and odd numbers? By using a formula and doing a little sorting, Excel can return the addresses ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.