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

 

[Ron Rosenfeld]

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

I would suggest downloading and installing the free Xnumbers V6.0 add-in 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.

 

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