I have been searching for a method to convert a decimal MAC address into a hex one.
So for example 170.187.204.0.17.34
to AA:BB:CC:00:11:22
.
Credits goes to Convert HEX to Decimal value? w/ an example.
I put my decimal value into columnA (starting A2) and the hexadecimal equivalent should go into columnB (starting B2).
As much as I enjoyed reading through @pnuts ' code, this may be a simpler solution:
Line one takes advantage of the fact that
DEC2HEX
truncates its parameter:MID
returns 170, which is already an integer.MID
returns 1.9, whichDEC2HEX
truncates to 1.Lines two through six
SUBSTITUTE
the nth "." with "^" and thenFIND
that character, returning the next 3 characters. Given 170.187.204.0.17.34, theseMID
functions return:These are then fed to
DEC2HEX
.And we simply concatenate everything with colons.
Output:
AA:BB:CC:00:11:22
With
170.187.204.0.17.34
in A1, apply Text to Columns with.
as the delimiter. In A2 copied across to F2:in G2:
=A2&":"&B2&":"&C2&":"&D2&":"&E2&":"&F2
Or, in B2 and dealing with hex for
0
: