[Hex to float Conversion]

[appdev]

Hello, I hope you're doing well.

I'm a new member.

I was wondering how to convert a hexadecimal to floating point in excel.

Can someone help me please ?

I used Hex2DEC but that didn't return a floating point.

Thank you.

 

[6StringJazzer]

What are you starting with as your hex string? Do you have a hexadecimal point in your string? HEX2DEC only deals with integers.

Here is a VBA function that will return a real number when the hex string has a hex point in it.

Function HEX2DECFloat(S As String) As Double

   Dim i As Long
   Dim Exp As Double
   
   If InStr(S, ".") = 0 Then
      HEX2DECFloat = CDbl("&H" & S)
   Else
      ' Get the integer part
      HEX2DECFloat = CDbl("&H" & Left(S, InStr(S, ".") - 1))
   
      ' Add the decimal part
      Exp = 16
      For i = InStr(S, ".") + 1 To Len(S)
         HEX2DECFloat = HEX2DECFloat + CDbl("&H" & Mid(S, i, 1)) / Exp
         Exp = Exp * 16
      Next i
      
   End If

End Function

This could be done with formulas but would be rather complicated.

 

[6StringJazzer]

Just for completeness, here is one that goes the other direction. Because a hex fraction may be not be able to be represented exactly in a decimal number, this function includes an optional precision argument. The conversion stops once the hex number is below the decimal number + precision. Otherwise you could get hex numbers that look like

FF.FF000000000000000000000000000000001

Function DEC2HEXFloat(D As Double, Optional Precision = 0.000000000000001) As String

   Dim Exp As Double
   Dim Resolved As Long
   Dim ResolvedH As String
   Dim DecRemainder As Double
   
   If D = Int(D) Then
      DEC2HEXFloat = Hex(D)
   Else
      ' Get the integer part
      DEC2HEXFloat = Hex(Int(D)) & "."
   
      ' Add the decimal part
      DecRemainder = D - Int(D)
      Exp = 16
      
      Do Until DecRemainder < Precision
      
         Resolved = Int(DecRemainder * Exp)
         ResolvedH = Hex(Resolved)
         DEC2HEXFloat = DEC2HEXFloat & ResolvedH
         
         DecRemainder = DecRemainder - Resolved / Exp
         
         Exp = Exp * 16
         
      Loop
      
   End If

End Function