Convert large 18 digit integer numbers to Hexadecimal number

[manikandan23]

Hi, I am looking to convert large 18 digit (Product Serial Numbers - Electronic Serial Numbers) numbers to its equivalent Hexadecimal numbers.

Since my number is huge, I am getting #NUM ! error when I use DEC2HEX function.

Can anyone please suggest me how do I do that in Excel?

My data looks like below: (Only some sample ESNs I posted here. But my excel is a huge list of ESNs to be converted to its equivalent Hex value)

256691622700551235
256691622700554551
256691622700554513
256691622700553880
256691622700553570
256691622700553001
256691622700169272
256691622700554824
256691622700555366
256691622700553315
256691622700554353
256691622700627288
256691622700550435
256691622700554581
256691622700554614
256691622700615734
256691622700410773
256691622700590150
256691622700590217
256691622700590725
256691622700590695

<colgroup><col></colgroup><tbody>
</tbody>

Thanks in Advance!
Mani A

 

[SpillerBD]

more than 15 digits is not a number as that exceeds the precision of Excel.

 

[Rick Rothstein]

Assuming your 18-digit numbers are entered into cells formatted as Text (otherwise the last 3 digits will have been converted to zeroes), you can use this UDF (user defined function), which can, of course, be called from other VBA functions as well. The function handles both positive and negative values, but you will need to provide the bit size (up to a maximum of 93 bits) for negative values.

[table="width: 500"]
[tr]
	[td]Function BigDec2Hex(ByVal DecimalIn As Variant, Optional BitSize As Long = 93) As String
  Dim X As Integer, PowerOfTwo As Variant, BinaryString As String
  Const BinValues = "*0000*0001*0010*0011*0100*0101*0110*0111*1000*1001*1010*1011*1100*1101*1110*1111*"
  Const HexValues = "0123456789ABCDEF"
  DecimalIn = Int(CDec(DecimalIn))
  If DecimalIn < 0 Then
    If BitSize > 0 Then
      PowerOfTwo = 1
      For X = 1 To BitSize
        PowerOfTwo = 2 * CDec(PowerOfTwo)
      Next
    End If
    DecimalIn = PowerOfTwo + DecimalIn
    If DecimalIn < 0 Then
      BigDec2Hex = CVErr(xlErrValue)
      Exit Function
    End If
  End If
  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[/td]
[/tr]
[/table]

 

[manikandan23]

Thank you so much!
How do I incorporate this into my spreadsheet and make use of this function? Could you please help on that?

Thanks again!

Assuming your 18-digit numbers are entered into cells formatted as Text (otherwise the last 3 digits will have been converted to zeroes), you can use this UDF (user defined function), which can, of course, be called from other VBA functions as well. The function handles both positive and negative values, but you will need to provide the bit size (up to a maximum of 93 bits) for negative values.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function BigDec2Hex(ByVal DecimalIn As Variant, Optional BitSize As Long = 93) As String
  Dim X As Integer, PowerOfTwo As Variant, BinaryString As String
  Const BinValues = "*0000*0001*0010*0011*0100*0101*0110*0111*1000*1001*1010*1011*1100*1101*1110*1111*"
  Const HexValues = "0123456789ABCDEF"
  DecimalIn = Int(CDec(DecimalIn))
  If DecimalIn < 0 Then
    If BitSize > 0 Then
      PowerOfTwo = 1
      For X = 1 To BitSize
        PowerOfTwo = 2 * CDec(PowerOfTwo)
      Next
    End If
    DecimalIn = PowerOfTwo + DecimalIn
    If DecimalIn < 0 Then
      BigDec2Hex = CVErr(xlErrValue)
      Exit Function
    End If
  End If
  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[/TD]
[/TR]
</tbody>[/TABLE]

[/QUOTE]

 

[Rick Rothstein]

Thank you so much!
How do I incorporate this into my spreadsheet and make use of this function? Could you please help on that?

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use BigDec2Hex just like it was a built-in Excel function. For example,

=BigDec2Hex(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[manikandan23]

Thank you so much Rick! That was quick and easy! Really appreciate your help on this.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use BigDec2Hex just like it was a built-in Excel function. For example,

=BigDec2Hex(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[pgc01]

Hi

Another option valid for up to 16 hex digits, which is more than enough for your needs.

Function Dec2Hex0(s As String) As String
Dim v As Variant, j As Long

v = CDec(s)
For j = 1 To 4
    Dec2Hex0 = Right("   " & Hex(v - Int(v / 65536) * 65536), 4) & Dec2Hex0
    v = Int(v / 65536)
Next j
Dec2Hex0 = Replace(LTrim(Dec2Hex0), " ", "0")
End Function

 

[Rick Rothstein]

Hi

Another option valid for up to 16 hex digits, which is more than enough for your needs.

Function Dec2Hex0(s As String) As String
Dim v As Variant, j As Long

v = CDec(s)
For j = 1 To 4
    Dec2Hex0 = Right("   " & Hex(v - Int(v / 65536) * 65536), 4) & Dec2Hex0
    v = Int(v / 65536)
Next j
Dec2Hex0 = Replace(LTrim(Dec2Hex0), " ", "0")
End Function

If the numbers be converted are never negative, then here is another function which will work for whole numbers from 1 up to a maximum of 79228162514264337593543950335 (which is 29 decimal digits long) which has a maximum of up to 23 hex digits...

[table="width: 500"]
[tr]
	[td]Function DecToHex(ByVal V As Variant) As String
  Do Until V = 0
    DecToHex = Mid("0123456789ABCDEF", CDec(V) - 16 * Int(CDec(V) / 16) + 1, 1) & DecToHex
    V = Int(CDec(V) / 16)
  Loop
End Function[/td]
[/tr]
[/table]

 

[pgc01]

Hi

Just to have it correct, I did not copy the last version of my udf, the statement that trims the left 0's is not OK., should be instead:

Dec2Hex0 = Replace(LTrim(Replace(Dec2Hex0, "0", " ")), " ", "0")

Anyway, Rick's udf is simpler and should work fine for you.

 

[rdrjr]

If the numbers be converted are never negative, then here is another function which will work for whole numbers from 1 up to a maximum of 79228162514264337593543950335 (which is 29 decimal digits long) which has a maximum of up to 23 hex digits...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function DecToHex(ByVal V As Variant) As String
  Do Until V = 0
    DecToHex = Mid("0123456789ABCDEF", CDec(V) - 16 * Int(CDec(V) / 16) + 1, 1) & DecToHex
    V = Int(CDec(V) / 16)
  Loop
End Function[/TD]
[/TR]
</tbody>[/TABLE]

I was wondering how the code could be changed to give the hexdec in reverse order.
For example the hex return for 1,000 = 3E8 but the reverse needed to poke would be E8 03