Hi,
If I have a column of numbers in base 10, is there an Excel function to
convert those numbers to a different base? I am particularly interested in
converting them to base 16 (hexadecimal).
I know I could do the calculations myself, but thats a little tedious.
Thanks in advance,
Alain
The Analysis Tool Pak has a DEC2HEX function which works for whole numbers.
There are some other functions for implemented for binary and octal
conversions, too.
Also, I have been working on the following UDF, which handles positive numbers,
including decimals, from base 2 to base 62. It uses A-Z and a-z for digits >9,
and handles decimals also. In the argument, you specify the number to convert,
base you are converting from, base you are converting to, and, optionally, the
number of decimal places in the result (if not a whole number).
To use this, <alt><F11> opens the Visual Basic Editor. Ensure your project is
highlighted in the Project Explorer, then Insert/Module and paste the code
below into the windo that opens.
To use it in your worksheet, replace the variables with numbers or cell
references. The DecPlace argument is optional.
=BaseConvert(num, FromBase, ToBase, [DecPlace])
==========================
Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String
Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r As Double
On Error GoTo HANDLER
If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If
If InStr(1, num.Text, "+") Then
BaseConvert = "Cannot use Scientific Notation"
Exit Function
End If
'Convert to Base 10
If FromBase > 10 Then
LDI = InStr(1, num, ".") - 2
If LDI = -2 Then LDI = Len(CStr(num)) - 1
Else
LDI = Fix(CDec(Log(num) / Log(FromBase)))
If num < 1 Then LDI = 0
End If
j = LDI
Temp = Replace(num, ".", "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = r + Temp2 * FromBase ^ j
j = j - 1
Next i
LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0
ReDim Digits(LDI)
For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = r - Digits(i) * ToBase ^ i
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)
If r <> 0 Then
Digits(0) = "."
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = r - Digits(i) * ToBase ^ -i
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If
BaseConvert = Temp & Join(Digits, "")
Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description)
End Function
======================
--ron
