Sort by IP in Sharepoint list

Question

I have a column in Sharepoint containing IP-addresses. I can't figure out how to sort them correctly.

They are being sorted like this:

192.168.1.1

192.168.1.10

192.168.1.100

192.168.1.2

Rather than

192.168.1.1

192.168.1.2

192.168.1.10

192.168.1.100

The only "solution" I can find was this thread:

http://social.technet.microsoft.com/Forums/sv-SE/sharepointgeneral/thread/c0902e45-0427-4081-b64a-b41ecaecf900

However, I have tried the formula suggestions and they simply don't work correctly. I have tried by using two columns with a formula each and by using one column with the combined formula:

=LEFT([Internal IP];7)&RIGHT([Internal IP];3)

It seems silly to me that sorting numbers would be such a hassle. Have I missed something?

Answer 1

This is actually pretty easy, I've done this in SQL Server for a long time. You just need to stop thinking of the data as an IP address. There is an old trick called bit twiddling that can be used to convert the value to an integer first. For example, 192.168.0.1 becomes 3232235521. Integers are fast and easy to sort. After doing the conversion, you drop the integer into a hidden computed column and do the sort on that column.

So if you have a text column called IPAddress and a calculated field called Integer, you would add this formula to compute the Integer column - it's really just a little binary math and you only end up storing an additional Int32 by treating each octet as a byte:

=VALUE(IF(NOT(ISBLANK([IPAddress]));(LEFT([IPAddress];
FIND(".";[IPAddress];1)-1)*16777216)+(MID([IPAddress];
FIND(".";[IPAddress];1)+1;FIND(".";[IPAddress];
FIND(".";[IPAddress];1)+1)-FIND(".";[IPAddress];1)-1)*65536)+(MID([IPAddress];
FIND(".";[IPAddress];FIND(".";[IPAddress];1)+1)+1;
FIND(".";[IPAddress];FIND(".";[IPAddress];
FIND(".";[IPAddress];1)+1)+1)-FIND(".";[IPAddress];
FIND(".";[IPAddress];1)+1)-1)*256)+(RIGHT([IPAddress];
LEN([IPAddress])-FIND(".";[IPAddress];
FIND(".";[IPAddress];FIND(".";[IPAddress];1)+1)+1)));0))

Then create a view that sorts by the hidden calcualted field Integer. If you need to sort IPv6 addresses, the concept is the same, but you're converting from hex values to binary to integers and you'll use more storage.

Answer 2

If Joshua' formula doesn't quite work for you, you may need to substitute all the semicolons for commas.

=VALUE(IF(NOT(ISBLANK([IPAddress])),(LEFT([IPAddress],
 FIND(".",[IPAddress],1)-1)*16777216)+(MID([IPAddress],
 FIND(".",[IPAddress],1)+1,FIND(".",[IPAddress],
 FIND(".",[IPAddress],1)+1)-FIND(".",[IPAddress],1)-1)*65536)+
 (MID([IPAddress],FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)+1,
 FIND(".",[IPAddress],FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)+1)-
 FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)-1)*256)+(RIGHT([IPAddress],
 LEN([IPAddress])-FIND(".",[IPAddress],FIND(".",[IPAddress],
 FIND(".",[IPAddress],1)+1)+1))),0))

The above is formatted for clarity and normally entered on one line.