@ > Home > Contents > Convert IP-Address (IP4) between String, Number and Hex Excel 97+

A1: is an IP-Adresse (String). Change this IP to a 12-digit number:

B1: =SUMPRODUCT(SUBSTITUTE(MID(SUBSTITUTE(A1,".",":::::::::"),ROW(1:4)*10-9,10),":",)*10^(3*(4-ROW(1:4))))

returning 192001020003. The reverse function

C1: =IF(B1<10^9,"0.","")&IF(B1<10^6,"0.","")&IF(B1<10^3,"0.","")&SUBSTITUTE(SUBSTITUTE(

returns '

The 3 leading IF's could be replaced by 


but this is not much shorter and does not work in Pocket Excel.

An Alternative =SUBSTITUTE(TEXT(B1,"#,##0");",";".") returns a string with byte-leading 0's: '

"String to Number" does not work with IP6, because it requires 18 digits, where Excel only can provide 15.

A hexadecimal format of IP4 requires 2 formulas (before xl2007) since it requires 11 nested levels:

D1: =TRUNC(B1/16/10^9)&"."&MOD(TRUNC(B1/10^9),16)&"."&

returns the intermediary ', and


returns 'C0011403 finally. xl2007: Simply replace D1 in E1 by D1's formula.


Last line of formula D1: MOD(B1,10^6) must be used - instead of only B1 - due to an Excel bug in MOD(), which would occur at IP>

The "decimal number" is not running, but speaking. In other words: Each section of thousands (3 digits) uses 0-255 instead of 0-999. 3/4 of all numbers are not used because an IP uses 256^4 instead of 1000^4.

For IP6 this means (as mentioned above): 10^18 = 1000^6 can not be used because of the significant digit limit of 15, but 256^6 is possible.

The sites http://excelformeln.de/formeln.html?welcher=117 and http://excelformeln.de/formeln.html?welcher=262 show how to change from HEX to DEC Numbers.