Excel: Base Conversion (Decimal to Quintal) - SOLVED, Haircut is teh l33t

[hjo3]

Could anyone tell me how to write an excel function to convert base 10 to base 5? I.e., take the number in A1 and return the quintal version of it in the current cell. Excel has conversion functions built in for all the major bases (octal, hexadecimal, binary), but nothing for other bases as far as I can tell. I've found Java scripts that convert bases, but I don't know how to convert them to Excel equations.

 

[hjo3]

bump

 

[Haircut]

Here is a function that will do that.

What you will need to do first is create the module for the code.
Go to Tools -> Macro -> Visual Basic Editor

Then go to Insert -> Module from that screen.
Now, paste the following code into the main editor window


Function ConvertBase(ByVal intNumberToConvert As Integer, ByVal intBase) As String

Dim strOutput As String
Dim intDigit As Integer
Dim strDigit As String
Dim blnNegative As Boolean

' We run out of letters if trying to convert to a base > 36 so don't let the user do it
If intBase > 36 Then
ConvertBase = 0
Exit Function
End If

If intNumberToConvert < 0 Then
intNumberToConvert = -1 * intNumberToConvert
blnNegative = True
End If

While intNumberToConvert <> 0
intDigit = intNumberToConvert Mod intBase
If intDigit <= 9 Then
strDigit = Str$(intDigit)
Else
strDigit = Chr$(intDigit + 55)
End If
intNumberToConvert = (intNumberToConvert - intDigit) / intBase
strOutput = Trim(strDigit) & strOutput

Wend

If blnNegative Then
strOutput = "-" & strOutput
End If

ConvertBase = strOutput

End Function


You will then have a function that you can call from Excel in the following way:

=ConvertBase (A1,k)
will convert cell A1 to base k.

At the minute it assumes that all input is in base 10.

Hope this helps 🙂

 

[hjo3]

Originally posted by: Haircut
Hope this helps 🙂

Thank you! You're awesome!
😀

 

[sflummox]

I realize this is an ancient thread, but it was by far the best n-base conversion for Excel that I was able to find. Thanks from the future, Haircut.

The one improvement I would suggest is using Long instead of Int in the first line of code:

Function ConvertBase(ByVal intNumberToConvert As Long, ByVal intBase) As String

 

[LaughterOnWater]

Haircut - I also found this useful, even further in the future. Thank you.
sflummox - changes made. 😀
Awesomeness!

 

[Chapbass]

Just gonna post in this double necro here...