With 170.187.204.0.17.34
in A1, apply Text to Columns with .
as the delimiter. In A2 copied across to F2:
=DEC2HEX(A1)
in G2: =A2&":"&B2&":"&C2&":"&D2&":"&E2&":"&F2
Or, in B2 and dealing with hex for 0
:
=SUBSTITUTE(DEC2HEX(MID(A2,1,FIND(".",A2)-1))&":"&DEC2HEX(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),1,FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))-1))&":"&DEC2HEX(MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),1,FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))-1))&":"&DEC2HEX(MID(MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)),1,FIND(".",MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)))-1))&":"&DEC2HEX(MID(MID(MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)),FIND(".",MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)))+1,LEN(A2)),1,FIND(".",MID(MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)),FIND(".",MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)))+1,LEN(A2)))-1))&":"&DEC2HEX(MID(MID(MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)),FIND(".",MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)))+1,LEN(A2)),FIND(".",MID(MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)),FIND(".",MID(MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)),FIND(".",MID(MID(A2,FIND(".",A2)+1,LEN($A2)),FIND(".",MID(A2,FIND(".",A2)+1,LEN($A2)))+1,LEN(A2)))+1,LEN(A2)))+1,LEN(A2)))+1,LEN(A2))),":0:",":00:")
As much as I enjoyed reading through @pnuts ' code, this may be a simpler solution:
=DEC2HEX(MID(A2,1,3),2)&":"&
DEC2HEX(MID(A2,FIND("^",SUBSTITUTE(A2,".","^",1))+1,3),2)&":"&
DEC2HEX(MID(A2,FIND("^",SUBSTITUTE(A2,".","^",2))+1,3),2)&":"&
DEC2HEX(MID(A2,FIND("^",SUBSTITUTE(A2,".","^",3))+1,3),2)&":"&
DEC2HEX(MID(A2,FIND("^",SUBSTITUTE(A2,".","^",4))+1,3),2)&":"&
DEC2HEX(MID(A2,FIND("^",SUBSTITUTE(A2,".","^",5))+1,3),2)
Line one takes advantage of the fact that DEC2HEX
truncates its parameter:
- Given 170.187.204.0.17.34,
MID
returns 170, which is already an integer.
- Given 1.9.1.1.1.1,
MID
returns 1.9, which DEC2HEX
truncates to 1.
Lines two through six SUBSTITUTE
the nth "." with "^" and then FIND
that character, returning the next 3 characters. Given 170.187.204.0.17.34, these MID
functions return:
These are then fed to DEC2HEX
.
And we simply concatenate everything with colons.
Output: AA:BB:CC:00:11:22