Change 18-digit dec number to hex

Hello,

When I have this number, 268435457800737937, Excel gives me an error when I
do the DEC2HEX formula. In an online converter, I get 3B9ACA06B551480. Any
ideas why I can't get a response from Excel?

Hi,

First problem is that there are no 18 digit numbers in Excel. Excel support
15 digits.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Toria" wrote:

Hello,

When I have this number, 268435457800737937, Excel gives me an error when I
do the DEC2HEX formula. In an online converter, I get 3B9ACA06B551480. Any
ideas why I can't get a response from Excel?

Thanks, Shane! Just to confirm, there's no way in Excel to convert to hex
from an 18-digit dec...?

"Shane Devenshire" wrote:

Hi,

First problem is that there are no 18 digit numbers in Excel. Excel support
15 digits.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Toria" wrote:

Hello,

When I have this number, 268435457800737937, Excel gives me an error when I
do the DEC2HEX formula. In an online converter, I get 3B9ACA06B551480. Any
ideas why I can't get a response from Excel?

If you take a look at help on dec2hex you may find the source of your error.

If number -549,755,813,888 or if number 549,755,813,887, DEC2HEX returns
the #NUM! error value.

Your example number certainly exceeds the maximum.

In help you will find other sources for errors arising.


Gord Dibben MS Excel MVP

On Wed, 11 Mar 2009 15:58:01 -0700, Toria
wrote:

Hello,

When I have this number, 268435457800737937, Excel gives me an error when I
do the DEC2HEX formula. In an online converter, I get 3B9ACA06B551480. Any
ideas why I can't get a response from Excel?

Did you read Excel Help for DEC2HEX?
"If number -549,755,813,888 or if number 549,755,813,887, DEC2HEX
returns the #NUM! error value."

Remember also that Excel numbers are stored to a precision of 15 significant
figures.

If you have your 18 digits as a string in A1 you can get an *approximation*
to the result by breaking it up, such as
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(--A1,2^32))
That gives 3B9ACA06B5510E0

If you convert it back, again breaking it up to manageable chunks,
=HEX2DEC(LEFT(A2,LEN(A2)-8))*2^32+HEX2DEC(RIGHT(A2,8))
gives 268435457800737000

If you look at your result, you will realise that 3B9ACA06B551480 cannot be
an accurate answer, as with zero at the end it must be a multiple of 16,
which 268435457800737937 isn't.

Doubtless with a more complicated segmentation of the text strings, you
could get the correct answer. For what it's worth, the Windows calculator
gives 3B9ACA06B551491
--
David Biddulph

Toria wrote:
Hello,

When I have this number, 268435457800737937, Excel gives me an error
when I do the DEC2HEX formula. In an online converter, I get
3B9ACA06B551480. Any ideas why I can't get a response from Excel?

If you are up for a UDF (User Defined Function), then give the function
below a try. To install it, press Alt+F11 to get into the Visual Basic
Editor, then click Insert/Module on its menu bar and copy/paste the function
below into the code window that opened up. Then you can use the function
just like a built-in function. Making sure your large number are entered as
text (either format the cell as text before entering the number into it or
place an apostrophe in front of the number when you enter it); for example,
put '268435457800737937 in A1) and then put...

=BigDec2Hex(A1)

in the cell you want the Hex value to be place in. Here is the function...

Function BigDec2Hex(ByVal DecimalIn As Variant) As String
Dim X As Integer
Dim BinaryString As String
Const BinValues = "*0000*0001*0010*0011" & _
"*0100*0101*0110*0111" & _
"*1000*1001*1010*1011" & _
"*1100*1101*1110*1111*"
Const HexValues = "0123456789ABCDEF"
Const MaxNumOfBits As Long = 96
BinaryString = ""
DecimalIn = Int(CDec(DecimalIn))
Do While DecimalIn 0
BinaryString = Trim$(Str$(DecimalIn - 2 * _
Int(DecimalIn / 2))) & BinaryString
DecimalIn = Int(DecimalIn / 2)
Loop
BinaryString = String$((4 - Len(BinaryString) _
Mod 4) Mod 4, "0") & BinaryString
For X = 1 To Len(BinaryString) - 3 Step 4
BigDec2Hex = BigDec2Hex & Mid$(HexValues, _
(4 + InStr(BinValues, "*" & _
Mid$(BinaryString, X, 4) & "*")) \ 5, 1)
Next
End Function

