Converting from Decimal to Base-36 in Excel Formula
From
gaalfred@gmail.com@21:1/5 to
All on Wed Dec 11 13:27:49 2019
I needed to convert the columns in Excel from base 26 using A=1, B=2....Z=26. I modified Sauralf's great code to do that. I put the following formula in every cell in row 1 and filled in row 2 with the number series 1,2,....16384. This way can use the
values of row 1 in my vba code count columns in base 10. Here's code:
=CONCATENATE(IF(FLOOR(A2/26^12,1)=0,"",IF(MOD(FLOOR(A2/26^12,1),26)>0,CHAR(MOD(FLOOR(A2/26^12,1),26)+64),MOD(FLOOR(A2/26^12,1),26))),IF(FLOOR(A2/26^11,1)=0,"",IF(MOD(FLOOR(A2/26^11,1),26)>0,CHAR(MOD(FLOOR(A2/26^11,1),26)+64),MOD(FLOOR(A2/26^11,1),26))),
IF(FLOOR(A2/26^10,1)=0,"",IF(MOD(FLOOR(A2/26^10,1),26)>0,CHAR(MOD(FLOOR(A2/26^10,1),26)+64),MOD(FLOOR(A2/26^10,1),26))),IF(FLOOR(A2/26^9,1)=0,"",IF(MOD(FLOOR(A2/26^9,1),26)>0,CHAR(MOD(FLOOR(A2/26^9,1),26)+64),MOD(FLOOR(A2/26^9,1),26))),IF(FLOOR(A2/26^8,1)
=0,"",IF(MOD(FLOOR(A2/26^8,1),26)>0,CHAR(MOD(FLOOR(A2/26^8,1),26)+64),MOD(FLOOR(A2/26^8,1),26))),IF(FLOOR(A2/26^7,1)=0,"",IF(MOD(FLOOR(A2/26^7,1),26)>0,CHAR(MOD(FLOOR(A2/26^7,1),26)+64),MOD(FLOOR(A2/26^7,1),26))),IF(FLOOR(A2/26^6,1)=0,"",IF(MOD(FLOOR(A2/
26^6,1),26)>0,CHAR(MOD(FLOOR(A2/26^6,1),26)+64),MOD(FLOOR(A2/26^6,1),26))),IF(FLOOR(A2/26^5,1)=0,"",IF(MOD(FLOOR(A2/26^5,1),26)>0,CHAR(MOD(FLOOR(A2/26^5,1),26)+64),MOD(FLOOR(A2/26^5,1),26))),IF(FLOOR(A2/26^4,1)=0,"",IF(MOD(FLOOR(A2/26^4,1),26)>0,CHAR(MOD(
FLOOR(A2/26^4,1),26)+64),MOD(FLOOR(A2/26^4,1),26))),IF(FLOOR(A2/26^3,1)=0,"",IF(MOD(FLOOR(A2/26^3,1),26)>0,CHAR(MOD(FLOOR(A2/26^3,1),26)+64),MOD(FLOOR(A2/26^3,1),26))),IF(FLOOR(A2/26^2,1)=0,"",IF(MOD(FLOOR(A2/26^2,1),26)>0,CHAR(MOD(FLOOR(A2/26^2,1),26)+
64),MOD(FLOOR(A2/26^2,1),26))),IF(FLOOR(A2/(26^1+1),1)=0,"",IF(MOD(FLOOR(A2/26^1,1),26)>0,CHAR(MOD(FLOOR(A2/26^1,1),26)+64),MOD(FLOOR(A2/26^1,1),26))),IF(MOD(FLOOR(A2/26^0,1),26)>0,CHAR(MOD(FLOOR(A2/26^0,1),26)+64),CHAR(MOD(FLOOR(A2/26^0,1),26)+90)))
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)