On Sat, 15 Jan 2005 01:51:35 GMT, Wildman wrote:

anyone know how to change a nine digit number into a base 32 number?

change "347472963" to "abc123"


I have found away to convert a 6 charactor base 32 string to decimal
using a VLOOkup to change the alpha charactor to decimal.
but Im stuck going to other way.

Thanks to any help in advance.

Wildman

Here is some code that should convert between bases (and will also handle
fractional numbers). But I've used capital letters for up to base 37 and small
letters for higher bases. So 347472963 under this scheme would convert to
ABC123. abc123 would be at least a base 39 number and would be, in base 10:
3335924205

If you have a version of Excel prior to about 2000, you will have to write (or
copy from the MS web site), routines for StrReverse, Split, Join which were in
the later versions of VBA.

Harlan has also written a version that works by string manipulation.


==========================
Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = Application.International(xlDecimalSeparator)

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, "E") And FromBase = 10 Then
Num = CDec(Num)
End If

'Convert to Base 10
LDI = InStr(1, Num, DecSep) - 2
If LDI = -2 Then LDI = Len(Num) - 1

j = LDI

Temp = Replace(Num, DecSep, "")
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 = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r < 0 Then 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 = CDbl(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) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(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 & vbLf & _
"Number being converted: " & Num)

End Function
==================================

--ron
--ron