Building a more powerful BIN2HEX function

Question

1. Excel 4 introduced the BIN2HEX function in the Analysis ToolPac.  =BIN2HEX(A1) where A1 contains the binary number. This function has an upper limit of: 111111111 for its binary argument which returns the hex value 1FF
2. Using Excel 2013’s DECIMAL and BASE functions with the formula =BASE(DECIMAL(A1,2),16) 
   where A1 had the maximum binary text string of: 111111111111111111111111111111111111111 For this binary string the hex value is   7FFFFFFFFF
3. In all versions of Excel >= 2019, the introduction of the CONCAT function allowed for the following formula =CONCAT(BIN2HEX(MID(REPT("0",CEILING(LEN(A1),4)LEN(A1))&A1,1+4*(ROW(INDIRECT("1:"&CEILING(LEN(A1)/4,1)))-1),4)))
   I have tested this with a string of 8190 1’s.  This returns the string “3” followed by 2048 F’s, which matched an online converter.
4. You can shorten this formula (C) as of 2021, using SEQUENCE to:
   =CONCAT(BIN2HEX(MID(REPT("0",CEILING(LEN(A1),4)-LEN(A1))&A1,1+4*(SEQUENCE(CEILING(LEN(A1)/4,1))-1),4)))

One can ask, “why would you need to calculate the hex value of a 8190 binary string?”  This is a fair question; The challenge - to create a formula which could extend the limits of the BASE(DECIMAL formula shown above.  And this formula matches (and exceeds) the limits of one of the online calculators.

Answer 1

Hello Shane!

I can think of no other reason to do such a calculation beyond us Excel nerds having some fun and exploring array formula math.

Kevin