IP address conversion formula

Hi,

i want to convert IP address into decimal number so that if i have IP's in Col A then i get decimal numbers in Col B.

The formula is like this:

if IP address is 192.168.1.2

then the formula to get its decimal number is: 192*16777216 + 168*65536 + 1*256 + 2

which comes out to be = 3232235778

So if A1 has 192.168.1.2 then B1 will come out to be 3232235778.

Any idea how to write above formula?

Re: IP address conversion formula

Here!

=(LEFT(A1,3)*16777216 )+(MID(A1,5,3)*65536 )+(MID(A1,FIND(".",A1,6)+1,FIND(".",A1,FIND(".",A1,6)+1)-FIND(".",A1,6)-1)*256 )+RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,6)+1))

Re: IP address conversion formula

Yes its working but only when all the numbers in the IP are 3 digits, for example: 123.345.567.890

but what if the numbers are not 3 digits as this: 12.34.4.5 ?

Re: IP address conversion formula

Try this, then...

Formula:

=16777216*MID(SUBSTITUTE("."&$A1,".",REPT(" ",125)),125*COLUMNS($A:A),125)+65536*MID(SUBSTITUTE("."&$A1,".",REPT(" ",125)),125*COLUMNS($A:B),125)+256*MID(SUBSTITUTE("."&$A1,".",REPT(" ",125)),125*COLUMNS($A:C),125)+MID(SUBSTITUTE("."&$A1,".",REPT(" ",125)),125*COLUMNS($A:D),125)

Re: IP address conversion formula

Let me point out that there is an inherit problem with displaying IP addresses as decimal numbers. If the first octet of the IP address (the number displayed before the first decimal point / dot) is any value less than 100 (i.e. 2.13.99.102) then you would have to format the cell displaying that content to show leading zeroes (12 digits) so it would display as 002013099102. Otherwise it would display as 2013099102, 10 digits rather than 12, which can be misleading. One basic reason for wanting to convert IP address ranges in cells to decimal numbers or strings without dotted decimal notation is in order to properly sort them by hierarchy.

With that in mind let me give you two sets of formulas, one to display the IP address as a decimal value (must be formatted to display 12 digits) and one to display it as a string (text), and then convert those results back to a valid IP address string.

For DECIMAL VALUE RESULTS:

If cell A1 contains the IP address string, let cell B1 contain the following formula to convert it to a decimal value:

**=VALUE(CONCATENATE(TEXT(MID(A1,1,FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1)+1,FIND(".",A1,1+FIND(".",A1,1))-FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))+1),"000")))**

To convert the decimal value now in cell B1 back to a properly formatted string for an IP Address, put the following formula in to cell C1:

**=CONCATENATE(INT(B1/1000000000),".",INT(B1/1000000)-(INT(B1/1000000000)*1000),".",INT(B1/1000)-(INT(B1/1000000)*1000),".",B1-(INT(H23/1000)*1000))**

For STRING CONTENT RESULTS:

To convert cell A1 to a 12 digit string (text), put the following formula into cell B1:

**=CONCATENATE(TEXT(MID(A1,1,FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1)+1,FIND(".",A1,1+FIND(".",A1,1))-FIND(".",A1,1)-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))-1),"000"),TEXT(MID(A1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))+1,FIND(".",A1,1+FIND(".",A1,1+FIND(".",A1,1)))-FIND(".",A1,1+FIND(".",A1,1))+1),"000"))**

To convert the cell B1 twelve digit string back to a properly formatted IP address string put the following formula into cell C1:

**=CONCATENATE(TEXT(MID(B1,1,3),"##0"),".",TEXT(MID(B1,4,3),"##0"),".",TEXT(MID(B1,7,3),"##0"),".",TEXT(MID(B1,10,3),"##0"))**

I hope you find these formulas useful.

Good luck!

Dan Callaghan (Server Engineer / Web Designer)

Re: IP address conversion formula

Or try this ...

=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",100)),ROW($1:$4)*100-99,100))*256^(4-ROW($1:$4)))

Re: IP address conversion formula

I think there is a piece missing from your formula to convert decimal back to IP string. You reference a cell H23 but don't give us any data for that cell.

I took 10.164.44.252 & converted it to 10164044252 with the first formula. The 2nd made it back into 10.164.44.10164044252

Please let me know what I'm doing wrong.

Re: IP address conversion formula

I know it's bad form resurrecting an old thread, but I just had to go make an account specifically to tell you that this is brilliant!

Re: IP address conversion formula

Quote:

Originally Posted by

**Phuocam**
Or try this ...

=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",100)),ROW($1:$4)*100-99,100))*256^(4-ROW($1:$4)))

I know it's bad form resurrecting an old thread, but I just had to go make an account specifically to tell you that this is brilliant!