Home 
Search 
Today's Posts 
#1




Converting from Decimal to Base36 in Excel Formula
Hi! Hoping someone can help with this...
I found this very nice formula in another post that converts a Base36 # to decimal: =IF(A1="","0",SUMPRODUCT(POWER(36,LEN(A1)ROW(INDIRECT("1:"&LEN(A1)))),(CODE(UPPER(MID(A1,RO W(INDIRECT("1:"&LEN(A1))),1)))48*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<58 )55*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64 )))) Can someone write me the opposite code in a similar Excel formula format? ie, converting a # as high as 999999999999999 (15 9's) to Base36 ... I'm using web converters right now as an alternative and the Base36 converted # is "9ugxnorjlr" If the code, like above, can accommodate other BaseX conversion (by changing the "36"), that would be even better, thanks! Jason 
#2




Converting from Decimal to Base36 in Excel Formula
On Thu, 12 May 2011 13:51:38 GMT, Jason Tram wrote:
Hi! Hoping someone can help with this... I found this very nice formula in another post that converts a Base36 # to decimal: =IF(A1="","0",SUMPRODUCT(POWER(36,LEN(A1)ROW(INDIRECT("1:"&LEN(A1)))),(CODE(UPPER(MID(A1,RO W(INDIRECT("1:"&LEN(A1))),1)))48*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<58 )55*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64 )))) Can someone write me the opposite code in a similar Excel formula format? ie, converting a # as high as 999999999999999 (15 9's) to Base36 ... I'm using web converters right now as an alternative and the Base36 converted # is "9ugxnorjlr" If the code, like above, can accommodate other BaseX conversion (by changing the "36"), that would be even better, thanks! Jason I would suggest downloading and installing the free Xnumbers V6.0 addin from http://www.thetropicalevents.com/Xnumbers60/ It will do base conversions for bases Base 1 to Base 36; as well as handle numbers with more than Excel's limitation of 15 digits. 
#3




Converting from Decimal to Base36 in Excel Formula
hi,
there is an example he http://www.freevbcode.com/ShowCode.asp?ID=6604 =ConvertBase10(A1,"0123456789ABCDEFGHIJKLMNOPQRSTU VWXYZ") Sub main() Dim MyNumber As Double MyNumber = 999999999999999# MsgBox MyNumber & ": " & ConvertBase10(MyNumber, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ") End Sub Public Function ConvertBase10(ByVal d As Double, ByVal sNewBaseDigits As String) As String Dim S As String, tmp As Double, i As Integer, lastI As Integer Dim BaseSize As Integer BaseSize = Len(sNewBaseDigits) Do While Val(d) < 0 tmp = d i = 0 Do While tmp = BaseSize i = i + 1 tmp = tmp / BaseSize Loop If i < lastI  1 And lastI < 0 Then S = S & String(lastI  i  1, Left(sNewBaseDigits, 1)) 'get the zero digits inside the number tmp = Int(tmp) 'truncate decimals S = S + Mid(sNewBaseDigits, tmp + 1, 1) d = d  tmp * (BaseSize ^ i) lastI = i Loop S = S & String(i, Left(sNewBaseDigits, 1)) 'get the zero digits at the end of the number ConvertBase10 = S End Function  isabelle 
#4




