Convert Base 10 to Base 36 Using a Formula

Question

Is there an easy way to convert a number from Base 10 to Base 36 in Excel? I have a list of over 2,000 numbers that I need to convert so I can't do it one by one in one of the online converters.

I was thinking even if there is a mathematical way of doing it I can use a formula.

Answer 1

A better solution would be to use the BASE function

=Base(number to convert,base)

eg

=base(35,36) = Z

=base(36,36) = 10

Answer 2

Have a look at this:

http://www.thetropicalevents.com/Xnumbers60/
http://www.thetropicalevents.com/Xnumbers60.htm

[Added the code as requested by Korem]

or

Sub main()
    Dim base10Number As Double
    base10Number = Int(Rnd * 1000)
    Debug.Print base10Number, ConvertBase10(base10Number, "0123456789ABCDEF")
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

Copied from http://www.freevbcode.com/ShowCode.asp?ID=6604

or similar....

=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

Copied from: https://groups.google.com/forum/?fromgroups=#!topic/microsoft.public.excel.worksheet.functions/yY7U_kX_FwU