Search code examples
excelipip-addresssubtraction

Subtract the value of an IP Address from another

I have two cells in a sheet both containing an IP Address:

C2 = 192.168.0.1

C3 = 192.168.0.10

I want to subtract C2 from C3...giving me the result of 9. The addresses will always have the same first three octets. IN this example, 192.168.0 is constant in both cells. It is only the last octet that will change.

Can this be done? Everything I try, including Value and Value2 end in error.

So in say C4 my formula is 

=C3-C2
or
=Value(c3)-Value(c2)

Gives errors. I'm a total novice so be gentle. Thanks.

Solution

  • So, you need to convert each IP address to a number, then subtract the numbers to get your difference. In several programming languages this function is called inet_aton (it stands for "internet array to number"). Googling "inet_aton excel" returns several links, one of which gives a formula to do it:

    =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)))

    It's a mess, and I'm not going to verify it, but you should be able to use it to turn your IP addresses to numbers and subtract them.

    The next page of that blog tells you how to add this formula as a custom Excel function, which you can then use.