|
|
|
|
New Member
|
|
Sep 15, 2009, 03:06 AM
|
|
Formula for converting 32 bit Hexadecimal into decimal in EXCEL
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.
|
|
|
Senior Member
|
|
Sep 15, 2009, 04:48 AM
|
|
=HEX2DEC(target cell) is a built in function. Will this do?
|
|
|
New Member
|
|
Sep 15, 2009, 11:00 PM
|
|
Originally Posted by colbtech
=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.
|
|
|
Senior Member
|
|
Sep 16, 2009, 12:51 AM
|
|
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?
|
|
|
New Member
|
|
Sep 18, 2009, 01:47 AM
|
|
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?
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
|
|
|
Software Expert
|
|
Sep 18, 2009, 08:55 AM
|
|
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:
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
How to install the User Defined 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.
|
|
|
New Member
|
|
Sep 26, 2009, 03:37 AM
|
|
Originally Posted by JBeaucaire
Thanks very much. The code is working Mr. Beaucaire.
Is there any code for converting the ASCII to decimal in EXCEL.
|
|
|
New Member
|
|
Sep 26, 2009, 03:47 AM
|
|
Originally Posted by JBeaucaire
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:
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
How to install the User Defined 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.
Thanks for the solutionMr. JBeaucaire.
Can you help in converting the ASCII to decoimal in EXCEL.
|
|
|
Software Expert
|
|
Sep 26, 2009, 10:43 AM
|
|
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.
|
|
|
New Member
|
|
Dec 3, 2009, 08:17 AM
|
|
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
|
|
|
Software Expert
|
|
Dec 3, 2009, 11:17 AM
|
|
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.
|
|
|
New Member
|
|
Dec 3, 2009, 01:53 PM
|
|
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
|
|
|
Software Expert
|
|
Dec 3, 2009, 05:19 PM
|
|
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)
|
|
|
New Member
|
|
Dec 23, 2009, 12:06 PM
|
|
Can't seem to get the hextosng function to work with Office 2007. Are there any known issues with this?
|
|
|
New Member
|
|
Dec 23, 2009, 12:08 PM
|
|
My post was with regards to the formula for converting 32 bit hexadecimal into decimal in EXCEL... Cheers, Shane
|
|
|
Software Expert
|
|
Dec 23, 2009, 12:33 PM
|
|
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.
|
|
|
New Member
|
|
Dec 23, 2009, 01:11 PM
|
|
I saved, closed and reopened Excel - all seems good.
Thanks,
Shane
|
|
|
New Member
|
|
Dec 8, 2010, 06:46 AM
|
|
How to convert from Float number to 32 bit Hex ?
|
|
|
New Member
|
|
Apr 25, 2011, 02:08 AM
|
|
How to convert this hexadecimal code 2fb2644b98dd3c99be2661cd41ca2f11 in letters please?
|
|
Question Tools |
Search this Question |
|
|
Check out some similar questions!
Excel formula
[ 15 Answers ]
Download Attachment, first.
I need:
-If B14=empty, then A and D colones both empty
-If only B14=selected, then A14=TREZOR, D14=1, D15=1, bold underline between C14-L14 and C15-L15, others A and D empty
-If B14=selected and B15=selected, then A14=TREZOR, A15=-II-, D14=1, D15=1, D16=2,...
I need a excel formula
[ 2 Answers ]
Hi
I need a excel formula for numbers ( Figures) are convert in words.
Like Rs.20 =Twenty only( In Formula) up to 15 digit.
Thanks
Regards
Hariom
Excel formula
[ 3 Answers ]
I am using Excel 2007.
I have 2 Sheets.
In first sheet there are 3 coloumns, first one contains the employes number, second coloumn contains the name of employes and third one contains the department he works in.
In the second sheet I have drop down list of the names of employes and the...
Excel Formula
[ 1 Answers ]
Hi
I have come across another problem with my spreadsheet. I have tried quite a few different ways to do it but can't get any of them to work.
Within my spreadsheet I have a quote price and a forcasted date. However when a order is definitely received then a sale price and a new date is put into...
Excel formula
[ 3 Answers ]
I have a column that you manually enter a number; either 0 (zero) or any number greater than zero.
I have another column with the formula of: =DAYS360(AN22,AQ22)
If the resulting formula figure/number is greater than the manually entered number then I want a third column to equal: $0.00
But...
View more questions
Search
|