Converting a Hexadecimal with a length of 14 to Hexadecimal

[Wallcop]

Dear All,

I am trying to convert a hexadecimal string eg <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>EAAA863573781B which should return <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>66052637949392923

However with the character limit of the function =HEX2DEC I used =HEX2DEC(LEFT(C2,7))*2^28+HEX2DEC(RIGHT(C2,7))
Where C1 has the original hexadecimal string but it never returns the last two digits instead providing
66052637949392900. Can anyone help me get a clean return that I can use?

 

[Wallcop]

C2*

 

[mikerickson]

Excel numbers are to only to 15 decimal places. Its not a Hex2Dec problem, its that the result is larger than Excel is accurate.

 

[Wallcop]

Excel numbers are to only to 15 decimal places. Its not a Hex2Dec problem, its that the result is larger than Excel is accurate.

Thank you for your response, do you have any suggestions or alternatives in or out of Excel to do this calculation and grab that number. I am assuming that user inputed numbers which are outside of a calculation don't have that limit.

 

[mikerickson]

Your method is as accurate as Excel is going to get. (Unless you write some vicious code that does arithmetic alphabetically)

 

[Wallcop]

Thank you for your help! I'll see if I can cook something up.

 

[joeu2004]

I am assuming that user inputed numbers which are outside of a calculation don't have that limit.

Yes, they do. "Numbers" with more than 15 significant digits should be entered as strings.

If it is okay for the hex-to-decimal conversion to result in a string and you are okay with using VBA, you can use the following VBA function.

Function myhex2dec(h As String) As String
Dim wf As Variant
Set wf = WorksheetFunction
myhex2dec = Format(CDec(wf.Hex2Dec(Left(h, 7))) * 2 ^ 28 + wf.Hex2Dec(Right(h, 7)), "0")
End Function

=myhex2dec("EAAA863573781B") returns "66052637949392923".
=myhex2dec("FFFFFFFFFFFFFF") returns "72057594037927935".

You can reference those "numbers" in arithmetic expressions. But Excel interprets only the first 15 significant digits, replacing any digits to the right with zero.