Converting IP Addresses to Decimal IP

This is part of creating a vlookup table to match an IP address to its associated ASnumber and Org. But first we need to convert the IP addresses (in Octet form) into decimal form.

So, now assuming the IP is in Column A2

=VALUE(LEFT(A2,FIND(“.”,A2)-1))*2²⁴

+VALUE(MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,FIND(“.”,A2)+1)-FIND(“.”,A2)-1))*2¹⁶

+VALUE(MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)-FIND(“.”,A2,FIND(“.”,A2)+1)-1))*2⁸

+VALUE(RIGHT(A2,LEN(A2)-FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)))

For the End IP block, assuming it is in B2, just replace A2 with B2.

After the formulas are copied to the rest of the sheet, we should have an output similar to this:

Also check out related stories:

CIDR to IP ranges
https://wilson-chua.medium.com/converting-cidr-into-ip-ranges-ce8f213a0a83
Decimal IP to Octet
https://wilson-chua.medium.com/converting-ip-decimal-to-ip-addresses-in-octet-form-a96143c7d021