Note that for your example number, the function returns 3B9ACA06B551491
which is the correct answer (not the answer you showed in your posting).

--
Rick (MVP - Excel)


"Toria" wrote in message
...
Hello,

When I have this number, 268435457800737937, Excel gives me an error when
I
do the DEC2HEX formula. In an online converter, I get 3B9ACA06B551480.
Any
ideas why I can't get a response from Excel?

I said below:
"Doubtless with a more complicated segmentation of the text strings, you
could get the correct answer."

One possibility which seems to work is
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(INT(A1/10000),2^32)*10000+RIGHT(A1,4))
--
David Biddulph

David Biddulph wrote:
Did you read Excel Help for DEC2HEX?
"If number -549,755,813,888 or if number 549,755,813,887, DEC2HEX
returns the #NUM! error value."

Remember also that Excel numbers are stored to a precision of 15
significant figures.

If you have your 18 digits as a string in A1 you can get an
*approximation* to the result by breaking it up, such as
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(--A1,2^32))
That gives 3B9ACA06B5510E0

If you convert it back, again breaking it up to manageable chunks,
=HEX2DEC(LEFT(A2,LEN(A2)-8))*2^32+HEX2DEC(RIGHT(A2,8))
gives 268435457800737000

If you look at your result, you will realise that 3B9ACA06B551480
cannot be an accurate answer, as with zero at the end it must be a
multiple of 16, which 268435457800737937 isn't.

Doubtless with a more complicated segmentation of the text strings,
you could get the correct answer. For what it's worth, the Windows
calculator gives 3B9ACA06B551491

Toria wrote:
Hello,

When I have this number, 268435457800737937, Excel gives me an error
when I do the DEC2HEX formula. In an online converter, I get
3B9ACA06B551480. Any ideas why I can't get a response from Excel?

Just to point out... the function I posted will work with any decimal number
(entered as a text value) up to 28 decimal digits long. Actually, it will
work for some, but not all 29 digit decimal numbers; but if you stick to 28
or less, you can handle their entire range of values.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If you are up for a UDF (User Defined Function), then give the function
below a try. To install it, press Alt+F11 to get into the Visual Basic
Editor, then click Insert/Module on its menu bar and copy/paste the
function below into the code window that opened up. Then you can use the
function just like a built-in function. Making sure your large number are
entered as text (either format the cell as text before entering the number
into it or place an apostrophe in front of the number when you enter it);
for example, put '268435457800737937 in A1) and then put...

=BigDec2Hex(A1)

in the cell you want the Hex value to be place in. Here is the function...

Function BigDec2Hex(ByVal DecimalIn As Variant) As String
Dim X As Integer
Dim BinaryString As String
Const BinValues = "*0000*0001*0010*0011" & _
"*0100*0101*0110*0111" & _
"*1000*1001*1010*1011" & _
"*1100*1101*1110*1111*"
Const HexValues = "0123456789ABCDEF"
Const MaxNumOfBits As Long = 96
BinaryString = ""
DecimalIn = Int(CDec(DecimalIn))
Do While DecimalIn 0
BinaryString = Trim$(Str$(DecimalIn - 2 * _
Int(DecimalIn / 2))) & BinaryString
DecimalIn = Int(DecimalIn / 2)
Loop
BinaryString = String$((4 - Len(BinaryString) _
Mod 4) Mod 4, "0") & BinaryString
For X = 1 To Len(BinaryString) - 3 Step 4
BigDec2Hex = BigDec2Hex & Mid$(HexValues, _
(4 + InStr(BinValues, "*" & _
Mid$(BinaryString, X, 4) & "*")) \ 5, 1)
Next
End Function

