What a mission! But for anybody interested I got there, ending up with a custom LAMBDA function HexToFloat() that was built without the need for VBA. The LAMBDA function and several other functions used are new in Excel 365 - if you're getting errors reproducing the functions below you might have an older version.
The Wikipedia page Single-precision floating-point format was my primary reference, in particular the section Converting binary32 to decimal.
Read the article for a full understanding (good luck!!), but for TLDR it's worth noting that there is an implied bit that is not actually stored in memory and when combined with the fraction component this is referred to as the "significand".
For my solution I had to build several supporting LAMBDA functions in Excel's Name Manager. In my case the data was stored as Little-Endian so if this is not applicable in your case you can skip that step.
| Name | Comment |
|---|---|
| LittleEndianHex | Interpret Hex data as Little Endian by reversing it in 2-byte chunks |
=LAMBDA(HexData,MID(HexData,7,2) & MID(HexData,5,2) & MID(HexData,3,2) & MID(HexData,1,2))
| Name | Comment |
|---|---|
| HEXtoBIN | Handles bigger numbers than the native Excel HEX2BIN function |
=LAMBDA(number,[places],LET(Unpadded,REDUCE("",HEX2BIN(MID(number,SEQUENCE(LEN(number)),1),4),LAMBDA(result,byte,result & byte)),REPT("0",IF(ISOMITTED(places),0,places-LEN(Unpadded))) & Unpadded))
| Name | Comment |
|---|---|
| BINtoDEC | Handles bigger numbers than the native Excel BIN2DEC function |
=LAMBDA(E,SUMPRODUCT(MID("0"&E,ROW(INDIRECT("1:"&LEN("0"&E))),1)*2^(LEN("0"&E)-ROW(INDIRECT("1:"&LEN("0"&E))))))
| Name | Comment |
|---|---|
| HexToFloat | Convert hexadecimal representation of little-endian IEEE 754 binary32 (4 bytes) number to Single-precision floating-point format |
=LAMBDA(HexData,LET(LEHex,LittleEndianHex(HexData),Binary,HEXtoBIN(LEHex,32),bSign,LEFT(Binary,1),bExponent,MID(Binary,2,8),bImplicit,IF(bExponent=REPT("0",8),"0","1"),bSignificand,bImplicit & RIGHT(Binary,23),dSign,BIN2DEC(bSign),dExponent,BINtoDEC(bExponent),dSignificand,BINtoDEC(bSignificand),(-1)^dSign*(dSignificand*2^-23)*2^(dExponent-127)))
Once you've done all this you can enter the HexToFloat formula directly into a cell, e.g. =HexToFloat(A1) or =HexToFloat("d162c240"). This particular example returns the result 6.07456254959106.
(PS I've never asked for votes before but this took me weeks! If you find it useful please consider giving me an up-tick.)
Answer from Wayne Ivory on Stack OverflowVideos
What a mission! But for anybody interested I got there, ending up with a custom LAMBDA function HexToFloat() that was built without the need for VBA. The LAMBDA function and several other functions used are new in Excel 365 - if you're getting errors reproducing the functions below you might have an older version.
The Wikipedia page Single-precision floating-point format was my primary reference, in particular the section Converting binary32 to decimal.
Read the article for a full understanding (good luck!!), but for TLDR it's worth noting that there is an implied bit that is not actually stored in memory and when combined with the fraction component this is referred to as the "significand".
For my solution I had to build several supporting LAMBDA functions in Excel's Name Manager. In my case the data was stored as Little-Endian so if this is not applicable in your case you can skip that step.
| Name | Comment |
|---|---|
| LittleEndianHex | Interpret Hex data as Little Endian by reversing it in 2-byte chunks |
=LAMBDA(HexData,MID(HexData,7,2) & MID(HexData,5,2) & MID(HexData,3,2) & MID(HexData,1,2))
| Name | Comment |
|---|---|
| HEXtoBIN | Handles bigger numbers than the native Excel HEX2BIN function |
=LAMBDA(number,[places],LET(Unpadded,REDUCE("",HEX2BIN(MID(number,SEQUENCE(LEN(number)),1),4),LAMBDA(result,byte,result & byte)),REPT("0",IF(ISOMITTED(places),0,places-LEN(Unpadded))) & Unpadded))
| Name | Comment |
|---|---|
| BINtoDEC | Handles bigger numbers than the native Excel BIN2DEC function |
=LAMBDA(E,SUMPRODUCT(MID("0"&E,ROW(INDIRECT("1:"&LEN("0"&E))),1)*2^(LEN("0"&E)-ROW(INDIRECT("1:"&LEN("0"&E))))))
| Name | Comment |
|---|---|
| HexToFloat | Convert hexadecimal representation of little-endian IEEE 754 binary32 (4 bytes) number to Single-precision floating-point format |
=LAMBDA(HexData,LET(LEHex,LittleEndianHex(HexData),Binary,HEXtoBIN(LEHex,32),bSign,LEFT(Binary,1),bExponent,MID(Binary,2,8),bImplicit,IF(bExponent=REPT("0",8),"0","1"),bSignificand,bImplicit & RIGHT(Binary,23),dSign,BIN2DEC(bSign),dExponent,BINtoDEC(bExponent),dSignificand,BINtoDEC(bSignificand),(-1)^dSign*(dSignificand*2^-23)*2^(dExponent-127)))
Once you've done all this you can enter the HexToFloat formula directly into a cell, e.g. =HexToFloat(A1) or =HexToFloat("d162c240"). This particular example returns the result 6.07456254959106.
(PS I've never asked for votes before but this took me weeks! If you find it useful please consider giving me an up-tick.)
Short answer = DEC2HEX('Your reference cell or value')
Your formula shifts places of low and high byte of when you extract the hex value from your string. Hex is just like all other position based numbers, where the lowest value is found on the right side.
This person explain working with hex values in excel
Debug of your formula
You can use multipe stages in one formula like:
=DEC2HEX(SUM(HEX2DEC("c240");HEX2DEC("d162")))
Note: If you use US settings in Office replace ; with ,
In Python 3:
>>> import struct
>>> struct.unpack('!f', bytes.fromhex('41973333'))[0]
18.899999618530273
>>> struct.unpack('!f', bytes.fromhex('41995C29'))[0]
19.170000076293945
>>> struct.unpack('!f', bytes.fromhex('470FC614'))[0]
36806.078125
In Python 2:
>>> import struct
>>> struct.unpack('!f', '41973333'.decode('hex'))[0]
18.899999618530273
>>> struct.unpack('!f', '41995C29'.decode('hex'))[0]
19.170000076293945
>>> struct.unpack('!f', '470FC614'.decode('hex'))[0]
36806.078125
I recommend using the ctypes module which basically lets you work with low level data types. In your case you could say
from ctypes import *
def convert(s):
i = int(s, 16) # convert from hex to a Python int
cp = pointer(c_int(i)) # make this into a c integer
fp = cast(cp, POINTER(c_float)) # cast the int pointer to a float pointer
return fp.contents.value # dereference the pointer, get the float
print convert("41973333") # returns 1.88999996185302734375E1
print convert("41995C29") # returns 1.91700000762939453125E1
print convert("470FC614") # returns 3.6806078125E4
I believe that the ctypes module makes sense here, because you're essentially asking how to perform low-level bit casting. Your question is basically, how do I tell Python to take some data and interpret that data as if those exact same bits were a different data type?
In C if you had an int and wanted to interpret its bits as a float, you'd do roughly the same thing, taking a pointer and then casting and dereferencing it:
int i = 0x41973333;
float f = *((float*)&i);
and that's exactly what the Python code using the ctypes library is doing in my example.
The safe way:
unsigned char b[sizeof(float)];
float f;
memcpy(&f, b, sizeof(f));
it is very likely that good compiler will optimize out the call to the memcpy and this method will be very efficient.
gcc safe way
union {
unsigned char b[sizeof(float)];
float f;
}a = {.b = {0xa1, 0xb2, 0xc3, 0xc4}};
printf("%f" , a.f);
or pointer punning as in the @AnttiHaapala answer
If the target architecture has float32 as the representation, and it is of same endianness as the source, then as simple as:
float f;
unsigned char *b = (unsigned char *)&f;
b[0] = 0xa1;
b[1] = 0xb2;
b[2] = 0xc3;
b[3] = 0xd4;
printf("f is %f\n", f);
If the data source and your target architecture have little/big endianness mismatch, just reverse the indices to 3 ... 0. If you have some other weird legacy endianness, I pity you.
Note that you can use memcpy - for which you would need to use an auxiliary buffer and not just something that iterates bytes; or an union which would be more code, and is not necessarily C++-compatible.
From this page on MSDN "How to: Convert Between Hexadecimal Strings and Numeric Types (C# Programming Guide)".
string hexString = "43480170";
uint num = uint.Parse(hexString, System.Globalization.NumberStyles.AllowHexSpecifier);
byte[] floatVals = BitConverter.GetBytes(num);
float f = BitConverter.ToSingle(floatVals, 0);
Console.WriteLine("float convert = {0}", f);
// Output: 200.0056
Something like this:
byte[] bytes = BitConverter.GetBytes(0x08fdc941);
if (BitConverter.IsLittleEndian)
{
bytes = bytes.Reverse().ToArray();
}
float myFloat = BitConverter.ToSingle(bytes, 0);
If python3 is your option, please try the following:
echo "436a508c" | python3 -c 'import struct; print(struct.unpack("!f", bytes.fromhex(input()))[0])'
Yields:
234.31463623046875
Hope this helps.
With GNU Debugger:
gdb --batch -ex "print/f (float *) 0x436A508C"
Output:
$1 = 234.314636
Source: Using the linux command line in bash as a programmer's calculator