Building a more powerful HEX2OCT function
A.???? Excel 4 introduced the HEX2OCT function in the Analysis ToolPak add-in.? The function allows up to an 8-digit hexadecimal number: 1FFFFFFF,which is ?536,870,91110 (2^29-1) and converts to the octal number 3777777777.?
B.???? Using Excel 4 or later one can use the array formula:
{=SUM((INT((SUM(IF(CODE(MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1))>64,CODE(MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1))-55,MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1))*16^(ROW(INDIRECT("F1:F"&LEN(A1)))-1))/8^(ROW($1:$20)-1))-INT((SUM(IF(CODE(MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1))>64,CODE(MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1))-55,MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1))*16^(ROW(INDIRECT("F1:F"&LEN(A1)))-1))/8^(ROW($1:$20)-1))/8)*8))*10^(ROW($1:$20)-1))}
To produce a maximum output of 777,777,777,777,777 from the input of 1FFFFFFFFFFF, based on? the 15 decimal point precision limit. This is equivalent to a decimal value of 35,184,372,088,831. ?The choice of column F for the portions F1:F is arbitrary.? You could shorten this using range names.
C.??? As of 2013 you could use the much shorter formula:
=BASE(DECIMAL(A1,16),8)
This is limited to a string input of 1FFFFFFFFFFFFF which outputs the maximum string of 37,7777,777,777,777,777 , which is the equivalent of 9,007,199,254,740,990 in base 10.
D.???? If you are using an Excel version of 2016 or later then the following formula allows a much larger input (256 F’s) and output 1 followed by 341 7’s, a decimal value of approximately 898,846,567,431,158E+307:
领英推荐
=IF(A1="0",0,CONCAT(BIN2OCT(MID(REPT("0",CEILING(LEN(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))),1024)),3)-LEN(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))),1024)))&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))),1024),1+3*(ROW(INDIRECT("1:"&CEILING(LEN(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))),1024))/3,1)))-1),3))))
E.????? Using SEQUENCE and LET beginning in 2021 you can shorten the above formula to:
=LET(C,CONCAT(HEX2BIN(MID(A1,SEQUENCE(LEN(A1)),1),4)),M,MID(C,FIND("1",C),1024),L,LEN(M),IF(A1="0",0,CONCAT(BIN2OCT(MID(REPT("0",CEILING(L,3)-L)&M,1+3*(SEQUENCE(,CEILING(L/3,1))-1),3)))))
F.????? With Python incorporated into Excel as of 2025 you can use the formulas in A2 and A3:
PY oct(int(str(xl("A1")),16))[2:]
=A2.Python_str
Here I introduce the operation [2:], the slice notation, which drops the first 2 characters from the result of the first formula, something we did in Excel with the MID function.? (Python places an 0o before the converted results.? Notice that Python formulas don’t begin with =, however, when you enter a Python formula in the spreadsheet you type =PY( and then Excel knows you are entering Python.? I’ve tested the first formula to an input string of 6000 F’s which outputs an 8000 string of 7’s.? As in all previous posts, the value to be converted is in A1.? (In the highly unlikely case where you need more digits you can choose an online conversion calculator, but of course that is not dynamic within Excel.)
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.