Need help with how to convert IPnumbers to dotted quad

[skullaria]

I was able to use text to columns and some math to get IP dotted quad numbers
into columns. Now I need to take the processed data output excel file and
get the ipnumbers back to dotted quad.

I know the math:
To reverse IP number to IP address,

w = int ( IP Number / 16777216 ) % 256
x = int ( IP Number / 65536 ) % 256
y = int ( IP Number / 256 ) % 256
z = int ( IP Number ) % 256

where % is the mod operator and int is return the integer part of the
division.


But how do I do this in excel? I'm rather lost.

 

[Dave Peterson]

After you did data|text to columns, weren't you left with:

255 255 255 255
(separate columns)
from
255.255.255.255


If this is true, you can use:
=a1&"."&b1&"."&c1&"."&d1
or even:
=text(a1,"000.")&text(b1,"000.")&text(c1,"000.")&text(d1,"000")

if you want leading zeros.

 

[skullaria]

I finally got it solved. I really had a lot of trouble with mod because I
didn't understand it. Once I read a bunch of math sites it got better and
that allowed the formulas to be made.

I made 4 columns, w, x, y, and z and used the following sort of formula
=MOD(INT(B1/16777216),256) (this was the w box.)
on down the line....

THEN, I concantenated the results of each of those 4 boxes with the dot
between them into a 5th box. This was the IP address that I needed.