Technology Notebook

Microsoft::Excel::Formula::Sort by IP Number

I hate that editors, excel, SQL reports, etc will not sort properly by IP address.
This great Excel formula will calculate a unique integer value for an IP address to allow sorting on that value:


=((VALUE(LEFT(A2, FIND(".", A2)-1)))*256^3)+((VALUE(MID(A2, FIND(".", A2)+1, FIND(".", A2, FIND(".", A2)+1)-FIND(".", A2)-1)))*256^2)+((VALUE(MID(A2, FIND(".", A2, FIND(".", A2)+1)+1, FIND(".", A2, FIND(".", A2, FIND(".", A2)+1)+1)-FIND(".", A2, FIND(".", A2)+1)-1)))*256)+(VALUE(RIGHT(A2, LEN(A2)-FIND(".", A2, FIND(".", A2, FIND(".", A2)+1)+1))))

And to turn that number back into an IP number:


=IF(B2<>"", CONCATENATE(INT(B2/256^3), ".", INT(MOD(B2, (256^3))/(256^2)), ".", INT(MOD(MOD(B2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(B2, 256^3), 256^2), 256)), "")

Thanks to Matt Schuster, quoted at www.mvps.org