View Single Post
  #17   Report Post  
Posted to microsoft.public.excel.programming
David David is offline
external usenet poster
 
Posts: 1,560
Default Convert Base 36 to base 10

Joe / Ron
Thanks very much for all that- works perfectly
--



"Joe User" wrote:

I wrote:
"David" wrote:
I have tried the code what I get is for a sample code
O81D8KEURD94I = #value


If you had tried my HexTri2Dec function, you wouldn't
have gotten that problem.


Although my function would do the best we can in converting O81D8KEURD94I, I
should point that the base10 equivalent is about 1.14778E+20. Since that is
more than 15 digits, it cannot be represented exactly as an Excel number.

Since these are transaction ids, not numbers to be used in arithmetic, it
would be better to use a UDF that returns the exact conversion as text.

Caveat: Someone might suggest using VBA type Decimal instead of Double.
That would indeed work for this example. However, it is not a general
solution, being limited to 28-digit integers (and some 29-digit integers).

Nevertheless, below is my UDF with that modification. For your example,
the result is the string 114779126356831142514.

Note: This implementation allows only integer base36 numbers.

UDF....


Option Explicit

Function HexTri2Dec(s As String)
Dim c As String * 1, bNeg As Boolean
Dim i As Long, x As Long, d
s = Trim(s)
If Mid(s, 1, 1) = "-" Then
If Len(s) = 1 Then GoTo badForm
bNeg = True: i = 2
Else
bNeg = False: i = 1
End If
c = ""
d = CDec(0)
On Error Resume Next
For i = i To Len(s)
c = LCase(Mid(s, i, 1))
If "0" <= c And c <= "9" Then x = Asc(c) - 48 _
Else If "a" <= c And c <= "z" Then x = Asc(c) - 87 _
Else: GoTo badForm
d = d * 36 + x
If Err.Number < 0 Then GoTo badNum
Next i

done:
If bNeg Then d = -d
HexTri2Dec = Format(d, "0") 'allow only integers
Exit Function

badNum:
HexTri2Dec = CVErr(xlErrNum)
Exit Function

badForm:
HexTri2Dec = CVErr(xlErrValue)
End Function


----- original message ------

"Joe User" wrote:

"David" wrote:
I have tried the code what I get is for a sample code
O81D8KEURD94I = #value


If you had tried my HexTri2Dec function, you wouldn't have gotten that
problem.


----- original message ------

"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

--
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


.