



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 IEEE754 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 "IEEE754 FloatingPoint Conversion From 32bit Hexadecimal Representation To Decimal FloatingPoint". I required the "IEEE754 FloatingPoint Conversion From 32bit Hexadecimal Representation To Decimal FloatingPoint" 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 20080919, 20090517 (minor changes)
' Routine Input Output WF/VBA
'    
' Byte2Sng ab(0 To 3) Single VBA
' Sng2Byte Single 4byte array Variant Both
' Sng2Hex Single Hex String Both
' Hex2Sng Hex string Single Both
' Var2Sng 4byte array Variant Single Both
' Routine Input Output WF/VBA
'    
' Byte2Dbl ab(0 To 7) Double VBA
' Dbl2Byte Double 8byte array Variant Both
' Dbl2Hex Double Hex string Both
' Hex2Dbl Hex string Double Both
' Var2Dbl 8byte array Variant Double Both
' Routine Input Output WF/VBA
'    
' Flt2Byte Single or Double 4 or 8byte array Variant VBA
' Byte2Hex Byte array Hex string VBA
'===============================================================================
' Userdefined 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 BigEndian, 0based, 4byte 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 LittleEndian 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 BigEndian, 0based, 4byte 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 BigEndian 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 BigEndian, 1based, 4byte 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 LittleEndian 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 BigEndian, 0based, 8byte array ab to a Double
' VBA function only
Dim ub As uab8
Dim ud As uDbl
Dim i As Long
' put the bytes in LittleEndian 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 BigEndian, 1based, 8byte 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 LittleEndian 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 BigEndian, 0based, 8byte 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 BigEndian 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 BigEndian, 0based, 4 or 8byte 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 BigEndian 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 BigEndian 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 20080919, 20090517 (minor changes)
' Routine Input Output WF/VBA
'    
' Byte2Sng ab(0 To 3) Single VBA
' Sng2Byte Single 4byte array Variant Both
' Sng2Hex Single Hex String Both
' Hex2Sng Hex string Single Both
' Var2Sng 4byte array Variant Single Both
' Routine Input Output WF/VBA
'    
' Byte2Dbl ab(0 To 7) Double VBA
' Dbl2Byte Double 8byte array Variant Both
' Dbl2Hex Double Hex string Both
' Hex2Dbl Hex string Double Both
' Var2Dbl 8byte array Variant Double Both
' Routine Input Output WF/VBA
'    
' Flt2Byte Single or Double 4 or 8byte array Variant VBA
' Byte2Hex Byte array Hex string VBA
'===============================================================================
' Userdefined 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 BigEndian, 0based, 4byte 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 LittleEndian 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 BigEndian, 0based, 4byte 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 BigEndian 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 BigEndian, 1based, 4byte 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 LittleEndian 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 BigEndian, 0based, 8byte array ab to a Double
' VBA function only
Dim ub As uab8
Dim ud As uDbl
Dim i As Long
' put the bytes in LittleEndian 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 BigEndian, 1based, 8byte 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 LittleEndian 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 BigEndian, 0based, 8byte 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 BigEndian 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 BigEndian, 0based, 4 or 8byte 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 BigEndian 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 BigEndian 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 C14L14 and C15L15, 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
