excelexcel-formula

How to create a Excel formula for converting Cell value to Hexadecimal number?

I am trying to create a excel formulae to find the hexa values from each bits. I have a excel sheet like below image

enter image description here

Here I have a 32-bit binary value (example: from A1 to AE1) and another 32-bit binary value (example: from A6 to AE6). My aim is to write formulas for the first hexadecimal value (32-bit binary value) and the next hexadecimal value (next 32-bit binary value). Finally, I need to get a 64-bit value by combining the first and second 32-bit values.

Result 1: Hexadecimal value (32-bit binary value) format: 0xFFFFFFFF

Result 2: Hexadecimal value (next 32-bit binary value) format: 0xFFFFFFFF

Final result: Hexadecimal value of 64-bit binary format: 0xFFFFFFFFFFFFFFFF

How can I write a simple 3 formulae to find these 3 Hexa values ? Any suggestions or example?

Solution

  • With MSB on the right

    
=CONCAT("0x", BIN2HEX(MID(CONCAT(INDEX(--A1:AF1,1,32-COLUMN(A1:AF1)+1)), SEQUENCE(, 4, 1, 8), 8), 2))

=CONCAT("0x", BIN2HEX(MID(CONCAT(INDEX(--A6:AF6,1,32-COLUMN(A6:AF6)+1)), SEQUENCE(, 4, 1, 8), 8), 2))

=CONCAT("0x", BIN2HEX(MID(CONCAT(
--INDEX(A1:AF1,1,32-COLUMN(A1:AF1)+1),
--INDEX(A6:AF6,1,32-COLUMN(A6:AF6)+1)),
SEQUENCE(, 8, 1, 8), 8), 2))

    Result MSB on the right

    With MSB on the left

    =CONCAT("0x", BIN2HEX(MID(CONCAT(--A1:AF1), SEQUENCE(, 4, 1, 8), 8), 2))

=CONCAT(
    "0x",
    BIN2HEX(
        MID(CONCAT(--A1:AF1, --A6:AF6), SEQUENCE(, 8, 1, 8), 8),
        2
    )
)

    Result