Convert Base 36 to base 10
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
|