Converting from Decimal to Base36 in Excel Formula
I also needed to do this  and spent considerable time doing so to get the following. First, a few comments  array formulas (row(indirect("1:10")) won't work for some reason, I think its because concatinate won't do it.. Sad, because it makes it harder.
Second,everywhere I see replies I see VBA or macros as an answer. While those work, the workbook is no longer safe and this isn't good. The code below can be pasted into excel and it will calculate. It assumes cell A1 has the number and D1 has the base you want to use. I hope others find this helpful, I had a lot of fun figuring it out. Sauralf Answer: =CONCATENATE( IF(FLOOR(A1/$D$1^12,1)=0,"",IF(MOD(FLOOR(A1/$D$1^12,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^12,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^12,1),$D$1))), IF(FLOOR(A1/$D$1^11,1)=0,"",IF(MOD(FLOOR(A1/$D$1^11,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^11,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^11,1),$D$1))), IF(FLOOR(A1/$D$1^10,1)=0,"",IF(MOD(FLOOR(A1/$D$1^10,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^10,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^10,1),$D$1))), IF(FLOOR(A1/$D$1^9,1)=0,"",IF(MOD(FLOOR(A1/$D$1^9,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^9,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^9,1),$D$1))), IF(FLOOR(A1/$D$1^8,1)=0,"",IF(MOD(FLOOR(A1/$D$1^8,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^8,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^8,1),$D$1))), IF(FLOOR(A1/$D$1^7,1)=0,"",IF(MOD(FLOOR(A1/$D$1^7,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^7,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^7,1),$D$1))), IF(FLOOR(A1/$D$1^6,1)=0,"",IF(MOD(FLOOR(A1/$D$1^6,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^6,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^6,1),$D$1))), IF(FLOOR(A1/$D$1^5,1)=0,"",IF(MOD(FLOOR(A1/$D$1^5,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^5,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^5,1),$D$1))), IF(FLOOR(A1/$D$1^4,1)=0,"",IF(MOD(FLOOR(A1/$D$1^4,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^4,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^4,1),$D$1))), IF(FLOOR(A1/$D$1^3,1)=0,"",IF(MOD(FLOOR(A1/$D$1^3,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^3,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^3,1),$D$1))), IF(FLOOR(A1/$D$1^2,1)=0,"",IF(MOD(FLOOR(A1/$D$1^2,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^2,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^2,1),$D$1))), IF(FLOOR(A1/$D$1^1,1)=0,"",IF(MOD(FLOOR(A1/$D$1^1,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^1,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^1,1),$D$1))), IF(MOD(FLOOR(A1/$D$1^0,1),$D$1)9,CHAR(MOD(FLOOR(A1/$D$1^0,1)10,$D$1)+65),MOD(FLOOR(A1/$D$1^0,1),$D$1)) ) 
#5




Converting from Decimal to Base36 in Excel Formula
Sauralf,
GREAT stuff and very appreciated. I need this but also need to go the other way (36 to 10). Any chance you could be persuaded into helping? thanks and appreciation, John On Wednesday, April 3, 2013 5:50:40 PM UTC7, wrote: I also needed to do this  and spent considerable time doing so to get the following..... 
#6




Converting from Decimal to Base36 in Excel Formula
Thank you for that great formula Sauralf! I started exploring when I saw this article; http://arstechnica.com/security/2015...ckyouanyway/ this is interesting on several different levels; the formula,the math, as a tool. Whenever I see this sort of thing I create a spreadsheet with the formulas and lots of sample data and notes,AND the URLs and ancillary articles that led me to, then save that forever. You are immortalized on my systems, my network, my cloud, Google Drive, Dropbox, etc. Thanks again!

#7




Converting from Decimal to Base36 in Excel Formula
Reno, I did this with a reverse vlookup table, similar to a rainbow table, using Sauralf's excellent formula. Not ideal, but it works. Be careful thoughtoo many rows and your machine will use all available memory. I kinda solved that by using a constant start and constant interval in my decimal reverse formulas (abs reference), but intermediate values must still be interpolated, similar to the way we used log and trig tables in the back of our math book in the 70's. That approach could be further refined by varying the interval based on the 36cimal string length. I think I just made up new math terminologywhere is Mr Pulitzer? That is even further problematic if trying to build something more universal; for instance to work with any plugged base, like 22 or 101, for which Sauralf's formula works, up to base 256, though the ASCIIs look odd.
I find this really interesting. The 36cimal equivalent of 100,000 takes only 4 places. I believe a proper Excel formula would require complex use of conditionals, powers and factorials that would be VERY difficult and long, and somewhat complicated and slow in VB, but MatLab or C might handle. For amusement; using "the Sauralf formula:" Decimal Number Base Decimal Converted to Basecimal 100,000,000,000,000 127 N_Å¡{Z3J 100,000,000,000,000 199 1Â°Å’Ã’ÃŠÃ¢E (I like Primes) And how WOULD one compile an ncimal prime list? How many subatomics in the known universe anyone? Perhaps around N_Å¡{Z3J ^ Å’ in 251cimal? Makes calculating Ï€ in binary seem like kid stuff. Would quantum computing handle this? Stretching my math skills and imagination here, but this was fun. 
#8




Converting from Decimal to Base36 in Excel Formula
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))) 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Converting amount($) of my expenditure into the base currency i wa  Excel Discussion (Misc queries)  
formulat to convert base 32 to decimal?  Excel Worksheet Functions  
Batch converting CSV files from commadecimal to perioddecimal  Excel Discussion (Misc queries)  
Converting 2place decimal value to floating point decimal number with leading zero  Excel Discussion (Misc queries)  
formula for converting decimal to inch & fraction  Excel Discussion (Misc queries) 