Formula for converting 32 bit Hexadecimal into decimal in EXCEL.
I required a formula in the mSEXCEL for cinverting IEEE-754 32bit hexadecimal floating point representation into decimal floating point.
|
Formula for converting 32 bit Hexadecimal into decimal in EXCEL.
I required a formula in the mSEXCEL for cinverting IEEE-754 32bit hexadecimal floating point representation into decimal floating point.
=HEX2DEC(target cell) is a built in function. Will this do?
No this formula is not help ful. It can conert only 4 digit hex number only. I required 8 digit hex number to be converted in to decimal. For eg: 4224A012, the number is an 32 bit hex representation. I required the conversion of the number in to decimal floating point.Quote:
Originally Posted by colbtech
In Excel 2007, the hex2dec function will convert an 8 digit number.
Enter 4224A012 into cell A1, in cell B1 enter the formula =HEX2DEC(A1) and the answer is 1109696530. In cell C1 enter the formula =DEC2HEX(B1) and the answer is 4224A012.
Is this not what you require?
Quote:
Originally Posted by colbtech
The answer for 4224A012 hex in decimal is 41.1. The answer is extracted by using "IEEE-754 Floating-Point Conversion From 32-bit Hexadecimal Representation To Decimal Floating-Point". I required the "IEEE-754 Floating-Point Conversion From 32-bit Hexadecimal Representation To Decimal Floating-Point" in the EXCEL
This is not built into Excel, but it can be added as a User Defined Function. Here is a family of functions that need to be installed together, one of which that does the job:
How to install the User Defined Function:Code:Option Explicit
' shg 2008-0919, 2009-0517 (minor changes)
' Routine Input Output WF/VBA
' -------- -------------------- -------------------------- ------
' Byte2Sng ab(0 To 3) Single VBA
' Sng2Byte Single 4-byte array Variant Both
' Sng2Hex Single Hex String Both
' Hex2Sng Hex string Single Both
' Var2Sng 4-byte array Variant Single Both
' Routine Input Output WF/VBA
' -------- -------------------- -------------------------- ------
' Byte2Dbl ab(0 To 7) Double VBA
' Dbl2Byte Double 8-byte array Variant Both
' Dbl2Hex Double Hex string Both
' Hex2Dbl Hex string Double Both
' Var2Dbl 8-byte array Variant Double Both
' Routine Input Output WF/VBA
' -------- -------------------- -------------------------- ------
' Flt2Byte Single or Double 4- or 8-byte array Variant VBA
' Byte2Hex Byte array Hex string VBA
'===============================================================================
' User-defined data types
' (Necessary because that's the only way LSET works)
'===============================================================================
Type uab4: ab(0 To 3) As Byte: End Type
Type uab8: ab(0 To 7) As Byte: End Type
Type uFlt: f As Single: End Type
Type uDbl: d As Double: End Type
'===============================================================================
' Functions for Singles (church dances)
'===============================================================================
Function Byte2Sng(ab() As Byte) As Single
' Returns the conversion of
' the Big-Endian, 0-based, 4-byte array ab to a Single
' SNaN will cause overflow (as it should)
' VBA function only
Dim ub As uab4
Dim uf As uFlt
' put the bytes in Little-Endian order
ub.ab(3) = ab(0)
ub.ab(2) = ab(1)
ub.ab(1) = ab(2)
ub.ab(0) = ab(3)
' copy the bytes into the float
LSet uf = ub
Byte2Sng = uf.f
End Function
Function Sng2Byte(f As Single) As Variant
' Returns the conversion of Single f to
' a Big-Endian, 0-based, 4-byte array in the variant
' Worksheet function or VBA
Dim ab(0 To 3) As Byte
Dim ub As uab4
Dim uf As uFlt
Dim i As Long
uf.f = f
' copy the single into the byte array
LSet ub = uf
' put the bytes in Big-Endian order
For i = 0 To 3
ab(i) = ub.ab(3 - i)
Next i
Sng2Byte = ab
End Function
Function Var2Sng(v As Variant) As Single
' Returns the conversion of
' the Big-Endian, 1-based, 4-byte array in v to a Single
' Worksheet function or VBA
Dim ub As uab4
Dim uf As uFlt
Dim i As Long
' put the bytes in Little-Endian order
For i = 0 To 3
ub.ab(i) = v(4 - i)
Next i
' copy the bytes into the float
LSet uf = ub
Var2Sng = CSng(uf.f)
End Function
Function Sng2Hex(f As Single) As String
' Returns the conversion of float f to a hex string
' Worksheet function or VBA
Const sPad As String = "0"
Dim uf As uFlt
Dim ub As uab4
Dim i As Long
uf.f = f
LSet ub = uf
For i = 0 To 3
Sng2Hex = Right(sPad & Hex(ub.ab(i)), 2) & " " & Sng2Hex
Next i
Sng2Hex = Left(Sng2Hex, Len(Sng2Hex) - 1)
End Function
Function Hex2Sng(ByVal s As String) As Single
' Converts hex string s to a Single
' Worksheet function or VBA
Const sPad As String = "00000000"
Dim i As Long
Dim ub As uab4
Dim ab(0 To 3) As Byte
s = Replace(s, " ", "")
If Len(s) > 8 Then Exit Function
If Len(s) < 8 Then s = Right(sPad & s, 8)
For i = 0 To 3
ab(i) = CByte("&H" & Mid(s, 2 * i + 1, 2))
Next i
Hex2Sng = Byte2Sng(ab)
End Function
'===============================================================================
' Functions for Doubles (swap meets)
'===============================================================================
Function Byte2Dbl(ab() As Byte) As Double
' Returns the conversion of
' the Big-Endian, 0-based, 8-byte array ab to a Double
' VBA function only
Dim ub As uab8
Dim ud As uDbl
Dim i As Long
' put the bytes in Little-Endian order
For i = 0 To 7
ub.ab(7 - i) = ab(i)
Next i
' copy the bytes into the double
LSet ud = ub
Byte2Dbl = ud.d
End Function
Function Var2Dbl(v As Variant) As Double
' Returns the conversion of
' the Big-Endian, 1-based, 8-byte array in v to a Double
' Worksheet function or VBA
Dim ub As uab8
Dim ud As uDbl
Dim i As Long
' put the bytes in Little-Endian order
For i = 1 To 8
ub.ab(8 - i) = v(i)
Next i
' copy the bytes into the double
LSet ud = ub
Var2Dbl = ud.d
End Function
Function Dbl2Byte(d As Double) As Variant
' Returns the conversion of Double d to
' a Big-Endian, 0-based, 8-byte array in the variant
' Worksheet function or VBA
Dim ab(0 To 7) As Byte
Dim ub As uab8
Dim ud As uDbl
Dim i As Long
ud.d = d
LSet ub = ud
' output the bytes in Big-Endian order
For i = 0 To 7
ab(i) = ub.ab(7 - i)
Next i
Dbl2Byte = ab
End Function
Function Dbl2Hex(d As Double) As String
' Returns the conversion of Double d to a hex string
' Worksheet function or VBA
Const sPad As String = "0"
Dim ud As uDbl
Dim ub As uab8
Dim i As Long
ud.d = d
LSet ub = ud
For i = 0 To 7
Dbl2Hex = Right(sPad & Hex(ub.ab(i)), 2) & " " & Dbl2Hex
Next i
Dbl2Hex = Left(Dbl2Hex, Len(Dbl2Hex) - 1)
End Function
Function Hex2Dbl(ByVal sInp As String) As Double
' Converts hex string sInp to a Double
' Worksheet function or VBA
Const sPad As String = "0000000000000000"
Dim i As Long
Dim ub As uab8
Dim ab(0 To 7) As Byte
sInp = Replace(sInp, " ", "")
If Len(sInp) > 16 Then Exit Function
If Len(sInp) < 16 Then sInp = Right(sPad & sInp, 16)
For i = 0 To 7
ab(i) = CByte("&H" & Mid(sInp, 2 * i + 1, 2))
Next i
Hex2Dbl = Byte2Dbl(ab)
End Function
'===============================================================================
' Functions for Either (metrosexuals)
'===============================================================================
Function Flt2Byte(flt As Variant) As Variant
' Returns the conversion of flt to
' a Big-Endian, 0-based, 4- or 8-byte array Variant
' VBA function only
Dim ab8(0 To 7) As Byte
Dim ub8 As uab8
Dim ud As uDbl
Dim ab4(0 To 3) As Byte
Dim ub4 As uab4
Dim uf As uFlt
Dim i As Long
Select Case VarType(flt)
Case vbSingle
uf.f = flt
' copy the single into the byte array
LSet ub4 = uf
' put the bytes in Big-Endian order
For i = 0 To 3
ab4(i) = ub4.ab(3 - i)
Next i
Flt2Byte = ab4
Case vbDouble
ud.d = flt
' copy the single into the byte array
LSet ub8 = ud
' put the bytes in Big-Endian order
For i = 0 To 7
ab8(i) = ub8.ab(7 - i)
Next i
Flt2Byte = ab8
Case Else
Flt2Byte = CVErr(xlErrValue)
End Select
End Function
Function Byte2Hex(ab() As Byte) As String
' Converts ab to a hex string
' VBA function only
Dim i As Long
For i = LBound(ab) To UBound(ab)
Byte2Hex = Byte2Hex & Hex(ab(i)) & " "
Next i
Byte2Hex = Left(Byte2Hex, Len(Byte2Hex) - 1)
End Function
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in all the code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
The function is installed and ready to use.
=============
In A1 put: 4224A012
In B1, try your new function like so: =HEX2SNG(A1)
Answer 41.15631866
Format that cell as needed.
See here for the full set of floating point tools from shg:
What's in Your Personal.xls? - Floating Point Tools
Thanks very much. The code is working Mr. Beaucaire.Quote:
Originally Posted by JBeaucaire
Is there any code for converting the ASCII to decimal in EXCEL.
Quote:
Originally Posted by JBeaucaire
Thanks for the solutionMr. JBeaucaire.
Can you help in converting the ASCII to decoimal in EXCEL.
With an ASCII string in cell A1, try these formulas:
ASCII to Decimal:
=CODE(A1)
ASCII to Hexadecimal:
=DEC2HEX(CODE(A1))
If those don't work for you, post a more complete question with sample data/results.
JB,
At the risk of being a complete PITA - I need to take a hex string (eg D0 BF FF FF) and turn it to Little endian (eg FF FF BF D0). I have tried to adapt some of your coding from your really helpful posting above but my VB skills are, frankly, appalling and while I can work out what your script is doing, I am not having any success in adapting it.
I'm sure this is a relatively simple couple of lines but it is beyond me.
Thanks, in advance for any help,
Nigel
If your text string in A2 is shown exactly like you demonstrated above with spaces between each quad, then use this worksheet formula to reverse the order of the quads:
=RIGHT(A2,2) & MID(A2,6,4) & MID(A2,4,3) & LEFT(A2,2)
If your data does not match this exact format, click on GO ADVANCED and use the paperclip icon to upload a sample worksheet with several examples of your data and your desired results.
JB,
Thanks for the really prompt response.
I knew I would get something wrong - I would like to input the HEX string as a continuous string, it makes the inputting process a whole lot easier, to have to include spaces would build unnecessary effort and room for error into the process.
So, the actual input will look like [D0BFFFFF] and the output would look similar [FFFFBFD0]. Just to emphasise, both input and output would be in continuous strings.
I'm sorry to have wasted your time, thanks for the help.
All the best,
Nigel
Hopefully you'll pull these two examples apart and see what's going on so you can use this technique yourself in the future.
This is for reversing your text string 2 characters at a time with no spaces:
=RIGHT(A1,2) & MID(A1,5,2) & MID(A1,3,2) & LEFT(A1,2)
Can't seem to get the hextosng function to work with Office 2007. Are there any known issues with this?
My post was with regards to the formula for converting 32 bit hexadecimal into decimal in EXCEL... Cheers, Shane
Were you going to post a workbook with the function installed in it and NOT working as expected? Do so, be sure to highlight the cells with the function(s) in use so I don't have to hunt for them.
I saved, closed and reopened Excel - all seems good.
Thanks,
Shane
How to convert from Float number to 32 bit Hex ?
How to convert this hexadecimal code 2fb2644b98dd3c99be2661cd41ca2f11 in letters please?
| All times are GMT -7. The time now is 07:06 PM. |