Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




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




Answer: Convert Base 36 to base 10
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:
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:
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.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 
#4
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
"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 
#5
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 09 and AF? 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 09 and AZ  use the HexTri2Dec UDF below. In either case, if the 30,000 hexwhatever 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 pastespecialvalue 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 
#6
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 (09) + 26 [AZ] letters  Base 36 ron 
#7
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 
#8
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 . 
#9
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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^311 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 
#10
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
"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 . 
#11
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 
#12
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 28digit integers (and some 29digit 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 . 
#13
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
Here is the Decimal Data Type version of my function which will handle up to
a 28digit 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 "*[!09AZaz]*" 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 
#14
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
Rick Rothstein wrote:
Here is the Decimal Data Type version of my function which will handle up to a 28digit 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 "*[!09AZaz]*" 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 
#15
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
Here is a version of my function which will handle up to a 28digit 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 "*[!09AZaz]*" 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 . 
#16
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 28digit 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 "*[!09AZaz]*" 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 
#17
Posted to microsoft.public.excel.programming




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 28digit integers (and some 29digit 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 . 
#18
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 
#19
Posted to microsoft.public.excel.programming




Convert Base 36 to base 10
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 4yearold 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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
formulat to convert base 32 to decimal?  Excel Worksheet Functions  
Option Base 1; how to also make autoarrays set to base 1?  Excel Programming  
convert a nine digit number to base 32  Excel Worksheet Functions  
how do I convert numbers to a different base in Excel?  Excel Worksheet Functions  
Convert Works 6.0 data base to Excel?  New Users to Excel 