将十进制MAC地址转换为十六进制MAC地址 [英] Convert decimal MAC address into hexadecimal MAC address
问题描述
我一直在寻找一种将十进制MAC地址转换为十六进制的方法。
所以例如 170.187.204.0.17.34
到 AA:BB:CC:00:11:22
。
我把十进制值放入columnA(起始A2),十六进制等价物应该进入columnB(起始B2) p>
尽管我喜欢阅读@pnuts的代码,这可能是一个更简单的解决方案:
= 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)
第一行利用了 DEC2HEX
截断其参数的事实:
- 给定170.187.204.0.17.34,
MID
返回170,这已经是一个整数。 - 给定1.9.1.1.1.1,
MID
返回1.9,其中DEC2HEX
截断为1。第二至第六行SUBSTITUTE
第n个。使用^然后FIND
该字符,返回接下来的3个字符。给定170.187.204.0.17.34,这些MID
函数返回:
- 187 < $>
- 0.1
- 17。
- 34
然后将这些提供给
DEC2HEX
。
我们只是用冒号连接所有东西。
输出:
AA:BB:CC:00:11:22
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
toAA: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:
=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, 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:- 187
- 204
- 0.1
- 17.
- 34
These are then fed to
DEC2HEX
.And we simply concatenate everything with colons.
Output:
AA:BB:CC:00:11:22
这篇关于将十进制MAC地址转换为十六进制MAC地址的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- 187 < $>