Building a more powerful OCT2HEX function
A.???? Excel 4 introduced the OCT2HEX function in the Analysis ToolPak add-in.? The function allows up to an 10-digit octal number: 3,777,777,777, which converts to a hex value of 1FFFFFFF.?
B.???? It seems that very few people used range names to shorten formulas before the introduction of LET.? In my last post on the x2y functions I thought it would be interesting and instructive to use this technique.? If you have access to LET I would not use the range name approach in most cases.? Using any version of Excel since 4 one can use the following array formula which we will afterwards simplify with range names: B1.
=IFNA(MID(DEC2HEX(SUM(MID(I3,L+1-ROW(INDIRECT("1:"&L)),1) 8^(ROW(INDIRECT("1:"&L))-1))/2^32)&DEC2HEX(MOD(SUM(MID(I3,L+1-ROW(INDIRECT("1:"&L)),1)8^(ROW(INDIRECT("1:"&L))-1)),2^32),8), MATCH(FALSE,-MID(DEC2HEX(SUM(MID(I3,L+1-ROW(INDIRECT("1:"&L)),1) 8^(ROW(INDIRECT("1:"&L))-1))/2^32)&DEC2HEX(MOD(SUM(MID(I3,L+1-ROW( INDIRECT("1:"&L)),1)8^(ROW(INDIRECT("1:"&L))-1)),2^32),8),1,ROW($1:$20))<> 0,1)+1,25),DEC2HEX(SUM(MID(I3,L+1-ROW(INDIRECT("1:"&L)),1)* 8^(ROW(INDIRECT("1:"&L))-1))/2^32)&DEC2HEX(MOD(SUM(MID(I3,L+1-ROW(INDIRECT("1:"&L)),1)*8^(ROW(INDIRECT("1:"&L))-1)),2^32),8)) ?
This formula is not for you to dissect, but to refer back to when examining the next formula.? Here are the defined range names: L=LEN(OCT2HEX!A1) which is used above.? Ro=ROW(INDIRECT("1:"&L)), SM =DEC2HEX(SUM(MID(OCT2HEX!A1,L+1-Ro,1)*8^(Ro-1))/2^32)&DEC2HEX(MOD(SUM(MID(OCT2HEX!A1,L+1-Ro,1)*8^(Ro-1)),2^32),8):
B2. =IFNA(MID(SM,MATCH(FALSE,-MID(SM,1,ROW($1:$20))<>0,1)+1,25),SM)
This produces a maximum output of 1FFFFFFFFFFFFF from the input string of 377777777777777777 based on? the 15 decimal point precision limit.
C.??? The good news, if you are using Excel 2013 or later you could use the much shorter formula below, without the need for range names:
=BASE(SUM(MID(A1,LEN(A1)+1-ROW(INDIRECT("F1:F"&LEN(A1))),1)*8^(ROW( INDIRECT("F1:F"&LEN(A1)))-1)),16)
Or better yet:
=BASE(DECIMAL(A1,8),16)
Both of these have the same limits as B1 & B2.
领英推荐
D.???? If you are using 2016 or later there is another monster formula with a much greater range.? To avoid displaying that monster here we go directly to the 2021 version which uses SEQUENCE and LET:
=LET(C,CONCAT(OCT2BIN(MID(A1,SEQUENCE(LEN(A1)),1),3)),M,MID(C,FIND(1,C),2500),L,LEN(M),CONCAT(BIN2HEX(MID(REPT("0",CEILING(L,4)-L)&M,1+4*(ROW(INDIRECT("1:"&CEILING(L/4,1)))-1),4))))
Note: C & R are illegal range names but not illegal LET names.? Without any substitutions this formula would be 565 characters long, but here it is 183.?
This formula accepts a octal input of 1 followed by 832 7’s and can output a maximum hexadecimal of 1 followed by 624 F’s.
E.????? With Python incorporated into Excel as of 2025 you can use the formulas in A2:
PY hex(int(str(xl("A1")),8))[2:]
=A2.Python_str
The formula in A2 returns the full 625 long string, but the formula in A3 returns just a 255-character string. 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.
?