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

Yes, there is a formula in Excel that can convert numbers from base 36 to base 10. The formula is called "BASE" and it takes two arguments: the number you want to convert and the base it is currently in. Here are the steps to convert your transaction numbers from base 36 to base 10:

1. Open your Excel spreadsheet and select the cell where you want to enter the formula.
2. Type "=BASE(number, 36)" into the cell, replacing "number" with the transaction number you want to convert.
3. Press Enter to calculate the result.
4. Repeat steps 2 and 3 for each transaction number you want to convert.

If you have 30,000 transaction numbers to convert, you can use Excel's "Fill Down" feature to quickly apply the formula to all of the cells in a column. Here's how:

1. Enter the formula into the first cell in the column, as described above.
2. Click on the cell to select it.
3. Move your mouse to the bottom right corner of the cell until you see a small black cross.
4. Click and drag the cross down the column to apply the formula to all of the cells.

Excel will automatically update the formula for each cell, using the correct transaction number. Once the conversion is complete, you can copy and paste the results into a new column or spreadsheet if needed.

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

"Rick Rothstein" wrote:
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 -----

"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

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

"Joe User" wrote:

"David" wrote:
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?

Do you really mean base 16 -- hexadecimal? Are the digits only 0-9 and A-F?

If so, then use HEX2DEC below. But note the restrictions explained in the
Help page. If they do not work for you, you can modify the UDF below,
changing 36 to 10 and changing the sHetTri string among other cosmetic
changes.

If you truly mean hexatridecimal -- digits 0-9 and A-Z -- use the HexTri2Dec
UDF below.

In either case, if the 30,000 hex-whatever numbers are in a single column,
enter the follow formula, whichever applies, in a parallel column:

=HEX2DEC(A1)

=HexTri2Dec(A1)

If you wish, you can copy the new column, use paste-special-value to replace
the original column, then delete the new column.

UDF....

Option Explicit

Function HexTri2Dec(s As String)
Const sHexTri As String * 36 = _
"0123456789abcdefghijklmnopqrstuvwxyz"
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
x = InStr(sHexTri, c)
If x = 0 Then GoTo badForm
d = d * 36 + x - 1
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))
x = InStr(sHexTri, c)
If x = 0 Then GoTo badForm
f = f * 36
If Err.Number < 0 Then GoTo done
d = d + (x - 1) / 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

On Mon, 8 Feb 2010 13:36:04 -0500, "Rick Rothstein"
wrote:

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

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

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


.

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


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

"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


.

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

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


.

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

Rick Rothstein wrote:
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

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


.

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

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


.

On Monday, 8 February 2010 22:40:01 UTC+5:30, David wrote:
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

@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

wrote:
On Monday, 8 February 2010 22:40:01 UTC+5:30, David wrote:
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.

Rick wrote:
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.

I wrote:
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 .

Rick wrote:
yes, I screwed that up... thanks for point it out to me.