Convert Base 36 to base 10

[David]

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?

 

[Rick Rothstein]

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.

 

[Joe User]

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

No, that's base 36. A thru Z represent the 26 values 10 thru 35. Google
"base 36" or see http://en.wikipedia.org/wiki/Base_36 .


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

 

[Joe User]

Improved....


Option Explicit

Function HexTri2Dec(s As String)
Dim c As String * 1, bNeg As Boolean
Dim i As Long, f As Double, d As Double, x As Long
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 = ""
On Error Resume Next
For i = i To Len(s)
c = LCase(Mid(s, i, 1))
If c = "." Then Exit For
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
If c = "." Then
f = 1
For i = i + 1 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
f = f * 36
If Err.Number <> 0 Then GoTo done
d = d + x / f
Next i
End If

done:
If bNeg Then d = -d
HexTri2Dec = d
Exit Function

badNum:
HexTri2Dec = CVErr(xlErrNum)
Exit Function

badForm:
HexTri2Dec = CVErr(xlErrValue)
End Function


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

 

[Ron Rosenfeld]

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

Are you sure about that Rick?

It seems to me that 10 digits (0-9) + 26 [A-Z] letters --> Base 36

--ron

 

[Rick Rothstein]

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

 

[David]

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

 

[Martin Brown]

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

Yes. Implicit in the declaration of Long values -2^31 < x <= 2^31-1

You have to explicitly implement some form of long integer arithmetic to
handle values which go outside this boundary.

Decimal 2146483647 is the largest Long value which in Base36 = ZIK0ZJ
(subject to typos)

You could cut the string into two parts and pray that the leading digit
is always zero. Unsigned integers can handle 6 base36 digits OK.

The mantissa of Double precision reals would let you do up to 9 digits
of Base36.

Regards,
Martin Brown

 

[Joe User]

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

 

[Ron Rosenfeld]

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

 

[Joe User]

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

 

[Rick Rothstein]

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.

 

[Martin Brown]

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.

It may be cleaner to avoid ^ entirely and to do the loop incrementally -
something along the lines of

ConvertBase36ToBase10 = 0
For x = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, x, 1))
ConvertBase36ToBase10 = ConvertBase36ToBase10*36 +
CDec(IIf(IsNumeric(Digit), Digit, (Asc(Digit) - 55))
Next

Regards,
Martin Brown

 

[Rick Rothstein]

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

I also through in some error checking as well.

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

--
Rick (MVP - Excel)

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)

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

 

[David]

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

 

[cardonline.india]

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?

@Rick Rothstein
I saw your posts. Thanks for those.
But I feel 0,1,2,...9 and A,B,C till Z adds up to 10 plus 26 equals 36 and not 37 as you have mentioned.
Please tell me where am I going wrong.
Thanks a lot.
Rajeev

 

[joeu2004]

I have been given a spreadsheet with transaction numbers
converted into base 36

[....]
@Rick Rothstein
I saw your posts. Thanks for those. But I feel 0,1,2,...9
and A,B,C till Z adds up to 10 plus 26 equals 36 and not 37
as you have mentioned. Please tell me where am I going wrong.

Where did you go wrong?

First, you are responding to comments made 4 years ago. Obviously, the
discussion is stale (read: dead).

Second, you fail to quote the comments you are responding to, namely Rick's.
So we have no context.

Finally, you are repeating comments that were already made in the 4-year-old
discussion, and Rick admitted his mistake.

Here is the complete context.

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.