如何在excel中将64位数字转换为十六进制?

I'm trying DEC2HEX(1000000000050000000) but it comes out as #NUM! as the number is too large for this function.

我正在尝试DEC2HEX(1000000000050000000)，但是结果是#NUM!因为这个数对这个函数来说太大了。

Is there another function I could use to turn this number into hexadecimal?

还有别的函数可以把这个数变成十六进制吗?

3 个解决方案

#1

There is a free add-in available that will handle that: Xnumbers

有一个免费的外接程序可以处理这个:xnumber。

Seems to work OK:

似乎工作好:

=cvDecBase("1000000000050000000",16) -->  DE0B6B3AA5EF080

#2

If you want to convert a decimal number to a 64 bit hex string, assuming that the decimal number is in cell A1 you can use the following:

如果您想将一个十进制数转换为64位十六进制字符串，假设十进制数在单元格A1中，您可以使用以下方法:

=CONCATENATE(DEC2HEX(B3/2^32),DEC2HEX(MOD(B3,2^32),8))

This will work up to decimal value of 18,446,744,073,709,500,000 or hex value of 0xfffffffffffff800.

它的十进制值为18446,744,073,709,500,000或十六进制值为0xfffffffffffff800。

Bonus:

奖金:

To convert from hex string to decimal, assuming that the 64bit hex string is in cell A1 and contains 16-characters then you can use the following:

要将十六进制字符串转换为十进制，假设64位十六进制字符串在单元格A1中，并且包含16个字符，则可以使用以下方法:

=HEX2DEC(LEFT(A1,8))*2^32+HEX2DEC(RIGHT(A1,8))

You can adjust the number of characters in the LEFT(text,[num_chars]) to better suit your needs.

您可以调整左边的字符数(文本，[num_chars])，以更好地满足您的需要。

If your hex string has a 0x then you can use the following:

如果您的十六进制字符串有0x，那么您可以使用以下内容:

=HEX2DEC(MID(A1,3,8))*2^32+HEX2DEC(RIGHT(A1,8))

Paul

保罗

#3

The DEC2HEX function has a limit of 549,755,813,887, try this formula it works for numbers up to 281,474,976,710,655.

DEC2HEX函数的极限是549,755,813,887，试试这个公式，它适用于281,474,976,710,655个数字。

=DEC2HEX(A7/(16^9),3)&DEC2HEX(MOD(A7,16^9),9)

= DEC2HEX(A7 /(16 ^ 9),3)&DEC2HEX(MOD(A7、16 ^ 9),9)

#1