Converting from Decimal to Base-36 in Excel Formula

[Jason Tram]

Hi! Hoping someone can help with this...

I found this very nice formula in another post that converts a Base-36 # to decimal:
=IF(A1="","0",SUMPRODUCT(POWER(36,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))),(CODE(UPPER(MID(A1,ROW(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 Base-36 ... I'm using web converters right now as an alternative and the Base-36 converted # is "9ugxnorjlr"

If the code, like above, can accommodate other Base-X conversion (by changing the "36"), that would be even better, thanks!

-Jason

 

[isabelle]

hi,

there is an example here:
http://www.freevbcode.com/ShowCode.asp?ID=6604

=ConvertBase10(A1,"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")

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

 

[sauralf]

I also needed to do this -- and spent considerable time doing so to get thefollowing. 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))
)

 

[sauralf]

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))
)

 

[reno1john]

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

 

[sauralf]

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 UTC-7, (e-mail address removed) wrote:
> I also needed to do this -- and spent considerable time doing so to get the following.....

Sure. This formula will work. Assume the BaseN value in cell A5 and the Base in B5, This does use the indirect formula to make an array from 1:Base then takes the value of the ASCII code to decimal.

=IF((A5=""),"0",SUMPRODUCT(POWER($B$5,(LEN(A5)-ROW(INDIRECT(("1:"&LEN(A5)))))),((CODE(UPPER(MID(A5,ROW(INDIRECT(("1:"&LEN(A5)))),1)))-(48*(CODE(MID(A5,ROW(INDIRECT(("1:"&LEN(A5)))),1))<58)))-(55*(CODE(MID(A5,ROW(INDIRECT(("1:"&LEN(A5)))),1))>64)))))

To make this easier, I'm attaching a simple Excel file (no macros, download safe always, which is really the point of this!) that has instructions and goes both ways from Base 2 to 36. I've done it with character substitution also (no I, or L, or O so you can't be confused), but that uses look-ups is is MUCH more complicated.



- Sauralf

 

[billschlossberg]

Thank you for that great formula Sauralf! I started exploring when I saw this article; http://arstechnica.com/security/2015/01/browsing-in-privacy-mode-super-cookies-can-track-you-anyway/ 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!

 

[sauralf]

You're welcome. I prefer workbooks that don't need to be "enabled", which is why I generally do it this way if at ALL possible myself. Feels good to be "imortalized"

 

[billschlossberg]

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 though--too many rows and your machine will use all available memory. I kindasolved 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 varyingthe interval based on the 36-cimal string length. I think I just made up new math terminology--where 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 36-cimal 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 Base-cimal
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 n-cimal prime list?

How many sub-atomics in the known universe anyone? Perhaps around
N_Å¡{Z3J ^ Å’ in 251-cimal?

Makes calculating π in binary seem like kid stuff. Would quantum computing handle this?

Stretching my math skills and imagination here, but this was fun.