Excel and IP addresses

THIS CONTENT IS NOW HOSTED ON GITHUB: https://github.com/sweenig/ipfromdec/



It seems I am frustrated by Excel's inability to handle IP addresses natively.  Eventually, i should write some global formulas mimicing inet_ntoa() and inet_aton() from MySQL.  In the mean time, I've been creating the formulas manually to slice and dice IP addresses.  It's not actually too hard, as long as you're not scared by long formulas.  I usually build this formula in pieces then consolidate them down to one (I should write a procedure that would do that automatically).  Eventually...

Anyway, the formula is this:
=VALUE(LEFT(A1,FIND(".",A1)-1))*2^24+VALUE(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1))*2^16+VALUE(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-FIND(".",A1,FIND(".",A1)+1)-1))*2^8+VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)))

You can put this formula into any cell and it will take the IP address in cell A1 and convert it to decimal.  The nice thing about this is that you can apply this formula to a list of IP addresses then use it to sort the IP addresses.  That way 10.200.0.0 comes after 10.5.0.0.

See part 2 of this post here.