ip2long and long2ip in Excel, SQLite and Oracle

Ever stuck with having to do data manipulation in Excel at a customer's restricted Microsoft environment, say with IP Addresses?  Here are some time savers for your IPV4 address manipulation in Microsoft Excel.  I have also added some SQLite function which can be helpful as SQLite has no native INET_NTOA or INET_ATON functions in MySQL.

If you have a column in Excel "A" that had IP addresses in it and you want to calculate an integer value (ip2long), here is the trick
=MID(A1,1,FIND("|",SUBSTITUTE(A1,".","|",1))-1)*2^24+MID(A1,FIND("|",SUBSTITUTE(A1,".","|",1))+1,FIND("|",SUBSTITUTE(A1,".","|",2))-FIND("|",SUBSTITUTE(A1,".","|",1))-1)*2^16+
MID(A1,FIND("|",SUBSTITUTE(A1,".","|",2))+1,FIND("|",SUBSTITUTE(A1,".","|",3))-FIND("|",SUBSTITUTE(A1,".","|",2))-1)*2^8+MID(A1,FIND("|",SUBSTITUTE(A1,".","|",3))+1,4)

Basically use Microsoft's indexOf function called FIND (Note: there is not lastindexOf) and SUBSTRING to walk through an IP address and multiple it by 2^24,2^16, etc... to get a long unsigned integer value.

You are ready to convert it back or you have some data that IP address in unsigned integer or long format: Here is a way to get your IP address back
=HEX2DEC(MID(DEC2HEX(B1,8),1,2))&"."&HEX2DEC(MID(DEC2HEX(B1,8),3,2))&"."&HEX2DEC(MID(DEC2HEX(B1,8),5,2))&"."&HEX2DEC(MID(DEC2HEX(B1,8),7,2))

Here you have a column "B" which has IP address in long format.  You can basically convert the long number to a hexadecimal 8 places using DEC2HEX from Excel.  A number like 73 will become 00000049 or a long number 3265961985 will become C2AAA001 which is basically 194.170.160.1 (C2=194,AA=170..) as an IP address.

If you are in SQLite, you have a column named "ip" that contains IP addresses as strings, here is the SQL SELECT statement that will change the string to IP integer format:
(INET_ATON equivalent)

SELECT REPLACE(ip,LTRIM(ip,"1234567890"),"")*16777216
+RTRIM(RTRIM(RTRIM(LTRIM(LTRIM(ip,"1234567890"),"."),"1234567890"),"."),"1234567890")*65536
+SUBSTR(LTRIM(RTRIM(LTRIM(LTRIM(ip,"1234567890"),"."),"1234567890"),"1234567890"),2)*256
+REPLACE(ip,RTRIM(ip,"1234567890"),"") FROM ipSTRING;

If you have column called IPint that contains integer IPS, here is the SQL statement (credit to John Machin from SQLite developers group), which can do the reverse and act as INET_NTOA in MySQL

SELECT IPint, ((IPint >> 24) & 255) ||'.'||
((IPint >> 16) & 255) ||'.'||  ((IPint >>  8) & 255) ||'.'||  ((IPint ) & 255)
FROM ipNUMBER;

In action:
sqlite> SELECT IPint, ((IPint >> 24) & 255) ||'.'||
   ...> ((IPint >> 16) & 255) ||'.'||  ((IPint >>  8) & 255) ||'.'||  ((IPint ) & 255)
   ...> FROM ipNUMBER;
202050571|12.11.12.11
16909060|1.2.3.4
sqlite> select replace(ip,ltrim(ip,"1234567890"),"")*16777216++rtrim(rtrim(rtrim(ltrim(ltrim(ip,"1234567890"),"."),"1234567890"),"."),"1234567890")*65536+substr(ltrim(rtrim(ltrim(ltrim(ip,"1234567890"),"."),"1234567890"),"1234567890"),2)*256+replace(ip,rtrim(ip,"1234567890"),""),ip from ipdb;
202050571|12.11.12.11
16909060|1.2.3.4

For those in the Oracle world, here is a package that will cover you 

/*

UTILITY functions for IP manipulation.

IP2LONG -> converts IPv4 address to long integer

LONG2IP -> converts IP long integer to dotted notation

version : 1.1

Updated: 5/15/2014

Author: Vijay Sarvepalli vss@cert.org

*/

CREATE OR REPLACE PACKAGE IP_UTL AS

  FUNCTION IP2LONG(IP_STRING VARCHAR2) RETURN NUMBER;

  FUNCTION LONG2IP(IPLONG NUMBER) RETURN VARCHAR2;

END IP_UTL;

/

CREATE OR REPLACE PACKAGE BODY IP_UTL AS  

FUNCTION IP2LONG(IP_STRING VARCHAR2) RETURN NUMBER

IS IP2LONG NUMBER;

BEGIN

RETURN

TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 1)) * 16777216 + -- 2^24

TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 2)) * 65536 + -- 2^16

TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 3)) * 256 + -- 2^8

TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 4)); -- 2^0

END IP2LONG;

FUNCTION LONG2IP(IPLONG NUMBER) RETURN VARCHAR2

IS 

BEGIN

    RETURN TO_CHAR(MOD(TRUNC(IPLONG/16777216),256))

           ||'.'||TO_CHAR(MOD(TRUNC(IPLONG/65536),256))

           ||'.'||TO_CHAR(MOD(TRUNC(IPLONG/256),256))

           ||'.'||TO_CHAR(MOD(IPLONG,256));

END LONG2IP;

END IP_UTL;

/

COMMIT

/