Convert Base 36 to base 10
Here is a 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 I also added some error checking as well.
--
Rick (MVP - Excel)
"David" wrote in message
...
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
--
Thanks for your help
"Rick Rothstein" wrote:
Joe, Ron... yes, I screwed that up... thanks for point it out to me.
David... this function will do what you want...
Function ConvertBase36ToBase10(Base36Number As String) As Long
Dim X As Long, Total As Long, 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
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
Are you sure your numbers are Base 36? I ask because I kind of suspect
your "digits" are these...
0, 1, 2, ...., 9, A, B, ..., X, Y, Z
and if that is the case, then you actually have Base 37 numbers and not
Base 36. For Base36 numbers, the letter Z would not be in your set of
digits... Z would be the 37th digit because 0 is the first digit.
Assuming
you really have Base36 numbers (no Z), then this function should do
what
you want...
Function ConvertBase36ToBase10(Base36Number As String) As Long
Dim X As Long, Total As Long, 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) - 54) * 36 ^ (Len(Base36Number) - X)
Next
End Function
If Z is in your set (meaning you have Base37 numbers), then simply
change
all the 36's to 37's.
--
Rick (MVP - Excel)
"David" wrote in message
...
I have been given a spreadsheet with transaction numbers converted into
base
36- alpha numeric - I need it in base 10- number format- I have approx
30,000
of these!- is ther a formula to convert from 1 to another?
--
Thanks for your help
.
|