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

0 |

2/8/2010 5:10:01 PM

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" <David@discussions.microsoft.com> wrote in message news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... >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

0 |

2/8/2010 6:36:04 PM

"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> 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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... > 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" <David@discussions.microsoft.com> wrote in message > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... >>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 >

0 |

2/8/2010 7:49:54 PM

On Mon, 8 Feb 2010 13:36:04 -0500, "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> 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

0 |

2/8/2010 7:59:40 PM

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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... > 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" <David@discussions.microsoft.com> wrote in message > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... >>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 >

0 |

2/9/2010 4:11:02 AM

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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message > news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... > > 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" <David@discussions.microsoft.com> wrote in message > > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... > >>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 > > > > . >

0 |

2/9/2010 10:18:03 AM

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

0 |

2/9/2010 12:47:34 PM

"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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message > > news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... > > > 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" <David@discussions.microsoft.com> wrote in message > > > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... > > >>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 > > > > > > > . > >

0 |

2/9/2010 1:06:05 PM

On Tue, 9 Feb 2010 02:18:03 -0800, David <David@discussions.microsoft.com> 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

0 |

2/9/2010 1:12:40 PM

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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message > > > news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... > > > > 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" <David@discussions.microsoft.com> wrote in message > > > > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... > > > >>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 > > > > > > > > > > . > > >

0 |

2/9/2010 1:32:01 PM

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" <ronrosenfeld@nospam.org> wrote in message news:24m2n5t9fnigourdm426m3ue953grv8e2t@4ax.com... > On Tue, 9 Feb 2010 02:18:03 -0800, David <David@discussions.microsoft.com> > 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

0 |

2/9/2010 3:48:59 PM

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

0 |

2/9/2010 3:59:00 PM

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" <David@discussions.microsoft.com> wrote in message news:DAEC194E-4145-4AA9-BA5C-50DBC3522CA7@microsoft.com... > 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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message >> news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... >> > 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" <David@discussions.microsoft.com> wrote in message >> > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... >> >>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 >> > >> >> . >>

0 |

2/9/2010 3:59:00 PM

> I also through in some error checking as well. "through"??? That should have been "threw" instead. -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message news:OOhXi9ZqKHA.3848@TK2MSFTNGP06.phx.gbl... > 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" <ronrosenfeld@nospam.org> wrote in message > news:24m2n5t9fnigourdm426m3ue953grv8e2t@4ax.com... >> On Tue, 9 Feb 2010 02:18:03 -0800, David >> <David@discussions.microsoft.com> >> 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 >

0 |

2/9/2010 3:59:55 PM

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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message > > > > news:uWTGQ2OqKHA.3848@TK2MSFTNGP06.phx.gbl... > > > > > 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" <David@discussions.microsoft.com> wrote in message > > > > > news:23328038-FEB3-461D-A3C6-F0E57A848C33@microsoft.com... > > > > >>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 > > > > > > > > > > > > > . > > > >

0 |

2/9/2010 4:20:15 PM

I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Hi; I have customized a totally new form for creating accounts and saved it, but now when I want to create new accounts the old form shows up and don't know how to access the new form! do you know what the problem could be? thanks, Farshad you need to publish your changes, goto Deployment Manager, and run "Publish" on the object you changed (ie accounts, leads, opportunity, etc). then restart IIS -Gary "farshad" <farshad@seancesoft.com> wrote in message news:037d01c394f8$3fd611e0$a401280a@phx.gbl... > Hi; > > I have customized a totally new form ...

Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

I need to SUM a range of cells only if it is in a certain month. =SUMIF(sheet1!A4:A10000,">="&DATE(2004,2,1),values) This formula sums all months from Feb 1 on. I only want February. Is there a way to enter the end date in my formula? I need to do this for every month. Joe =SUMPRODUCT(--(TEXT(Sheet1!A4:A10000,"mmm")="Feb")) HTH Jason Atlanta, GA >-----Original Message----- >I need to SUM a range of cells only if it is in a certain month. > >=SUMIF(sheet1!A4:A10000,">="&DATE(2004,2,1),values) > >This formula su...

I can sync combo boxes in a form, but cannot get it to work on a subform. I do not know VBA, so if you try to give me code, that won't help. I use macros and queries for everything, I haven't learned VBA yet. How are you syncing them on the form? If the RowSource for the second combo box points to the first combo box to limit it, recognize that you cannot refer directly to controls on forms being used as subforms, you have to go via the parent form. Forms!NameOfParentForm!NameOfSubformControl.Form!NameOfControlOnSubform Note that depending on how you added the subf...

...

This might be a dumb question, but I don't want to screw things up. I was running Mac OS 9.0.4 and I downloaded OS 9.1, 9.1.2, 9.2.2 and then the new 10.2. However, all my software is still in the 9.1 classic. I want to use it all in the new OS 10.2. Will Office 2001 Academic Edition work in OS 10.2? Do I need to uninstall it in OS 9.1 first, then install it is OS 10.2? Or, can I just drag and drop it on the 10.2 desktop or in the applications folder? I have a lot of other expensive softwares in OS 9.1 that I don't want to have to upgrade, but need to be able to use in OS 10.2. Wi...

Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

