A1: 192.1.20.3 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(
SUBSTITUTE(SUBSTITUTE(TEXT(B1,"#.##0"),".00","."),".0","."),"..",".0."),"..",".0.")&IF(RIGHT(B1,3)="000","0","")
returns '192.1.20.3.
The 3 leading IF's could be replaced by
=REPT("0.",SUMPRODUCT(--(A1<10^(3*ROW(1:3)))))&...
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: '192.001.020.003
"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)&"."&
TRUNC(MOD(B1,10^9)/16/10^6)&"."&MOD(TRUNC(MOD(B1,10^9)/10^6),16)&"."&
TRUNC(MOD(B1,10^6)/16/10^3)&"."&MOD(TRUNC(MOD(B1,10^6)/10^3),16)&"."&
TRUNC(MOD(MOD(B1,10^6),10^3)/16)&"."&MOD(TRUNC(MOD(MOD(B1,10^6),10^3)),16)
returns the intermediary '12.0.0.1.1.4.0.3, and
E1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
D1,"10","A"),"11","B"),"12","C"),"13","D"),"14","E"),"15","F"),".",)
returns 'C0011403 finally. xl2007: Simply replace D1 in E1 by D1's formula.
Remarks
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> 134.217.255.255.
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.