Note that for your example number, the function returns 3B9ACA06B551491
which is the correct answer (not the answer you showed in your posting).

--
Rick (MVP - Excel)


"Toria" wrote in message
...
Hello,

When I have this number, 268435457800737937, Excel gives me an error when
I
do the DEC2HEX formula. In an online converter, I get 3B9ACA06B551480.
Any
ideas why I can't get a response from Excel?

I get a #NUM! error for a value of '123456789012345678 with your formula.

--
Rick (MVP - Excel)


"David Biddulph" groups [at] biddulph.org.uk wrote in message
...
I said below:
"Doubtless with a more complicated segmentation of the text strings, you
could get the correct answer."

One possibility which seems to work is
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(INT(A1/10000),2^32)*10000+RIGHT(A1,4))
--
David Biddulph

David Biddulph wrote:
Did you read Excel Help for DEC2HEX?
"If number -549,755,813,888 or if number 549,755,813,887, DEC2HEX
returns the #NUM! error value."

Remember also that Excel numbers are stored to a precision of 15
significant figures.

If you have your 18 digits as a string in A1 you can get an
*approximation* to the result by breaking it up, such as
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(--A1,2^32))
That gives 3B9ACA06B5510E0

If you convert it back, again breaking it up to manageable chunks,
=HEX2DEC(LEFT(A2,LEN(A2)-8))*2^32+HEX2DEC(RIGHT(A2,8))
gives 268435457800737000

If you look at your result, you will realise that 3B9ACA06B551480
cannot be an accurate answer, as with zero at the end it must be a
multiple of 16, which 268435457800737937 isn't.

Doubtless with a more complicated segmentation of the text strings,
you could get the correct answer. For what it's worth, the Windows
calculator gives 3B9ACA06B551491

Toria wrote:
Hello,

When I have this number, 268435457800737937, Excel gives me an error
when I do the DEC2HEX formula. In an online converter, I get
3B9ACA06B551480. Any ideas why I can't get a response from Excel?

True. It obviously needs someone with a clever UDF then, Rick. :-)
--
David Biddulph

Rick Rothstein wrote:
I get a #NUM! error for a value of '123456789012345678 with your
formula.

"David Biddulph" groups [at] biddulph.org.uk wrote in message
...
I said below:
"Doubtless with a more complicated segmentation of the text strings,
you could get the correct answer."

One possibility which seems to work is
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(INT(A1/10000),2^32)*10000+RIGHT(A1,4))
--
David Biddulph

David Biddulph wrote:
Did you read Excel Help for DEC2HEX?
"If number -549,755,813,888 or if number 549,755,813,887,
DEC2HEX returns the #NUM! error value."

Remember also that Excel numbers are stored to a precision of 15
significant figures.

If you have your 18 digits as a string in A1 you can get an
*approximation* to the result by breaking it up, such as
=DEC2HEX(A1/2^32)&DEC2HEX(MOD(--A1,2^32))
That gives 3B9ACA06B5510E0

If you convert it back, again breaking it up to manageable chunks,
=HEX2DEC(LEFT(A2,LEN(A2)-8))*2^32+HEX2DEC(RIGHT(A2,8))
gives 268435457800737000

If you look at your result, you will realise that 3B9ACA06B551480
cannot be an accurate answer, as with zero at the end it must be a
multiple of 16, which 268435457800737937 isn't.

Doubtless with a more complicated segmentation of the text strings,
you could get the correct answer. For what it's worth, the Windows
calculator gives 3B9ACA06B551491

Toria wrote:
Hello,

When I have this number, 268435457800737937, Excel gives me an
error when I do the DEC2HEX formula. In an online converter, I get
3B9ACA06B551480. Any ideas why I can't get a response from Excel?