I have several tables and I’m having a problem with my last one. I have two columns in this table, ‘License’, which are populated using combo boxes, and these combo boxes are populated from using queries on other tables: I have an ‘Entitlement’ table with has and Entitlement ID (different to it’s primary Key ID). For each entitlement there can be one or more templates and so I have a ‘Template’ table with a ‘Template ID’. As it happens a template can also be in one or more entitlements so there is also an ‘Entitlement-Template’ table to manage the many to many link. My lic...

I am using David McRitchie's code for changing color of entire row based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? One way: Target.EntireRow.resize(1,17).Interior.ColorIndex = 36 JingleRock wrote: > > I...

Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

The spell check function on word 2007 installed on my laptop is no longer working. Any suggestions that I might try to correct this? Thanks See http://sbarnhill.mvps.org/WordFAQs/MasterSpellCheck.htm. -- Stefan Blom Microsoft Word MVP "recent grad" <recentgrad@discussions.microsoft.com> wrote in message news:81F572E7-E547-4CDE-A4F7-A8BFF0CA003B@microsoft.com... > The spell check function on word 2007 installed on my laptop is no longer > working. Any suggestions that I might try to correct this? > > Thanks ...

Hi ! I have a CDialog based application. I added the line : CLASS "MyClassName" in the .RC file. In the InitInstance() I register my class. I use an external DLL ressource. This DLL is loaded in the InitInstance() methode It doesn't work. It seems that it because I have an external ressource. Does anybody have an idea ? Thank you comiv You can use AfxRegisterClass to register you window. Look at the sample code here: http://msdn2.microsoft.com/en-us/library/kcb1w44w(VS.80).aspx I dont know what exactly is the problem if you have a different DLL where this string resource is....

Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

My Quicken files are mostly investment related, and generally converted fine. However all bonds (regular and muni's) converted as Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) How do you change the Investment Type for an item? Thank you. In microsoft.public.money, Mike wrote: >My Quicken files are mostly investment related, and generally >converted fine. However all bonds (regular and muni's) converted as >Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) Money typically converts custom data types from Quicken into funds. I thou...

I a trying to figure out why when I type 11 and automatically converts it to .11, if I type 11. it will stay 11,if I change all the cells to text then back to number they willstay. I have checked the formatting of the cells, it even happens when I open a brand new worksheet. Any ideas? Thanks Dawn Hi Dawn, Tools>Option>Edit, uncheck Fixed Decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "DawnP" <anonymous@discussions.microsoft.com> wrote in message news:c3cf01c48a05$d75359d0$a501280a@phx.gbl... > I a trying to figure out why when I type 11 and &...

Hi. I'm hoping someone will give this a try. I'd like to enter a function into each cell in column D so that the total hours worked for the previous week appears each Sunday. For instance, cell D6 would display 11, the sum of C2:C6 (note this is a short week). Cell D20 would display 22.5, the sum of C7:C20. On some days, no work at all is performed, but on other days multiple tasks are performed. The only cells in column D that should display a value are the Sunday's... all other cells should display nothing. Thanks very much for your help!! Gregg. ...

Good day all. Excel 2007. I have (many) columns each displaying student data in rows below. On a separate sheet I have a data validated sellection from a dropdown menu of the students names. What I am trying to achieve is that when a particular student name is selected from the dropdown, the corresponding student data should appear in fixed cells on the top rows of same sheet where the student name was selected. The cells where the selction is made will move down the sheet row by row, but the data should appear (now for the new student selected) in the same fixed cells on the top...

Quicken 2004 has many bugs, and I have had it. The most recent being that it doesn't work AT ALL now that it is the year 2004. I have had to change the date on my computer today to open it. I want to get Money instead, however I do not know if Money can get my data from the 2004 version. Does anybody know for sure? Yes is the answer to the question you posed. No is the answer to the question you are getting to but didn't pose. M04 imports Q03 and earlier. If the past predicts the future M05 will import Q04. "Colin" <anonymous@discussions.microsoft.com> wrote ...

We have created custom Fields in the Campaign Response entity that we would like to populate on the Lead Entity when we do a conversion. However it doesn't seem like this is possible from the Data Map. Is there a way to modify what fields are carried forward during a conversion, or do I need to find a different method? Thank you in advance. Hi, As I had some time left, I tried to figure this one out. To my surprise I can't find ANY direct link between a converted campaign response and a new lead. There is only an indirect link, through the campaign entity. But that does not ...

Is there an efficient way for me to convert my AOL address book over to Outlook? Before I buy Outlook, I want to be sure I cam make the conversion without loosing the large address book I have on AOL. "Herb Clader" <Herb Clader@discussions.microsoft.com> wrote in message news:44A9739C-A94F-49AB-BF08-31D78A86CCB7@microsoft.com... > Is there an efficient way for me to convert my AOL address book over to > Outlook? > > Before I buy Outlook, I want to be sure I cam make the conversion without > loosing the large address book I have on AOL. AOL's help p...