Extending the range of Excel's HEX2BIN function
A. Excel 4 introduced the HEX2BIN function in the Analysis ToolPak add-in. This function has an upper limit of 1FF for its maximum upper HEX input argument which returns the binary value 111111111.
B. Using Excel 4 or later one can use the array formula: {=SUM(HEX2BIN(MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1))*10000^(LEN(A1)-ROW(OFFSET(A1,,,LEN(A1)))))}
To produce a maximum output of 111,111,111,111,111 from the input of 7FFF, based on the 15 decimal point precision limit.
C. You could also exceed the limits of formula B in Excel 4 with the following ungainly, non-array formula, a portion of which is shown below: =HEX2BIN(LEFT(A1))&IF(LEN(A1)>1,HEX2BIN(MID(A1,2,1),4),)&IF(LEN(A1)>2,HEX2BIN(MID(A1,3,1),4),)&IF(LEN(A!)>3,HEX2BIN(MID(A!,4,1),4),)&IF(LEN(A1)>4,HEX2BIN(MID(A1,5,1),4),) …
I have tested this up to the hex input of 1FFFFFFFFFFFFFF which returned a binary string of 57 1’s and exceeds the limit of the BASE/DECIMAL formula (D).
D. With the introduction of the BASE and DECIMAL functions in 2013 one could use the formula:
=BASE(DECIMAL(A1,16),2) Which will take a string decimal input of 9,007,199,254,740,991 which converts to a hex value of 1FFFFFFFFFFFFF and outputs a binary string of 51 1’s, slightly shorter than formula C.
Recommended by LinkedIn
E. With the introduction of CONCAT in 2019 you can use the following formula: =IF(A1=0,0,MID(CONCAT(HEX2BIN(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),4)),FIND("1",CONCAT(HEX2BIN(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),4))),2300))
I have tested this out to a hex input of 7 followed by 510 F’s (generated with the formula =7&REPT("F",255)&REPT("F",255)). This outputs a binary string of 2043 1’s, something we all need.
F. One can simplify the above formula (E) using the 2021 functions of SEQUENCE and LET: =LET(C,CONCAT(HEX2BIN(MID(A1,SEQUENCE(LEN(A1)),1),4)),IF(A1="0",0, MID(C,FIND("1",C),2300)))
G. With Python incorporated into Excel as of 2025 you can use the formula in A2: PY bin(int(xl("A1"),16)) And then strip the leading {PY}0b using Excel formula =MID(A2,3,2043) in A3 . Notice that Python formulas don’t begin with =. Since we know the length of the output we can enter it, but it might be safer to add a cushion. As in all previous posts, the value to be converted is in A1.
Python is free to use and distribute. It's an open-source programming language that's available for everyone. You can download Python and some of its libraries for free from Python.org. Although “Python in Excel” is free for users with qualifying Microsoft 365 subscriptions, some features may require a paid license. One place to start is: https://support.microsoft.com/en-us/office/python-in-excel-availability-781383e6-86b9-4156-84fb-93e786f7cab0#:~:text=With%20qualifying%20Microsoft%20365%20subscriptions,compute%20up%20to%20a%20limit.
Insightful tutorial on HEX2BIN function
Slight correction on use of Python in Excel under point G. There's no need to strip anything from the result of a Python cell. When its output mode is set to "Excel Values", the cell result is equivalent to the output of any other formula and can be referenced as any other cell.
If you replace the 2300 in the formula in section F with 4*LEN(A1) then the formula would theoretically be able to output any size binary value; but of course, there is a physical limit of the number of characters Excel will let you put into a single cell.