Multiple conversion of IP Numbers to IP Addresses

[thepeoplespal]

My missus is looking to set up a .htaccess file to block certain people from a website she helps to maintain.

Basically she has several hundred (Decimal) IP Numbers which she wants to convert to IP Addresses, she is not a programmer. So is there anyone out there who knows a formula for converting multiple IP Numbers into IP Addresses using say Excel or Access.

Example IP Number to IP Address:
1040466943 converts to 62.4.67.255

She doesn't have access to PHP, Nuke or Server type software and the only freeware converters she has found so far do ONE conversion at a time. She has found IP Address to IP Number, but not the other way round.

Any help would be appreciated. Cheers.

[JamieBeeston]

Formula to convert from IP to DWORD is.

Formula said:

Start with an IP address. In this example we'll use 80.253.108.11. Enter the following keystrokes into the calculator exactly as shown: 80 * 256 + 253 = * 256 + 108 = * 256 + 11 = The dword equivalent of the IP address will be the result. In this case, 1358785547.

You can just ping them to get the correct answer, but its a time consuming enterprise.

it wouldnt be so hard to just use a nix box,

paste in 100 entries, then copy back the results.

If you get stuck, feel free to mail the list to me via my profile, and I will have them converted for you.

Enjoy

[squirrelz]

Ok, this is hideous coding, but an excel formula would be

=CONCATENATE(MOD((((((A1-MOD(A1,256))/256)-MOD((A1-MOD(A1,256))/256,256))/256)-MOD((((A1-MOD(A1,256))/256)-MOD((A1-MOD(A1,256))/256,256))/256,256))/256,256),".",MOD((((A1-MOD(A1,256))/256)-MOD((A1-MOD(A1,256))/256,256))/256,256),".",MOD((A1-MOD(A1,256))/256,256),".",MOD(A1,256))

Paste your first value into A1, this formula into A2 and it should show the dotted format. Theres probably a really elegant way of doing this, but hey.

[TheHobbit]

I normally do it with inet_aton and inet_ntoa in perl and sql. dunno if excel has an equivalent?

[thepeoplespal]

squirrelz said:

Ok, this is hideous coding, but an excel formula would be =CONCATENATE(MOD((((((A1-MOD(A1,256))/256)-MOD((A1-MOD(A1,256))/256,256))/256)-MOD((((A1-MOD(A1,256))/256)-MOD((A1-MOD(A1,256))/256,256))/256,256))/256,256),".",MOD((((A1-MOD(A1,256))/256)-MOD((A1-MOD(A1,256))/256,256))/256,256),".",MOD((A1-MOD(A1,256))/256,256),".",MOD(A1,256)) Paste your first value into A1, this formula into A2 and it should show the dotted format. Theres probably a really elegant way of doing this, but hey.

Cheers for this, I'll pass it on and see what she thinks.(1) I'm sure if I'd have asked her brother (doing a Maths Phd for fun), he could have sorted the problem out, but there is only so many times you can make yourself look (feel) stupid.

(1) Edited to say - this is exactly what was required thanks a lot - don't think she wanted to manually do about 4700 IP addresses - would have taken all year and inevitably had mistakes.

>> Edited by thepeoplespal on Wednesday 5th May 20:27