I am trying to create a excel formulae to find the hexa values from each bits. I have a excel sheet like below image
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?
2
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))
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
)
)
8
There already is an answer on how to bin2dec for numbers longer than 10 bits in excel so it should be easy enough to adapt it for your needs. If you have Excel 365:
=LET(
string,TEXTJOIN("", TRUE, A2:AF2),
dec,SUMPRODUCT(--MID(string,LEN(string)+1-ROW(INDIRECT("1:"&LEN(string))),1),(2^(ROW(INDIRECT("1:"&LEN(string)))-1))),
DEC2HEX(dec))
Or using more of Excel 365:
=LET(
string, TEXTJOIN("", TRUE, A2:AF2),
len, LEN(string),
dec, SUMPRODUCT(--MID(string, SEQUENCE(len, 1, len, -1), 1), 2^(SEQUENCE(len, 1, 0))),
DEC2HEX(dec))
If you don’t have Excel 365 you can use a helper cell.