Convert large 18 digit integer numbers to Hexadecimal number

M

manikandan23

New Member
Joined
Sep 6, 2017
Messages
3
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)
[TABLE="width: 139"]
<colgroup><col></colgroup><tbody>[TR]
[TD]256691622700551235[/TD]
[/TR]
[TR]
[TD]256691622700554551[/TD]
[/TR]
[TR]
[TD]256691622700554513[/TD]
[/TR]
[TR]
[TD]256691622700553880[/TD]
[/TR]
[TR]
[TD]256691622700553570[/TD]
[/TR]
[TR]
[TD]256691622700553001[/TD]
[/TR]
[TR]
[TD]256691622700169272[/TD]
[/TR]
[TR]
[TD]256691622700554824[/TD]
[/TR]
[TR]
[TD]256691622700555366[/TD]
[/TR]
[TR]
[TD]256691622700553315[/TD]
[/TR]
[TR]
[TD]256691622700554353[/TD]
[/TR]
[TR]
[TD]256691622700627288[/TD]
[/TR]
[TR]
[TD]256691622700550435[/TD]
[/TR]
[TR]
[TD]256691622700554581[/TD]
[/TR]
[TR]
[TD]256691622700554614[/TD]
[/TR]
[TR]
[TD]256691622700615734[/TD]
[/TR]
[TR]
[TD]256691622700410773[/TD]
[/TR]
[TR]
[TD]256691622700590150[/TD]
[/TR]
[TR]
[TD]256691622700590217[/TD]
[/TR]
[TR]
[TD]256691622700590725[/TD]
[/TR]
[TR]
[TD]256691622700590695[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in Advance!
Mani A
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
more than 15 digits is not a number as that exceeds the precision of Excel.
 
Upvote 0
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.
Code: 
[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]
 
Last edited:
Upvote 0
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.
Code: 
[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]
 
Upvote 0
manikandan23 said:
Thank you so much!
How do I incorporate this into my spreadsheet and make use of this function? Could you please help on that?
Click to expand...
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.
 
Upvote 0
Thank you so much Rick! That was quick and easy! Really appreciate your help on this.

Rick Rothstein said:
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.
Click to expand...
 
Upvote 0
Hi

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

Code: 
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
 
Upvote 0
pgc01 said:
Hi

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

Code: 
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
Click to expand...
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...
Code: 
[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]
 
Upvote 0
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:

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

Anyway, Rick's udf is simpler and should work fine for you.
 
Upvote 0
Rick Rothstein said:
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...
Code: 
[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]
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
index match excel formula to vba code
Replies
9
Views
365
DanteAmor
DanteAmor
K0st4din
  • Solved
Macros Serial number generation with repeatability comparison base
2 3
Replies
20
Views
853
StephenCrump
S
M
  • Question Question
using Large when the cell has more than 15 digits
Replies
1
Views
529
Fluff
Fluff
B
  • Question Question
VBA to edit/update cells within a large table from a common "Dashboard"
Replies
1
Views
503
brianv
B
G
  • Question Question
Converting a ASCII text file with strange delimiters.
2
Replies
14
Views
1K
felixstraube
F

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top