Check out some similar questions!

sapl_info · Sep 15, 2009, 03:06 AM

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.

colbtech · Sep 15, 2009, 04:48 AM

=HEX2DEC(target cell) is a built in function. Will this do?

sapl_info · 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.

colbtech · 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?

sapl_info · 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

JBeaucaire · 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.

JBeaucaire · Sep 18, 2009, 08:58 AM

See here for the full set of floating point tools from shg:
What's in Your Personal.xls? - Floating Point Tools

sapl_info · Sep 26, 2009, 03:37 AM

Originally Posted by JBeaucaire

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.
Is there any code for converting the ASCII to decimal in EXCEL.

sapl_info · 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.

JBeaucaire · 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.

nigel_cro · 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

JBeaucaire · 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.

nigel_cro · 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

JBeaucaire · 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)

shanemacdonald · 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?

shanemacdonald · 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

JBeaucaire · 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.

shanemacdonald · Dec 23, 2009, 01:11 PM

I saved, closed and reopened Excel - all seems good.

Thanks,

Shane

kishorekumar88 · Dec 8, 2010, 06:46 AM

How to convert from Float number to 32 bit Hex ?

besjon · Apr 25, 2011, 02:08 AM

How to convert this hexadecimal code 2fb2644b98dd3c99be2661cd41ca2f11 in letters please?