How can I convert four bytes of hexadecimal into a floating point number within Excel

Question

I have copied a stream of hexadecimal data from a Wireshark capture into Excel as a text value with no spaces.

04000000ffffffff2b010000c900000000000000000000000000000000000000

I know how to convert a section of this to an integer in Excel using the formula:

=HEX2DEC(MID(B173,19,2) & MID(B173,17,2))

which in this case returns 299.

But how do I do something similar to retrieve a float? Some articles I've found discuss writing a C# program but I'm just trying to setup a little debug environment in Excel. Other articles only seem to discuss doing the conversion in the other direction but I couldn't figure out the inverse procedure.

Edit: An example of what I am after is:

=SOMEFUNCTION("d162c240") --> 6.075

or possibly

=SOMEFUNCTION("c240" & "d162") --> 6.075

Answer 1

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.)