If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Change 18-digit dec number to hex
Thank you to all who responded. This helps immensely!
"Rick Rothstein" wrote: 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? |
|
Thread Tools | |
Display Modes | |
|
|