IP to IPX network number formula

Todd Vohs

unread,

Nov 27, 2001, 6:02:28 PM11/27/01

to

Someone has probably done this before but here is my dilema. I need to take
an IP Address Network Number, i.e., 10.100.56.0, and convert the numbers to
hex, i.e., an IPX Network Number. All my workbooks have IP listed as above.
I can get it to work if I break out each octet of the IP and put into its
own cell

F30 = 10
G30 = 100
H30 = 56
I30 = 0

The formula in J30 is:

=CONCATENATE(DEC2HEX(F30,2),(DEC2HEX(G30,2)),(DEC2HEX(H30,2)),(DEC2HEX(I30,2
)))

and the output is:

0A643800

This works but it requires more typing to put 10.100.56.0 into a four
separate cells and running the formula against the cells. Anyway of taking
10.100.56.0 and concatenating and dec2hex without separating?

Any help appreciated. TIA.

Todd.

--
This email was scanned on its way to you using Norton Antivirus 2002.
If you encounter a virus from this, please notify me.

rjb

unread,

Nov 27, 2001, 10:07:11 PM11/27/01

to

Here is a brute force method:

Type in the following:
A1 10.100.56.0 <<<IP address here
B1 =SEARCH(".",A1,1)
C1 =SEARCH(".",A1,B1+1)
D1 =SEARCH(".",A1,C1+1)
E1 =MID(A1,1,B1-1)
F1 =MID(A1,B1+1,C1-B1-1)
G1 =MID(A1,C1+1,D1-C1-1)
H1 =MID(A1,D1+1,3)
I1
==CONCATENATE(DEC2HEX(E1,2),(DEC2HEX(F1,2)),(DEC2HEX(G1,2)),(DEC2HEX(H1,2)))

Adjust to whatever cells you desire.

Rick
-----------------

"Todd Vohs" <tv196...@hotmail.com> wrote in message
news:#yKvle5dBHA.1776@tkmsftngp07...

Peo Sjoblom

unread,

Nov 28, 2001, 9:17:30 AM11/28/01

to

Hi Todd,

one way, albeit rather messy that will return the same
result if the IP address is in A1, can then be copied down

=DEC2HEX(LEFT(A1,SEARCH(".",A1)-1),2)&DEC2HEX(MID(A1,SEARCH
(".",A1)+1,SEARCH(".",A1,SEARCH(".",A1)+1)-SEARCH(".",A1)-
1),2)&DEC2HEX(MID(A1,SEARCH(".",A1,SEARCH(".",A1)+1)
+1,SEARCH(".",A1,SEARCH(".",A1,SEARCH(".",A1)+1)+1)-SEARCH
(".",A1,SEARCH(".",A1)+1)-1),2)&DEC2HEX(RIGHT(A1,LEN(A1)-
SEARCH(".",A1,SEARCH(".",A1,SEARCH(".",A1)+1)+1)),2)

if you put 10.100.56.0 in A1, it will return 0A643800

Regards,

Peo Sjoblom

>.
>

Harlan Grove

unread,

Nov 29, 2001, 5:08:55 AM11/29/01

to

"Todd Vohs" <tv196...@hotmail.com> wrote...

>Someone has probably done this before but here is my dilema. I need to
take
>an IP Address Network Number, i.e., 10.100.56.0, and convert the numbers to
>hex, i.e., an IPX Network Number. All my workbooks have IP listed as
above.

...
>0A643800
...
> . . . Anyway of taking 10.100.56.0 and concatenating and dec2hex without
>separating?
...

I had to give this a try. How about the array formula

=DEC2HEX(SUM(MID(IP,SMALL(IF(MID("."&IP&".",
ROW(INDIRECT("1:17")),1)=".",ROW(INDIRECT("1:17"))),
{1;2;3;4}),SMALL(IF(MID("."&IP&".",ROW(INDIRECT("1:17")),
1)=".",ROW(INDIRECT("1:17"))),{2;3;4;5})-SMALL(IF(MID("."
&IP&".",ROW(INDIRECT("1:17")),1)=".",ROW(INDIRECT("1:17"))),
{1;2;3;4})-1)*256^{3;2;1;0}),8)

where IP should be replaced with a cell refernce containing an IP address.
If you create two defined names, seq4 referring to ={1;2;3;4} and seq17
referring to ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}, this could be
rewritten as

=DEC2HEX(SUM(MID(IP,SMALL(IF(MID("."&IP&".",seq17,1)=".",
seq17),seq4),SMALL(IF(MID("."&IP&".",seq17,1)=".",seq17),1+seq4)
-SMALL(IF(MID("."&IP&".",seq17,1)=".",seq17),seq4)-1)*256^(4-seq4)),8)

But this is still a monster. Better to use a udf for this!