I also through in some error checking as well.

"through"??? That should have been "threw" instead.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is the Decimal Data Type version of my function which will handle up
to a 28-digit Base36 number (max "number" is ZZZZZZZZZZZZZZZZZZ)...

Function ConvertBase36ToBase10(Base36Number As String) As Variant
Dim x As Long, Digit As String, Power As Variant
If Len(Base36Number) 18 Or Base36Number Like "*[!0-9A-Za-z]*" Then
ConvertBase36ToBase10 = CVErr(xlErrNum)
Exit Function
End If
For x = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, x, 1))
If Len(Base36Number) 9 Then
Power = CDec("101559956668416") * (36 ^ (Len(Base36Number) - 9 - x))
Else
Power = 36 ^ (Len(Base36Number) - x)
End If
ConvertBase36ToBase10 = ConvertBase36ToBase10 + CDec(IIf(IsNumeric( _
Digit), Digit, (Asc(Digit) - 55)) * Power)
Next
End Function

Note that the If..Then handling of the exponent for the 36 base number is
necessary because raising any number to a power using the caret (^(^(^)
operator collapses Decimal Data Type values back to Long Data Type
values... the 101559956668416 value is 36 raised to the 9th power. I also
through in some error checking as well.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 9 Feb 2010 02:18:03 -0800, David

wrote:

Guys thanks for this-

I have tried the code what I get is for a sample code
O81D8KEURD94I = #value
but
081d8ke = 486026654

Is there any length critera in the function- couldn't spot any

You get a VALUE error because Rick Dim'd is variables as Longs, and your
first
entry overflows that.

If you change it to Double, it should work OK:

===================
Function ConvertBase36ToBase10(Base36Number As String) As Double
Dim X As Long, Total As Double, Digit As String
For X = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, X, 1))
ConvertBase36ToBase10 = ConvertBase36ToBase10 + IIf(IsNumeric(Digit),
_
Digit, (Asc(Digit) - 55)) * (36 ^ (Len(Base36Number) - X))
Next
End Function
=====================

Of course, Excel is limited to 15 digit precision. You can get increased
precision in VBA by using the Decimal data type, but the only way to get
that
into a worksheet cell would be with a string output.
--ron